触发器

练习 1:DML触发器

---------------------------------------------------------------------------------
create or replace trigger e_update
before update of sal on e
for each row 
begin
  if updating then
    raise_application_error(-20001,'salary do not modify!');
  end if;
end;
/


练习 2:DML触发器限制数据修改
---------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER secure_emp
before INSERT ON e
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '09'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'insert into table E！');
  END IF;
END;
/


练习 3:更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER UPDATE OF deptno ON d FOR EACH ROW 
BEGIN
  IF (UPDATING AND :old.deptno != :new.deptno) THEN --关联标识（保留结构）
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
/


练习 4:删除d表数据时自动删除e表数据
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER DELETE ON d FOR EACH ROW 
BEGIN
  IF DELETING THEN
    delete e
    WHERE deptno = :old.deptno;
  END IF;
END;
/


练习 5:删除d表数据时将e表deptno置空
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER DELETE ON d FOR EACH ROW 
BEGIN
  IF DELETING THEN
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
/


练习 6:删除d表数据时将e表deptno置空，更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER delete or UPDATE OF deptno ON d FOR EACH ROW 
BEGIN
  IF deleting or (UPDATING AND :old.deptno != :new.deptno) THEN
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
/


练习 7:删除d表数据时自动删除e表数据，更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER delete or UPDATE OF deptno ON d FOR EACH ROW 
BEGIN
  IF (UPDATING AND :old.deptno != :new.deptno) THEN
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  elsif deleting then 
    delete e
    WHERE deptno = :old.deptno;    
  END IF;
END;
/


练习 8:trigger不能含有事物处理的语句!需要使用 自制事务！
---------------------------------------------------------------------------------
create or replace procedure Autonomous 
(v_sal e.sal%type,v_empno e.empno%type)
is
PRAGMA Autonomous_transaction; --声明为自治事物
begin 
    update e set sal=v_sal where empno=v_empno;
    commit;
end Autonomous;
/


--如果emp表的sal被更新那么自动更新e表的sal
create or replace trigger cascade_update_e
before update of sal on emp for each row
declare
  v_sal e.sal%type;
  v_empno e.empno%type;
begin
  v_sal := :new.sal;
  v_empno := :new.empno;
  Autonomous(v_sal,v_empno);
end;
/


练习 9: 系统触发器
---------------------------------------------------------------------------------
conn scott/tiger
drop table log_table;
create table log_table(sid number,
                       serial# number,
                       username varchar2(30),
                       action varchar2(8),
                       log_time varchar2(19));


grant select on scott.log_table to public;
grant insert on scott.log_table to public;


--用户级别：
create or replace trigger scott_logon
  after logon on schema
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end scott_logon;
/


create or replace trigger scott_logof
  before logoff on schema
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
  v_status v$session.status%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'logoff',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end scott_logof;
/


--通过用户级别触发器修改排序方法:（也可以修改语言选项或日期格式）
CONN SCOTT/TIGER
CREATE OR REPLACE TRIGGER test_tri
AFTER LOGON ON schema
DECLARE
 sqlstr VARCHAR2(200) := 'alter session set nls_sort = SCHINESE_STROKE_M';
BEGIN
    execute immediate sqlstr;
END test_tri;
/


--通过数据库级别触发器修改排序方法:
CONN / AS SYSDBA
CREATE OR REPLACE TRIGGER test_tri
AFTER LOGON ON DATABASE
DECLARE
 sqlstr VARCHAR2(200) := 'alter session set nls_sort = SCHINESE_STROKE_M';
BEGIN
  IF (USER = 'SCOTT') THEN
    execute immediate sqlstr;
  END IF;
END test_tri;
/


--数据库级别：得具有访问v$的权限! （ 这个好像只能建在SYS用户下）
conn sys/oracle as sysdba


create or replace trigger LogSchemaConnects
  after logon on database
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end LogSchemaConnects;
/


create or replace trigger dbshutdown
  before shutdown on database
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'ShutDown',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end dbshutdown;
/


--限制指定用户从指定IP登录:
CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
 RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
 ALLOWED_IP      VARCHAR2(16) := '10.1.1.157';
 LOGON_USER      VARCHAR2(32);
 CLIENT_IP       VARCHAR2(16);
BEGIN
 LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');
 CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
  IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
   RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
 END IF;

END;

/

练习 10:替代触发器(由视图的dml操作所触发)
-------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW VVV AS 
  SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC
  FROM EMP E,DEPT D
  WHERE E.DEPTNO=D.DEPTNO;


INSERT INTO VVV VALUES (7777,'zjz',50,'SALES','Beijing');


CREATE OR REPLACE TRIGGER insert_EMP_AND_DEPT
   INSTEAD OF INSERT ON VVV
 DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
 BEGIN
IF INSERTING THEN
 INSERT INTO DEPT (DEPTNO, DNAME, LOC)
 VALUES (
   :new.DEPTNO,
   :new.DNAME,
   :new.LOC);
   INSERT INTO EMP
     (EMPNO, ENAME, DEPTNO) 
   VALUES (
   :new.EMPNO, 
   :new.ENAME,
   :new.DEPTNO);
ELSIF DELETING THEN
NULL;
ELSE
NULL;
END IF;
 EXCEPTION
   WHEN duplicate_info THEN
     RAISE_APPLICATION_ERROR (
       num=> -20107,
       msg=> 'Duplicate EMP or DEPT ID');
 END insert_EMP_AND_DEPT;
/