一、语句触发器
语法:CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } event1 [OR event2 OR event3]
ON table_name
PL/SQL block;
21-1:禁止在周六、周日改变EMP表的数据
CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN ('SAT','SUN') THEN
raise_application_error(-20001,
'不能在休息日改变雇员信息');
END IF;
END;
/
21-2:
CREATE TABLE aud_upd_table(
host VARCHAR2(30),statement VARCHAR2(100),exectime DATE);
CREATE OR REPLACE TRIGGER tr_upd_emp
AFTER UPDATE ON emp
DECLARE
sql_txt ora_name_list_t;
v_stmt VARCHAR2(100);
n BINARY_INTEGER;
BEGIN
n:=ora_sql_txt(sql_txt);
FOR i IN 1..n LOOP
v_stmt:=v_stmt||sql_txt(i);
END LOOP;
INSERT INTO aud_upd_table VALUES(
sys_context('userenv','host'),v_stmt,SYSDATE);
END;
/
INSERTING:当触发事件是INSERT语句时,返回TRUE,否则返回FALSE。
UPDATING:当触发事件是UPDATE语句时,返回TRUE,否则返回FALSE。
DELETING:当触发事件是DELETE语句时,返回TRUE,否则返回FALSE。
21-3:
CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN ('SAT','SUN') THEN
CASE
WHEN INSERTING THEN
raise_application_error(-20001,
'不能在休息日执行INSERT');
WHEN UPDATING THEN
raise_application_error(-20002,
'不能在休息日执行UPDATE');
WHEN DELETING THEN
raise_application_error(-20003,
'不能在休息日执行DELETE');
END CASE;
END IF;
END;
/
二、行触发器
语法:CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } event1 [OR event2 OR event3]
ON table_name [REFERENCING OLD AS-old | NEW AS new]
FOR EACH ROW [WHEN condition]
PL/SQL block;
--REFERENCING子句用于指定引用新、旧数据的方式,
--默认情况下使用old操作符引用旧数据,使用new操作符引用新数据。
21-4:确保雇员工资不能低于原工资。
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
IF :new.sal<:old.sal THEN
raise_application_error(-20010,'工资只涨不降');
END IF;
END;
/
21-5:
CREATE TABLE audit_emp_change (
name VARCHAR2(10),oldsal NUMBER(6,2),
newsal NUMBER(6,2),time DATE);
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp
FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM audit_emp_change
WHERE name=:old.ename;
IF v_temp=0 THEN
INSERT INTO audit_emp_change
VALUES(:old.ename,:old.sal,:new.sal,SYSDATE);
ELSE
UPDATE audit_emp_change
SET oldsal=:old.sal,newsal=:new.sal,time=SYSDATE
WHERE name=:old.ename;
END IF;
END;
/
21-6:限制触发器
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp
FOR EACH ROW WHEN (old.job='SALESMAN')
DECLARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM audit_emp_change
WHERE name=:old.ename;
IF v_temp=0 THEN
INSERT INTO audit_emp_change
VALUES(:old.ename,:old.sal,:new.sal,SYSDATE);
ELSE
UPDATE audit_emp_change
SET oldsal=:old.sal,newsal=:new.sal,time=SYSDATE
WHERE name=:old.ename;
END IF;
END;
/
三、组合触发器(11G新特性)
21-7:CREATE OR REPLACE TRIGGER tr_update_sal
FOR UPDATE OF sal ON emp COMPOUND TRIGGER
msg1 VARCHAR2(50):='工资必须在1000到5000之间!';
msg2 VARCHAR2(50):='不能在休息日更新!';
BEFORE STATEMENT IS
BEGIN
IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
IN ('SAT','SUN') THEN
raise_application_error(-20000,msg2);
END IF;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :new.sal NOT BETWEEN 1000 AND 5000 THEN
raise_application_error(-20001,msg1);
END IF;
END AFTER EACH ROW;
END;
/
四、触发器示例
21-8:限制修饰数据的时间。CREATE OR REPLACE TRIGGER tr_emp_time
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF to_char(SYSDATE,'HH24') NOT BETWEEN
'9' AND '17' THEN
raise_application_error(-20101,'非工作时间');
END IF;
END;
/
UPDATE emp SET sal=3200 WHERE empno=7788;
21-9:审计删除
CREATE TABLE audit_delete_emp(name VARCHAR2(10),time DATE);
CREATE OR REPLACE TRIGGER tr_delete_emp
AFTER DELETE ON emp FOR EACH ROW
BEGIN
INSERT INTO audit_delete_emp VALUES(:old.ename,SYSDATE);
END;
/
DELETE FROM emp WHERE empno=7788;
SELECT * FROM audit_delete_emp;
21-10:实现数据完整性
CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal ON emp FOR EACH ROW
WHEN (new.sal<old.sal OR new.sal>1.2*old.sal)
BEGIN
raise_application_error(-20931,
'工资只升不降,并且升幅不能超过20%');
END;
/
UPDATE emp SET sal=sal*1.25 WHERE empno=7788;
21-11:实现参照完整性
CREATE OR REPLACE TRIGGER tr_update_cascade
AFTER UPDATE OF deptno ON dept FOR EACH ROW
BEGIN
UPDATE emp SET deptno=:new.deptno
WHERE deptno=:old.deptno;
END;
/
UPDATE dept SET deptno=50 WHERE deptno=10;
SELECT ename FROM emp WHERE deptno=50;
五、INSTEAD OF触发器
当复杂视图子查询包含有集合操作符、分组函数、distinct关键字或者连接查询,那么将禁止在该视图上执行DML操作。为了在这些复杂视图上执行DML操作,需要建立INSTEAD OF触发器。
INSTEAD OF触发器只适用于视图。
INSTEAD OF触发器不能指定BEFORE和AFTER选项。
不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。
INSTEAD OF触发器必须包含FOR EACH ROW选项。
21-12:
CREATE OR REPLACE VIEW dept_emp AS
SELECT a.deptno,a.dname,b.empno,b.ename FROM dept a,emp b
WHERE a.deptno=b.deptno;
CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM dept WHERE deptno=:new.deptno;
IF v_temp=0 THEN
INSERT INTO dept (deptno,dname) VALUES(:new.deptno,:new.dname);
END IF;
SELECT count(*) INTO v_temp FROM emp WHERE empno=:new.empno;
IF v_temp=0 THEN
INSERT INTO emp (empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);
END IF;
END;
/
INSERT INTO dept_emp VALUES(50,'ADMIN',1223,'MARY');
SELECT dname FROM dept WHERE deptno=50;
SELECT ename FROM emp WHERE empno=1223;
六、事件触发器
21-13:数据库启动之后触发。conn sys/oracle as sysdba
create table event_table(event varchar2(30),time date);
CREATE OR REPLACE TRIGGER tr_startup
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
SHUTDOWN
STARTUP
SELECT * FROM event_table;
21-14:用户登录之后触发。
conn sys/oracle as sysdba
CREATE TABLE aud_logon_tab(
username VARCHAR2(20),time DATE,addr VARCHAR2(20));
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO aud_logon_tab VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;
/
conn scott/tiger@test
SELECT * FROM sys.aud_logon_tab;
21-15:DDL触发器
conn sys/oracle as sysdba
CREATE TABLE aud_ddl_tab(
event VARCHAR2(20),username VARCHAR2(10),
owner VARCHAR2(10),objname VARCHAR2(20),
objtype VARCHAR2(10),time DATE);
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON scott.schema
BEGIN
INSERT INTO aud_ddl_tab VALUES(
ora_sysevent,ora_login_user,ora_dict_obj_owner,
ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
END;
/
conn scott/tiger@test
CREATE TABLE temp(cola INT);
DROP TABLE temp;
SELECT username,event,objtype,objname FROM sys.aud_ddl_tab;
七、维护触发器
21-16:set long 800
set pagesize 400
SELECT trigger_body FROM user_triggers WHERE trigger_name='TR_UPD_EMP';
21-17:
ALTER TRIGGER tr_upd_emp DISABLE;
21-18:
ALTER TRIGGER tr_upd_emp ENABLE;
21-19:
ALTER TRIGGER tr_upd_emp COMPILE;
21-20:
DROP TRIGGER tr_check_sal;