5.创建触发器。
CREATE OR REPLACE TRIGGER tri_first
AFTER DELETE
ON
t_procedure
BEGIN
IF deleting THEN
dbms_output.put_line('我的第一个触发器');
END IF;
END;
/
SQL> set serveroutput on
SQL> delete from t_procedure where id=5;
我的第一个触发器
1 row deleted
SQL> select * from T_PROCEDURE;
ID NAME SHULIANG TYPE JIAGE TYPE_ID
---------- ---------- ---------- ---------- ---------- ----------
6 双人床 3 家具 1000 2
2 苹果 100 水果 1
1 芒果 100 水果 1
4 小米4 50 电子 1499 3
3 mini平板 20 电子 2000 3
6.查看触发器。
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
SELECT * FROM USER_SOURCE WHERE NAME='TRIGGER_NAME' ORDER BY LINE;
7.DML类型触发器。
例1到5都是行级触发器。
例1：
----创建日志表
CREATE TABLE log_cfq
(ID VARCHAR2(1000),
oper_table VARCHAR2(100),
oper_table_prk VARCHAR2(100),
oper_kd VARCHAR2(100),
oper_date DATE,
CONSTRAINT log_cfq_prk PRIMARY KEY (ID));
-----创建序列号
CREATE SEQUENCE log_cfq_id
MINVALUE 1000000000
MAXVALUE 9999999999
START WITH 1000000000
INCREMENT BY 1
/
-----日志表记录插入
CREATE OR REPLACE TRIGGER tri_dml
BEFORE INSERT
ON productinfo
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO log_cfq VALUES
(log_cfq_id.nextval,
'productinfo',
:NEW.productid,
'insert',
SYSDATE
);
dbms_output.put_line('插入的数据是'||:new.productid);
END IF;
END;
/
-------------------------------------------------------------
SQL> set serveroutput on
SQL> insert into productinfo values('0240090002','触发器测试',1000,0,0000000001,'','测试1');
插入的数据是0240090002
1 row inserted
例2：在触发器中使用多种触发类型
CREATE OR REPLACE TRIGGER tri_dml_2
AFTER INSERT OR UPDATE OR DELETE
ON productinfo
FOR EACH ROW
BEGIN
CASE
WHEN inserting THEN
INSERT INTO log_cfq VALUES(log_cfq_id.nextval,'productinfo',:new.productid,'insert',SYSDATE);
dbms_output.put_line('已插入数据，新数据的id主键为：'||:new.productid);
WHEN UPDATing THEN
INSERT INTO log_cfq VALUES(log_cfq_id.nextval,'productinfo',:old.productid,'update',SYSDATE);
dbms_output.put_line('已更新数据，数据的id主键为：'||:old.productid);
WHEN deleting THEN
INSERT INTO log_cfq VALUES(log_cfq_id.nextval,'productinfo',:old.productid,'delete',SYSDATE);
dbms_output.put_line('已删除数据，删除数据的id主键为：'||:old.productid);
END CASE;
END;
/
SQL> update productinfo set productid='0240090003' where productid='0240090001';
已更新数据，数据的id主键为：0240090001
1 row updated
例3.在触发器中使用if语句。
CREATE OR REPLACE TRIGGER tri_if
BEFORE UPDATE OF productprice
ON productinfo
FOR EACH ROW
BEGIN
IF (to_char(SYSDATE,'dd')=6 AND :old.productprice>1000) THEN
raise_application_error(-20000,'今天是6号，不允许修改价格高于1000的数据'); ----只能用20000到30000之间的数值
INSERT INTO log_cfq VALUES(Log_Cfg_Id.Nextval,'productinfo',:new.productid,'insert',SYSDATE);
dbms_output.put_line('已经修改数据，插入数据的主键id:'||:new.productid);
END IF;
END;
/
SQL> update productinfo set productprice='3000' where productid='0240090003';
update productinfo set productprice='3000' where productid='0240090003'
ORA-20000: 今天是6号，不允许修改价格高于1000的数据
ORA-06512: 在 "lentim.TRI_IF", line 3
ORA-04088: 触发器 'LENTIM.TRI_IF' 执行过程中出错
例4.使用when限制语句。
CREATE OR REPLACE TRIGGER tri_when
BEFORE INSERT
ON productinfo
FOR EACH ROW
WHEN (new.category='0100010001')
BEGIN
dbms_output.put_line('输入的原价格为：'||:new.productprice);
:new.productprice := :new.productprice*0.9;
dbms_output.put_line('打9折后的价格为'||:new.productprice);
END;
/
SQL> INSERT INTO productinfo VALUES('0240010001','天堂伞测试','59.00','50','0100010001','','测试');
输入的原价格为：59
打9折后的价格为53.1
例5.实现级联修改的触发器。
CREATE OR REPLACE TRIGGER tri_jilian
BEFORE UPDATE OF categoryid ON categoryinfo
FOR EACH ROW
BEGIN
--更新产品表的产品类型id;
UPDATE productinfo SET productinfo.category=:new.categoryid WHERE category=:OLD.categoryid;
--把产品类型表的更改写入到log表中;
INSERT INTO categroyinfo_log VALUES(:old.categoryid,:old.categoryname);
dbms_output.put_line('已更新产品类型，并保存记录到categroyinfo_log,旧记录的id为：'||:old.categoryid);
END;
/
SQL> update categoryinfo set categoryid='0100010002' where categoryid='0100010001';
已更新产品类型，并保存记录到categroyinfo_log,旧记录的id为：0100010001
1 row updated
SQL> select * from produceinfo;
CATEGROYID CATEGROYNAME
---------- --------------------
0100010001 雨具
SQL> select * from productinfo;
PRODUCTID PRODUCTNAME PRODUCTPRICE QUANTITY CATEGORY DESPERATION ORIGIN
---------- -------------------- ------------ ----------- ---------- ------------------------------------------- ----------
0240010001 天堂伞 59.00 50 0100010002 杭州
0240040001 夏普LGD-45G100A 7000.00 20 0100030001 日本
0240030001 海尔XQB50-919A 1100.00 29 0100030002 中国
0240030002 三星WF-R1065S/XSC 3600.00 12 0100030002 中国
0240030003 三星XQB55-T86A/XSC 2500.00 111 0100030002 中国
0240050001 台电C436T 400.00 129 0100040001 中国
0240020001 思科RV082路由器 2800.00 22 0100020001 中国
0240010001 天堂伞测试 53.10 50 0100010002 测试
8 rows selected
例6.语句级触发器。
CREATE OR REPLACE TRIGGER TRI_YUJU
BEFORE INSERT OR UPDATE OR DELETE ON PRODUCTINFO
BEGIN
IF (TO_CHAR(SYSDATE, 'dd') = 6 AND TO_CHAR(SYSDATE, 'hh24') > 13) THEN
RAISE_APPLICATION_ERROR(-20001, '当前时间段不允许修改数据');
END IF;
END;
/
SQL> update productinfo set productinfo.productprice='100' where productinfo.productname='雨伞';
update productinfo set productinfo.productprice='100' where productinfo.productname='雨伞'
ORA-20001: 当前时间段不允许修改数据
ORA-06512: 在 "LENTIM.TRI_YUJU", line 3
ORA-04088: 触发器 'LENTIM.TRI_YUJU' 执行过程中出错
8.触发器的执行顺序。(oracle11g及以上才有的特性，同类型同事件的触发器在10G前都是随机执行的。11g可以用follows来控制)
1.前语句级---2.前行级----3.后行级----4.后语句级
例：
CREATE OR REPLACE TRIGGER TRI_YUJU
BEFORE INSERT OR UPDATE OR DELETE ON PRODUCTINFO
FOLLOW TRIGGER_NAME ----FOLLOW触发器表示限定跟随在这个触发器后面触发。
BEGIN
IF (TO_CHAR(SYSDATE, 'dd') = 6 AND TO_CHAR(SYSDATE, 'hh24') > 13) THEN
RAISE_APPLICATION_ERROR(-20001, '当前时间段不允许修改数据');
END IF;
END;
/
9.复合类型触发器。（compound triggers）
组合触发器指南
a- 组合触发器合并了所有的定时触发器
b- 组合触发器仅限执行 DML 操作，不支持 DDL 和系统操作
c- 你只可在行级代码块中使用 :OLD 和 :NEW 变量标识符 (BEFORE EACH ROW, AFTER EACH ROW)
d- 不支持 PRAGMA_AUTONOMOUS_TRANSACTION
e- 你可使用 WHEN 语句来提升触发器性能，但组合触发器不支持
f- 所有的定时触发器共享一个声明部分中的变量，直到事务结束。
g- 允许在组合触发器中重复执行某定时触发器
h- INSERTING, UPDATING 和 DELETING 谓词在组合触发器中仍然有效
i- 解决变异表错误 Resolve mutating table error (ORA-04091)
组合触发器带来的好处：
1- 在一个地方实现事务(?)
2- 所有触发器可共享声明段
3- 维护单元
4- 解决变异表错误 (ORA-04091)
复合类型属oracle11g新特性，是触发器的增强部分。包含下面4个类型的触发类型。
语句之前：before statement
行之前：before row
行之后：after row
语句之后：after statement
例：
利用该类型触发器可以方便地解决ora-04091错误。触发器不能对正在被dml操作修改的表进行操作。
CREATE OR REPLACE TRIGGER tri_fuhe_error
BEFORE UPDATE ON t_avg
FOR EACH ROW
DECLARE
avg_1 NUMBER(10,2):=0.0;
BEGIN
SELECT avg(jiage) INTO avg_1 FROM t_avg WHERE jiage<45;
IF :new.jiage-:old.jiage>avg_1*0.2 THEN
dbms_output.put_line('操作的数据有问题！');
END IF;
END;
SQL> UPDATE t_avg SET jiage=jiage+30 WHERE ID=3;
UPDATE t_avg SET jiage=jiage+30 WHERE ID=3
ORA-04091: 表 LENTIM.T_AVG 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "LENTIM.TRI_FUHE_ERROR", line 4
ORA-04088: 触发器 'LENTIM.TRI_FUHE_ERROR' 执行过程中出错
-------------------------------------------------
CREATE OR REPLACE TRIGGER tir_fuhe
FOR UPDATE ON t_avg COMPOUND TRIGGER ----注意for update compound trigger的运用。
avg_2 NUMBER(10,2):=0.0;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(jiage) INTO avg_2 FROM t_avg WHERE jiage<45;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :new.jiage-:old.jiage>avg_2*0.2 THEN
raise_application_error(-20002,'操作的数据有问题！');
END IF;
END AFTER EACH ROW;
END;
/
SQL> UPDATE t_avg SET jiage=jiage+30 WHERE ID=3;
UPDATE t_avg SET jiage=jiage+30 WHERE ID=3
ORA-20002: 操作的数据有问题！
ORA-06512: 在 "LENTIM.TIR_FUHE", line 10
ORA-04088: 触发器 'LENTIM.TIR_FUHE' 执行过程中出错
10.利用instead of 解决视图更新不了的问题。
--创建测试视图
CREATE OR REPLACE VIEW view_int AS
SELECT DISTINCT t.id,t.name,t.shuliang,t.type,t.jiage FROM t_avg t ORDER BY t.id;
--创建触发器执行视图更新
CREATE OR REPLACE TRIGGER tri_int
INSTEAD OF INSERT ON view_int
DECLARE
v_typeid VARCHAR2(10);
BEGIN
SELECT typeid INTO v_typeid FROM t_type WHERE typename=:NEW.type;
INSERT INTO t_avg VALUES(:new.id,:new.name,:new.shuliang,v_typeid,:new.jiage);
END;
/
--验证
SQL> INSERT INTO view_int VALUES('3','荔枝','50','水果','5');
1 row inserted
11.DDL触发器。
CREATE OR REPLACE TRIGGER TRI_DDL
BEFORE CREATE OR ALTER OR DROP OR RENAME ON LENTIM.SCHEMA
BEGIN
IF SYSEVENT = 'CREATE' THEN
DBMS_OUTPUT.PUT_LINE(DICTIONARY_OBJ_NAME || '正在新增中。。。');
ELSIF SYSEVENT = 'DROP' THEN
RAISE_APPLICATION_ERROR(-20000, '不允许删除表');
ELSIF SYSEVENT = 'ALTER' THEN
IF DICTIONARY_OBJ_NAME = 'T_AVG' THEN
RAISE_APPLICATION_ERROR(-20000, '不允许修改t_avg表');
END IF;
ELSIF SYSEVENT = 'RENAME' THEN
RAISE_APPLICATION_ERROR(-20000, '不允许修改表名');
END IF;
END;
/
--验证：
SQL> drop table t_avg_2;
drop table t_avg_2
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20000: 不允许删除表
ORA-06512: 在 line 5
SQL> alter table t_avg add test4 varchar2(10);
alter table t_avg add test4 varchar2(10)
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20000: 不允许修改t_avg表
ORA-06512: 在 line 8
12.用户和系统事件触发器。
--创建log表。
CREATE TABLE t_log_user (
logonid VARCHAR2(10),
logonname VARCHAR2(10),
logontime DATE,CONSTRAINT pri_logonid PRIMARY KEY(logonid));
--创建序列
CREATE SEQUENCE s_log
START WITH 1
INCREMENT BY 1
/
--创建触发器
CREATE OR REPLACE TRIGGER tri_log_time
AFTER logon ON DATABASE
BEGIN
INSERT INTO t_log_user VALUES(s_log.nextval,sys.Login_User,SYSDATE);
END;
/
--验证
SQL> select * from t_log_user;
LOGONID LOGONNAME LOGONTIME
---------- ---------- -----------
1 LENTIM 15/9/9 下午 3
2 LENTIM 15/9/9 下午 3
评论回复