浏览 2633 次
锁定老帖子 主题:学习笔记:21触发器
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2010-01-07
21-1:建立before触发器 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:建立after触发器 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; / 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; / 21-4:建立before行触发器 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:建立after行触发器 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; / 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; 21-12:instead of 触发器 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; 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |