触发器是附加在数据库对象上的一段代码,它指定了触发的时机和事件。
自动触发(指定触发的事件,增,删,改,登录,登出,重启,关闭)可以在某个事件前,后触发(还有一个替代)指定在什么对象上建触发器触发器执行的力度是语句级的还是行级(DML)DDL没有,系统级的也没有行级触发器比较隐蔽,发生问题不好定问,可以考虑是否用存储过程或其它技术来替代。
根据触发器作用的对象分为三种:1.DML触发器;2.替代触发器;3.系统触发器。
DML触发器
DML触发器有语句级和行级两种
语句级触发器
建一个日志表,给emp表创建一个语句级DML触发器,操作数据时在日志表中建日志
CREATE OR REPLACE TRIGGER trg_emp1
BEFORE INSERT OR UPDATE OR DELETE
ON emp
DECLARE
v_event VARCHAR(10);
BEGIN
IF inserting THEN
v_event:='insert';
ELSIF updating THEN
v_event:='update';
ELSE
v_event:='delete';
END IF;
INSERT INTO t_log VALUES(seq1.nextval,USER,SYSDATE,v_event||' on emp ');
END;
INSERT INTO emp(empno) VALUES(1);
INSERT INTO emp(empno) VALUES(2);
DELETE FROM emp WHERE empno IN(1,2);
COMMIT;
行级触发器
给emp表中增加一条记录,查看emp2表,发现成功插入新记录。
再来看一下更新操作的触发器,在上述触发trg_emp2的基础之上进行修改,增加修改和删除的处理。对emp表做更新和删除操作,观察emp2表的数据变化.
-- 创建新表,同原表数据填充
CREATE TABLE emp2 AS SELECT * FROM emp;
--创建新表,只要原表的结构
CREATE TABLE emp3 AS SELECT * FROM emp WHERE 1=2;
CREATE OR REPLACE TRIGGER trg_emp2
BEFORE INSERT OR UPDATE OR DELETE
ON emp
FOR EACH ROW -- 行级
DECLARE
BEGIN
IF inserting THEN
INSERT INTO emp2(empno,ename) VALUES (:new.empno,:new.ename);
ELSIF updating THEN
UPDATE emp2 SET ename=:new.ename WHERE empno=:old.empno;
ELSE
DELETE FROM emp2 WHERE empno=:old.empno;
END IF;
END;
替代触发器
替代视图完成表数据的维护
instead of和before、after是三选一,所以替代触发器没有before和after选项。
CREATE OR REPLACE VIEW v_emp AS SELECT empno,sal+comm sal FROM emp;
SELECT * FROM v_emp;
INSERT INTO v_emp(empno,sal) VALUES(3,100);
CREATE OR REPLACE TRIGGER trg_vemp
INSTEAD OF INSERT ON v_emp
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO emp(empno,sal,comm) VALUES(:new.empno,:new.sal,0);
END;
系统触发器(DDL)
在scott用户下操作数据库对象时可以记录操作日志。
CREATE OR REPLACE TRIGGER trg_dd1
AFTER DDL ON scott.schema
DECLARE
BEGIN
INSERT INTO t_log(ID,Log_User,log_date,log_text)
VALUES(seq1.nextval,USER,SYSDATE,ora_sysevent||'-'||
ora_dict_obj_name||'-'||ora_dict_obj_type);
END;