学习笔记：21触发器

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;