我的书签
添加书签
移除书签11.2.3 DML类型触发器
该类型触发器在日常开发中比较常用，前面已经介绍过DML触发器主要针对表的INSERT、UPDATE、DELETE操作。本小节将演示多个示例以增强对该类型触发器的了解，通过该小节将理解什么是前触发、后触发以及行级、语句级触发等。
【示例3】创建行级触发器
要求创建行级触发器，当在PRODUCTINFO表中增加数据时将激发该触发器，并把所做的操作记录到表OPERATION_LOG中。该触发器属于单一事件的触发器。创建行级触发器分为以下3个步骤：
1）创建操作事件记录表。该表数据结构如表11.3所示。
【语法说明】
❑记录ID：该ID作为LOG_TAB表的主键，触发器中实现该功能比较可取的方式是使用序列自动生成。
❑被操作的表名：此字段记录了DML操作的表。
❑被操作表的主键：利用主键可以得到具体的记录，非常有实用价值。
❑OPER_KD：记录INSERT、UPDATE、DELETE操作。
❑OPER_DATE：记录DML操作的时间，这里直接使用Oracle的系统时间。
登录SQL*Plus，执行如下脚本，创建事件记录表LOG_TAB。
01 CREATE TABLE LOG_TAB
02 (
03 ID VARCHAR2(10)not null,
04 OPER_TABLE VARCHAR2(20),
05 OPER_KD VARCHAR2(10),
06 OPER_TABLE_PRK VARCHAR2(50),
07 OPER_DATE DATE,
08 constraint LOG_TAB_PRK primary key(ID)
09 )
10 /
如果执行成功将会出现如下字样：
表已创建
2）创建用做LOG_TAB表主键的自增长序列。
在SQL*Plus中执行如下脚本：
01 CREATE SEQUENCE LOG_TAB_ID
02 MINVALUE 1000000000
03 MAXVALUE 9999999999
04 START WITH 1000000000
05 INCREMENT BY 1
/
如果执行成功将会出现如下字样：
序列已创建
【代码解析】
❑第1行表示创建名为LOG_TAB_ID的序列。
❑第2行表示该序列的最小值为1 000 000 000。
❑第3行表示该序列的最大值为9 999 999 999。
❑第4行表示该序列的值从1 000 000 000开始。
❑第5行表示该序列增长量为1。
表中主键字段的值不允许重复，在Oracle中不能直接设置某字段为自增长字段以达到符合主键值要求的目的，这种情况下的解决方法就是利用序列生成的值作为表主键的值。
3）创建触发器。
01 CREATE TRIGGER PRODUCTINFO_OPER_TGR
02 BEFORE INSERT
03 ON PRODUCTINFO
04 FOR EACH ROW
05 BEGIN
06 IF INSERTING THEN
07 INSERT INTO LOG_TAB
08 VALUES
09 (LOG_TAB_ID.NEXTVAL,
10 'PRODUCTINFO',
11 'INSERT',
12 :NEW.PRODUCTID,
13 SYSDATE);
14 DBMS_OUTPUT.put_line('插入数据主键是'||:new.PRODUCTID);
15 END IF;
16 END;
17 /
【代码解析】
❑第1行表示创建名为PRODUCTINFO_OPER_TGR的触发器。
❑第2行表示该触发器为前触发，触发事件是INSERT。
❑第3行表示该触发器作用在表PRODUCTINFO上。
❑第4行表示该触发器为行级触发，也就是说每增加一行就会触发一次。
❑第6行表示判断如果是插入数据操作则进入IF语句。
❑第7～13行表示向事件记录表中增加数据。其中第9行的LOG_TAB_ID.NEXTVAL表示得到序列的下一个值，第13行的SYSDATE表示增加数据时的系统时间。
❑第14行输出增加数据的PRODUCTID字段值，该字段是'PRODUCTINFO'表的主键。
行级的触发器里使用:new或:old来访问变更前和变更后的数据。其中，如果是增加新记录操作，则只有:new可以访问；如果是修改操作，则:new和:old都可以访问，:new表示修改后的记录，:old表示修改前的记录；而删除则只有:old可以访问，因为该操作是删除已有的记录。
【执行效果】
在SQL*Plus中执行成功后提示如下：
触发器已创建
【验证触发器】
在表PRODUCTINFO中增加数据并查看是否正常激发触发器。脚本如下：
INSERT INTO PRODUCTINFO VALUES('0240090001','触发器测试',1000,0,0000000000,'','测试');
【执行效果】
插入数据完成，主键是0240090001
已创建1行
继续查询表PRODUCTINFO和表LOG_TAB，检查是否有对应数据增加。两个表查询结果见图11.3。
图 11.3 两个表新增的数据
以上是一个入门型的示例，该触发器中只包含了一种触发事件，就是增加数据的时候触发。但我们可以在一个触发器中添加多个触发事件，只需要把它们利用条件语句分开即可。下面一个示例演示了如何在一个触发器中使用多种触发事件。
【示例4】在触发器中使用多种触发事件
把示例3改为当增加、修改、删除记录时都能触发，记录操作事件的表（LOG_TAB）不变。该类型触发器属于多个事件的触发器。修改后的脚本如下：
01 CREATE TRIGGER PRODUCTINFO_OPER_DML_TGR
02 AFTER INSERT OR UPDATE OR DELETE
03 ON PRODUCTINFO
04 FOR EACH ROW
05 BEGIN
06 CASE
07 WHEN INSERTING THEN—增加操作
08 INSERT INTO LOG_TAB
09 VALUES
10 (LOG_TAB_ID.NEXTVAL,
11 'PRODUCTINFO',
12 'INSERT',
13 :NEW.PRODUCTID,
14 SYSDATE);
15 DBMS_OUTPUT.PUT_LINE('插入数据完成，主键是'||:NEW.PRODUCTID);
16 WHEN UPDATING THEN—修改操作
17 INSERT INTO LOG_TAB
18 VALUES
19 (LOG_TAB_ID.NEXTVAL,
20 'PRODUCTINFO',
21 'UPDATE',
22 :OLD.PRODUCTID,
23 SYSDATE);
24 DBMS_OUTPUT.PUT_LINE('修改数据完成，修改数据主键是'||:OLD.PRODUCTID);
25 WHEN DELETING THEN—删除操作
25 INSERT INTO LOG_TAB
27 VALUES
28 (LOG_TAB_ID.NEXTVAL,
29 'PRODUCTINFO',
30 'DELETE',
31 :OLD.PRODUCTID,
32 SYSDATE);
33 DBMS_OUTPUT.PUT_LINE('删除数据完成，删除数据主键是'||:OLD.PRODUCTID);
34 END CASE;
35 END;
36 /
【代码解析】
❑第1行表示创建名为PRODUCTINFO_OPER_DML_TGR的触发器。
❑第2行表示在增加、修改、删除操作后触发。
❑第3行表示该触发器作用在PRODUCTINFO表上。
❑第4行表示行级触发。
❑第6行表示使用CASE语句区分各种操作。
❑第7～1 5行表示当增加数据时，向事件记录表中增加数据并输出到屏幕。其中LOG_TAB_ID.NEXTVAL表示利用序列作为新增数据的主键，:NEW.PRODUCTID表示新增数据的产品ID。
❑第1 6～2 4行表示当修改数据时，向事件记录表中增加数据并输出到屏幕。:OLD.PRODUCTID表示修改前该字段的值。
❑第2 5～3 3行表示当删除数据时，向事件记录表中增加数据并输出到屏幕。:OLD.PRODUCTID表示删除前该字段的值。
【执行效果】
在SQL*Plus下执行脚本，如果成功，则提示如下：
触发器已创建
【验证触发器】
该触发器是行级触发器，当修改或删除多条记录时会触发多次，而且如果没有数据被修改，即使执行删除或修改操作都不会激发该触发器。在表PRODUCTINFO上分别执行增加、修改、删除操作，查看该触发器是否被正常触发。具体操作步骤如下：
1）对PRODUCTINFO表执行DML操作。
为PRODUCTINFO表增加一条记录：
INSERT INTO PRODUCTINFO VALUES('0240090001','触发器测试',1000,0,0000000000,'','测试');
修改PRODUCTINFO表记录，把所有的DESPERATION字段值置成TEST字符串：
UPDATE PRODUCTINFO SET DESPERATION='TEST';
删除表PRODUCTINFO中的一条记录：
DELETE FROM PRODUCTINFO WHERE PRODUCTID='0240090001';
以上3条语句执行完成后提示如图11.4所示。
图 11.4 DML操作表PRODUCTINFO提示
2）检查事件记录表的数据是否成功。执行如下查询SQL，查询结果如图11.5所示。
SELECT*FROM LOG_TAB;
图 11.5 LOG_TAB表记录
对比图11.4和图11.5，可以发现图11.5中LOG_TAB的记录同图11.4中的操作是对应的，也就是说该触发器成功地激发了。该例利用CASE语句对3种不同触发方式分别进行操作，当然也可以利用IF语句实现同样的功能。其中在执行UPDATE操作的时候修改了多条记录，因为是行级触发，那么该触发器会触发多次，把所有修改的数据的主键都得到了，并把相关数据放进了事件记录表。
【示例5】在触发器中使用IF语句
该示例将使用IF语句进行条件判断，如果修改数据的日期是当月的25日，并且修改产品的价格高于3000，那么该修改操作将被终止。
注意 这两个条件必须同时满足，否则数据可以正常修改，并把操作记录存入表LOG_TAB中。具体脚本如下：
01 CREATE TRIGGER PRODUCTINFO_OPER_CHK_TGR
02 BEFORE UPDATE OF PRODUCTPRICE ON PRODUCTINFO
03 FOR EACH ROW
04 BEGIN
05 IF(TO_CHAR(SYSDATE,'dd')=25 AND:OLD.PRODUCTPRICE＞3000)THEN
06 RAISE_APPLICATION_ERROR(-20000,
07 '今天是25号，不允许修改价格高于3000的数据！');
08
09 INSERT INTO LOG_TAB
10 VALUES
11 (LOG_TAB_ID.NEXTVAL,
12 'PRODUCTINFO',
13 'INSERT',
14 :NEW.PRODUCTID,
15 SYSDATE);
16 DBMS_OUTPUT.PUT_LINE('修改数据完成，主键是'||:NEW.PRODUCTID);
17
18 END;
19 /
【代码解析】
❑第1行表示创建名为PRODUCTINFO_OPER_CHK_TGR的触发器。
❑第2行表示该触发器为前触发，作用在PRODUCTINFO表的PRODUCTPRICE字段，当修改该字段时触发器会被激发。
❑第3行表示行级触发。
❑第5行使用IF语句判断如果系统时间中包含25，也就是说当日是25号，并且修改前的价格大于3000，则进入IF语句内部。
❑第6行表示错误提示。RAISE_APPLICATION_ERROR可以把应用程序错误传递到客户端，它包含两个参数，第一个参数是错误代码，可以写-20 999～-20 000之间的数值；第二个参数是错误提示。
❑第9～15行表示当不符合判断条件时能正常修改数据，并把操作记录增加到LOG_TAB表中。
❑第16行表示输出提示。
【执行效果】
在SQL*Plus下执行脚本，如果成功，则提示如下：
触发器已创建
【验证触发器】
1）修改PRODUCTINFO表中主键为0240030002的数据，该记录价格为3600，执行如下的脚本：
UPDATE PRODUCTINFO SET PRODUCTPRICE = 2000 WHERE PRODUCTID = '0240030002';
如果当日为25号，则会出现如图11.6所示的效果。
图 11.6 修改价格时中断操作
2）修改PRODUCTINFO表中主键为0240050001的数据，该记录价格为400，这种情况下即使日期为当月的25号，也会正常通过。效果如图11.7所示。
图 11.7 数据修改正常通过
利用IF条件可以方便地终止不符合业务逻辑的数据操作，当出现自定义的异常时，SQL语句会在执行操作前就终止。
【示例6】使用WHEN限制条件的触发器
该触发器完成的功能是当在表PRODUCTINFO中增加的数据是“雨具”类型时，需要把当前的价格改为打九折，需要利用WHEN子句完成数据验证。具体触发器脚本如下：
01 CREATE TRIGGER PRODUCTINFO_WHEN_OPER_CHK__TGR
02 BEFORE INSERT ON PRODUCTINFO
03 FOR EACH ROW
04 WHEN(NEW.CATEGORY='0100050001')
05 BEGIN
06 DBMS_OUTPUT.PUT_LINE('原价格：'||:NEW.PRODUCTPRICE);
07 :NEW.PRODUCTPRICE:=:NEW.PRODUCTPRICE*0.9;
08 DBMS_OUTPUT.PUT_LINE('打折后价格：'||:NEW.PRODUCTPRICE);
09 END;
10 /
【代码解析】
❑第1行表示创建名为PRODUCTINFO_WHEN_OPER_CHK__TGR的触发器。
❑第2行表示该触发器作用在表PRODUCTINFO上，触发事件是增加数据，触发方式为前触发。
❑第3行表示该触发器为行级触发器。
❑第4行表示限制条件，脚本字面解释为触发器激发前增加的数据中CATEGORY字段为0100050001的记录，也就是说增加的数据是“雨具”类型时将激发该触发器。
❑第7行表示把增加数据的价格打九折，然后再存入表中。
【执行效果】
在SQL*Plus下执行脚本，如果成功，则提示如下：
触发器已创建
【验证触发器】
对表PRODUCTINFO执行INSERT操作，增加两条记录。具体脚本如下：
01 INSERT INTO PRODUCTINFO VALUES('0240090001','触发器测试',1000,0,'0100020001','','测试1');
02 INSERT INTO PRODUCTINFO VALUES('0240090002','触发器测试',1000,0,'0100050001','','测试2');
其中第1行脚本表示增加一条类型为“路由器”的记录，第2行表示增加一条类型为“雨具”的记录。这两条记录的增加过程以及最终结果见图11.8。
图 11.8 验证触发器结果
从图11.8中可以发现第一条记录的价格没有变，依然是1000，而第二条记录的价格则变成了900，这说明该触发器成功激发并且WHEN选项限制条件准确。
利用WHEN限制条件可以比较精确地限制触发器的激发条件，每当操作特定数据时可以考虑使用该条件限制，使得触发器更加灵活。需要说明的是，在WHEN条件中的:NEW或:OLD使用方式和通常使用方式上稍微有点差别，这里直接使用NEW或OLD关键词得到数据，没有前面的冒号。
注意 如果想修改:NEW引用的数据（类似脚本第7行的操作），那么该触发器应为前触发的方式。因为后触发方式中:NEW数据是不允许被修改的，只能被调用，即使能修改也没有任何意义。
为了保证数据的完整性，在某种情况下开发人员会利用触发器实现级联操作功能，如级联修改或级联删除等。
【示例7】实现级联修改的触发器
该示例将提供级联修改产品类型编码的功能，并将原来的编码存放到CATEGROYINFO_BAK（表结构参考CATEGROYINFO）表中。具体来说，就是当表CATEGROYINFO的编码发生变化时，PRODUCTINFO表中CATEGORY字段与之对应的编码同样要变化。分为如下两个步骤实现：
1）创建备份产品类型编码表CATEGROYINFO_BAK。脚本如下：
CREATE TABLE CATEGROYINFO_BAK AS SELECT*FROM CATEGROYINFO WHERE 1＜＞1
该语句表示创建表CATEGROYINFO_BAK，其表结构复制CATEGROYINFO的表结构，但不包括复制数据。如果不包含WHERE后面的条件则将完全复制CATEGROYINFO表，包括里面的数据。
2）创建触发器。具体脚本如下：
01 CREATE TRIGGER MUTLI_OPER_CHK__TGR
02 AFTER UPDATE OF CATEGROYID ON CATEGROYINFO
03 FOR EACH ROW
04 BEGIN
05 UPDATE PRODUCTINFO
06 SET CATEGORY=:NEW.CATEGROYID
07 WHERE CATEGORY=:OLD.CATEGROYID;
08 INSERT INTO CATEGROYINFO_BAK VALUES(:OLD.CATEGROYID,:OLD.CATEGROYNAME);
09
10 DBMS_OUTPUT.PUT_LINE('数据已存入CATEGROYINFO_BAK表中');
11 END;
12 /
【代码解析】
❑第1行表示创建名为MUTLI_OPER_CHK__TGR的触发器。
❑第2行表示该触发器在修改CATEGROYINFO表的CATEGROYID字段后触发。
❑第3行表示该触发器为行触发。
❑第5～7行表示修改PRODUCTINFO中CATEGORY字段的数据，修改后的数据同CATEGROYINFO表数据一致。其中:NEW.CATEGROYID表示CATEGROYINFO表修改后的数据，:OLD.CATEGROYID为修改前的数据。
❑第8行表示备份修改前的数据到表CATEGROYINFO_BAK中。
【执行效果】
在SQL*Plus下执行脚本，如果成功，则提示如下：
触发器已创建
【验证触发器】
验证触发器也分为两个步骤：
1）修改CATEGROYINFO表的CATEGROYID字段。SQL语句如下：
UPDATE CATEGROYINFO SET CATEGROYID='0100010000'
WHERE CATEGROYID='0100010001'
2）查询PRODUCTINFO、CATEGROYINFO以及CATEGROYINFO_BAK，确认数据修改正确。查询结果见图11.9。
图 11.9 触发器数据验证
在很多情况下由于数据库设计的原因，两个表中字段很可能出现级联的关系，这种情况最常见的就在字典表和业务表中。由于业务表使用了字典数据，所以如果字典表发生变化会造成数据的不一致性，因此需要在字典表数据发生变化的同时修改其他表中的该数据。
以上介绍的都是行级触发器，与其对应的是语句级触发器。语句级触发器适合用在整张表的操作控制上，因为它只对语句有效，也就是当执行语句时触发一次，与修改多少条记录没有关系。
【示例8】语句级触发器
该示例将通过语句级触发器控制每个月的1号晚上23点到0点不允许操作表中数据。具体脚本如下：
01 CREATE TRIGGER PRODUCTINFO_STMT_CHK_TGR
02 BEFORE INSERT OR UPDATE OR DELETE ON PRODUCTINFO
03 BEGIN
04 IF(TO_CHAR(SYSDATE,'dd')=1 AND TO_CHAR(SYSDATE,'HH24')='23')THEN
05 RAISE_APPLICATION_ERROR(-20000,'当前时段不允许修改数据！');
06 END IF;
07 END;
【代码解析】
❑第1行表示创建名为PRODUCTINFO_STMT_CHK_TGR的触发器。
❑第2行表示该触发器在增加、修改、删除前触发。
❑第4行表示判断每月的1号23:00～0:00不允许修改数据。
❑第5行表示把应用程序的错误提到客户端。
【执行效果】
在SQL*Plus下执行脚本，如果成功，则提示如下：
触发器已创建
【验证触发器】
验证该触发器可以向PRODUCTINFO表增加或修改、删除数据，这里以增加做测试语句。SQL语句如下：
INSERT INTO PRODUCTINFO VALUES('0240090001','触发器测试',1000,0,0000000000,NULL,'测试');
执行效果如图11.10所示。
图 11.10 验证语句级触发器
利用语句级触发器可以达到在某个条件范围内用户对表数据操作进行限制的目的。
