ORACLE触发器(trigger)的使用

本文详细介绍了触发器的概念、分类及其在数据库中的应用,并通过具体案例展示了不同类型的触发器如何工作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、触发器说明

触发器是一种在事件发生时隐式地自动执行的PL/SQL块,不能接受参数,不能被显式调用

2、触发器类型

根据触发器所创建的语句及所影响的对象的不同,将触发器分为以下3类

(1)DML触发器

对数据表进行DML语句操作(如insert、update、delete)时所触发的触发器,可以分为:

语句级触发器或行级触发器:行级触发器会对数据库表中的受影响的每一行触发一次触发器代码,语句级触发器则只触发一次,与语句所影响到的行数无关

before触发器或after触发器:before触发器在触发事件发生之前执行触发器代码,after触发器则在触发事件发生之后执行

语法:
create [or replace] trigger trigger_name
{before | after} trigger_event
on table_name
[for each row]
[when trigger_condition]
trigger_body

语法解释:

trigger_name:触发器名称

before | after : 指定触发器是在触发事件发生之前触发还暗示发生之后触发

trigger_event:触发事件,在DML触发器中主要为insert、update、delete等

table_name:表名,表示发生触发器作用的对象

for each row:指定创建的是行级触发器,若没有该子句则创建的是语句级触发器

when trigger_condition:添加的触发条件

trigger_body:触发体,是标准的PL/SQL语句块


(2)替代触发器(instead of触发器)

对视图进行操作时定义的触发器,替代触发器只能定义在视图上

语法:
create [or replace] trigger trigger_name --触发器名称
instead of trigger_event --触发事件
on view_name --视图名称
for each row  --替代触发器必须指定为行级的触发器
[when trigger_condition] --触发条件
trigger_body --触发体,PL/SQL块

(3)系统事件触发器

对数据库实例或某个用户模式进行操作时定义的触发器,可以分为:

数据库系统触发器和用户触发器


3、案例

(1)DML触发器

DML触发器的案例都是基于student表和stu_log表来进行的,所以先创建student表和stu_log表

create table STUDENT   ---创建student表
(
  id        NUMBER(19), --id
  stu_no    VARCHAR2(20), --学号
  stu_name  VARCHAR2(32), --姓名
  stu_age   NUMBER,  --年龄
  stu_major VARCHAR2(32) --专业
)
create table STU_LOG   ---创建stu_log表,用于记录对student表的操作日志
(
  log_id     NUMBER,  --日志id
  log_action VARCHAR2(100),  --操作名称
  log_date   DATE,  --操作时间
  log_message   VARCHAR2(32) --
)

 a、行级触发器(before触发器)

创建触发器:实现id的隐式自增
create or replace trigger modify_stu 
before insert on student
for each row
declare
next_id number;
begin
  select seq_test.nextval into next_id from dual;
  :new.id :=next_id;
end;

插入一条数据,但是不插入id

insert into student(stu_no,stu_name,stu_age,stu_major) values('NO1','张三',20,'中文系'); 

查询结果如下,自动生成id了


b、 行级触发器(after触发器)

创建触发器:将对student表的操作都记录到stu_log表中(update of 用于指定一个或多个字段,指定字段被更新时才会触发触发器)
create or replace trigger modify_stu
after insert or delete or update of stu_name
on student
for each row
  begin 
    if inserting then
      insert into stu_log values(1,'insert',sysdate,:new.stu_name);
    elsif deleting then
       insert into stu_log values(2,'delete',sysdate,:old.stu_name);
    elsif updating then
      insert into stu_log values(3,'update_old',sysdate,:old.stu_name);
      insert into stu_log values(4,'update_new',sysdate,:new.stu_name);
     end if;
end;
insert into student values(1,'NO2','李四',21,'数学系');  --插入一条数据
delete student where stu_name='张三';   --删除一条数据
update student set stu_age=19 where stu_name='李四';  --修改李四的年龄
update student set stu_name='王二' where stu_name='李四';  --修改李四的名称

查询stu_log表的结果如下,第3条update语句没有触发该触发器,因为触发器指定只有修改stu_name字段才会触发触发器


 c、语句级触发器(before触发器):用来控制对表的修改

create or replace trigger modify_stu
before insert or update or delete on student
begin
   if deleting then
     raise_application_error(-20001,'该表不允许删除数据');
   elsif updating then
     raise_application_error(-20002,'该表不允许修改数据');
    elsif inserting then
     raise_application_error(-20003,'该表不允许插入数据');
    end if;
end;

插入数据时报错如下,删除和修改数据同样也报错


 d、语句级触发器(after触发器):略


<think>我们正在讨论Oracle触发器中如何声明和使用变量。根据引用[1]和[2],我们可以总结如下: 在Oracle触发器中,声明变量的位置在触发器定义的DECLARE部分(如果有的话)。但需要注意的是,触发器有两种类型:行级触发器和语句级触发器。在行级触发器使用FOR EACH ROW)中,我们可以使用:OLD和:NEW来引用当前行的旧值和新值,同时也可以在DECLARE部分声明变量。 触发器基本结构: ```sql CREATE [OR REPLACE] TRIGGER trigger_name BEFORE | AFTER | INSTEAD OF [INSERT] [OR] | [UPDATE] [OR] | [DELETE] [OF column_name] ON table_name [REFERENCING OLD AS old NEW AS new] [FOR EACH ROW] -- 如果指定则为行级触发器,否则为语句级触发器 [WHEN (condition)] DECLARE -- 这里声明变量 variable_name datatype [:= initial_value]; BEGIN -- 触发器逻辑 END; ``` 在DECLARE部分,我们可以声明变量,然后在BEGIN和END之间的代码块中使用这些变量。 例如,根据引用[2]中的示例,在触发器内部我们可以这样使用变量: ```sql CREATE OR REPLACE TRIGGER Test_Tri AFTER INSERT ON Test FOR EACH ROW DECLARE v_class VARCHAR2(4); -- 声明一个变量 BEGIN -- 使用变量 IF :new.iValue > 90 THEN v_class := 'A'; -- 给变量赋值 INSERT INTO Test_log VALUES (:new.iKey, v_class); END IF; END; ``` 注意:在行级触发器中,我们可以使用:OLD和:NEW伪记录来访问行的旧值和新值。在语句级触发器中则不能使用。 另外,在触发器中也可以使用常量、游标等。 总结一下步骤: 1. 在触发器定义的DECLARE部分声明变量(可选)。 2. 在BEGIN和END之间的代码块中使用这些变量,包括赋值和读取。 3. 注意变量的作用域只在当前触发器的代码块中。 如果触发器不需要变量,可以省略DECLARE部分。 在引用[2]中还有一个模式级触发器的例子,其中没有使用变量,但同样可以在DECLARE部分声明变量。 注意:在触发器中声明的变量不能与:NEW或:OLD中的列名相同,否则可能会引起混淆。 例如,如果我们有一个列名为`v_class`,那么我们在触发器中声明变量`v_class`就会覆盖列名,此时应该避免。通常,我们可以通过给变量加前缀(如`v_`)来避免这种冲突。 另外,在触发器中给变量赋值使用`:=`操作符。 所以,用户可以在触发器的DECLARE部分声明变量,然后在触发器体中像在PL/SQL块中一样使用它们。 §§相关问题§§ 1. Oracle触发器中的:OLD和:NEW伪记录有什么使用限制? 2. 在Oracle触发器中如何声明和使用游标? 3. Oracle触发器中的异常处理应该如何实现?</think>### Oracle触发器中声明和使用变量的方法 在Oracle触发器中声明和使用变量的语法如下: ```sql CREATE [OR REPLACE] TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name [FOR EACH ROW] -- 行级触发器 DECLARE -- 变量声明区域 variable_name1 DATATYPE [:= initial_value]; -- 声明变量并可选初始化 variable_name2 DATATYPE; BEGIN -- 变量使用区域 variable_name1 := value; -- 变量赋值 :NEW.column_name := expression; -- 修改新值 DBMS_OUTPUT.PUT_LINE(variable_name1); -- 使用变量 END; ``` #### 关键语法说明: 1. **声明位置** 在`DECLARE`和`BEGIN`之间声明变量,支持所有PL/SQL数据类型: ```sql DECLARE v_count NUMBER := 0; -- 数字类型 v_status VARCHAR2(10) := 'NEW'; -- 字符串类型 v_date DATE := SYSDATE; -- 日期类型 ``` 2. **变量赋值** 使用`:=`操作符在`BEGIN-END`块内赋值: ```sql BEGIN v_count := :OLD.quantity * 2; -- 使用旧值计算 v_status := 'UPDATED'; -- 直接赋值 END; ``` 3. **结合:OLD/:NEW伪记录** 在行级触发器中访问数据变更: ```sql CREATE TRIGGER salary_audit BEFORE UPDATE ON employees FOR EACH ROW DECLARE v_diff NUMBER; BEGIN v_diff := :NEW.salary - :OLD.salary; -- 计算差值 IF v_diff > 10000 THEN INSERT INTO audit_log VALUES ('Excessive raise', SYSDATE); END IF; END; ``` #### 注意事项: - 行级触发器(`FOR EACH ROW`)才能使用`:OLD`和`:NEW`伪记录[^1] - 变量作用域仅限于当前触发器 - 使用`DBMS_OUTPUT`调试时需确保服务器输出已启用 - 避免在触发器中进行耗时操作以免影响性能 > 示例:根据订单金额自动计算折扣 > ```sql > CREATE TRIGGER order_discount > BEFORE INSERT ON orders > FOR EACH ROW > DECLARE > v_discount_rate NUMBER; > BEGIN > IF :NEW.amount > 1000 THEN > v_discount_rate := 0.15; -- 15%折扣 > ELSE > v_discount_rate := 0.05; -- 5%折扣 > END IF; > :NEW.discount := :NEW.amount * v_discount_rate; > END; > ```
评论 11
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值