Oracle PL/SQL编程之八： 把触发器说透

           RAISE_APPLICATION_ERROR(
–
20001
, 
‘
部门80的人员的工资不能降
‘
);
        
END
 
IF
;
     
WHEN
 UPDATING (
‘
commission_pct
‘
) 
THEN

        
IF
 :NEW.commission_pct 
<
 :old.commission_pct 
THEN

           RAISE_APPLICATION_ERROR(
–
20002
, 
‘
部门80的人员的奖金不能降
‘
);
        
END
 
IF
;
     
WHEN
 DELETING 
THEN

          RAISE_APPLICATION_ERROR(
–
20003
, 
‘
不能删除部门80的人员记录
‘
);
     
END
 
CASE
;

END
; 

/*

实例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);

*/

—
创建触发器调用存储过程…


CREATE
 
OR
 
REPLACE
 
TRIGGER
 update_job_history
 AFTER 
UPDATE
 
OF
 job_id, department_id 
ON
 employees
 
FOR
 EACH ROW

BEGIN

 add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);

END
;

DELETE
 
FROM
 emp_view 
WHERE
 deptno
=
10
; 

DROP
 
TRIGGER
 emp_view_delete;

DROP
 
VIEW
 emp_view; 

 
SELECT
 
COUNT
(
*
) 
INTO
 v_count 
FROM
 countries 
WHERE
 country_id 
=
 :new.c_id;
 
IF
 v_count 
=
 
0
 
THEN

    
INSERT

    
INTO
 countries
      (
        country_id,
        country_name,
        region_id
      )
      
VALUES

      (
        :new.c_id,
        :new.c_name,
        :new.r_id
      );
 
END
 
IF
;

END
;

—
创建用于记录事件用的表




CREATE
 
TABLE
 ddl_event
(crt_date 
timestamp
 
PRIMARY
 
KEY
,
 event_name 
VARCHAR2
(
20
), 
 
user_name
 
VARCHAR2
(
10
),
 obj_type 
VARCHAR2
(
20
),
 obj_name 
VARCHAR2
(
20
));

—
创建触犯发器


CREATE
 
OR
 
REPLACE
 
TRIGGER
 tr_ddl
AFTER DDL 
ON
 
SCHEMA


BEGIN

   
INSERT
 
INTO
 ddl_event 
VALUES

   (systimestamp,ora_sysevent, ora_login_user, 
    ora_dict_obj_type, ora_dict_obj_name);

END
 tr_ddl;

—
创建登录触发器


CREATE
 
OR
 
REPLACE
 
TRIGGER
 tr_logon
AFTER LOGON 
ON
 
DATABASE


BEGIN

   
INSERT
 
INTO
 log_event (
user_name
, address, logon_date)
   
VALUES
 (ora_login_user, ora_client_ip_address, systimestamp);

END
 tr_logon;

—
创建退出触发器


CREATE
 
OR
 
REPLACE
 
TRIGGER
 tr_logoff
BEFORE LOGOFF 
ON
 
DATABASE


BEGIN

   
INSERT
 
INTO
 log_event (
user_name
, address, logoff_date)
   
VALUES
 (ora_login_user, ora_client_ip_address, systimestamp);

END
 tr_logoff;

—
例：ALTER TRIGGER emp_view_delete DISABLE;

—
例：使表EMP 上的所有TRIGGER 失效：


ALTER
 
TABLE
 emp DISABLE 
ALL
 TRIGGERS; 

INSERT
 
INTO
 dept_summary(deptno, sal_sum, emp_count)
 
SELECT
 deptno, 
SUM
(sal), 
COUNT
(
*
) 

FROM
 emp 

GROUP
 
BY
 deptno;

—
创建一个PL/SQL过程disp_dept_summary

—
在触发器中调用该过程显示dept_summary标中的数据。


CREATE
 
OR
 
REPLACE
 
PROCEDURE
 disp_dept_summary

IS

 Rec dept_summary
%
ROWTYPE;
 
CURSOR
 c1 
IS
 
SELECT
 
*
 
FROM
 dept_summary;

BEGIN

 
OPEN
 c1;
 
FETCH
 c1 
INTO
 REC;
 DBMS_OUTPUT.PUT_LINE(
‘
deptno    sal_sum    emp_count
‘
);
 DBMS_OUTPUT.PUT_LINE(
‘
————————————-
‘
);
 
WHILE
 c1
%
FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 
6
)
||

      To_char(rec.sal_sum, 
‘
$999,999.99
‘
)
||

      LPAD(rec.emp_count, 
13
));
    
FETCH
 c1 
INTO
 rec;
 
END
 LOOP;
 
CLOSE
 c1;

END
;

BEGIN

 DBMS_OUTPUT.PUT_LINE(
‘
插入前
‘
);
 Disp_dept_summary();
 DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘

    CREATE OR REPLACE TRIGGER trig1
      AFTER INSERT OR DELETE OR UPDATE OF sal ON emp
    BEGIN
      DBMS_OUTPUT.PUT_LINE(
”
正在执行trig1 触发器…
”
);
      DELETE FROM dept_summary;
      INSERT INTO dept_summary(deptno, sal_sum, emp_count)
      SELECT deptno, SUM(sal), COUNT(*) 
      FROM emp GROUP BY deptno;
    END;
 
‘
);

 
INSERT
 
INTO
 dept(deptno, dname, loc) 
 
VALUES
(
90
, ‘demo_dept’, ‘none_loc’);
 
INSERT
 
INTO
 emp(ename, deptno, empno, sal)
 
VALUES
(
USER
, 
90
, 
9999
, 
3000
);

 DBMS_OUTPUT.PUT_LINE(
‘
插入后
‘
);
 Disp_dept_summary();

 
UPDATE
 emp 
SET
 sal
=
1000
 
WHERE
 empno
=
9999
;
 DBMS_OUTPUT.PUT_LINE(
‘
修改后
‘
);
 Disp_dept_summary();

 
DELETE
 
FROM
 emp 
WHERE
 empno
=
9999
;
 
DELETE
 
FROM
 dept 
WHERE
 deptno
=
90
;

 DBMS_OUTPUT.PUT_LINE(
‘
删除后
‘
);
 Disp_dept_summary(); 
 DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
DROP
 
TRIGGER
 trig1’);
EXCEPTION
   
WHEN
 OTHERS 
THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE
||
‘
—
‘
||
SQLERRM);

END
;

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    
‘
CREATE OR REPLACE TRIGGER trig2_delete
      AFTER DELETE ON emp
      REFERENCING OLD AS old_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(
”
正在执行trig2_delete 触发器…
”
);
      SELECT emp_count INTO I 
      FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I >1 THEN
        UPDATE dept_summary 
        SET sal_sum=sal_sum – :old_emp.sal,
        Emp_count=emp_count – 1
        WHERE deptno = :old_emp.deptno;
      ELSE
        DELETE FROM dept_summary WHERE deptno = :old_emp.deptno;
      END IF;
    END;
‘

  );

  
INSERT
 
INTO
 dept(deptno, dname, loc) 
    
VALUES
(
90
, 
‘
demo_dept
‘
, 
‘
none_loc
‘
);
  
INSERT
 
INTO
 emp(ename, deptno, empno, sal)
    
VALUES
(
USER
, 
90
, 
9999
, 
3000
);
  
INSERT
 
INTO
 emp(ename, deptno, empno, sal)
    
VALUES
(
USER
, 
90
, 
9998
, 
2000
);
  DBMS_OUTPUT.PUT_LINE(
‘
插入后
‘
);
  Disp_dept_summary();

  
UPDATE
 emp 
SET
 sal 
=
 sal
*
1.1
 
WHERE
 deptno
=
90
;
  DBMS_OUTPUT.PUT_LINE(
‘
修改后
‘
);
  Disp_dept_summary();

  
DELETE
 
FROM
 emp 
WHERE
 deptno
=
90
;
  
DELETE
 
FROM
 dept 
WHERE
 deptno
=
90
;
  DBMS_OUTPUT.PUT_LINE(
‘
删除后
‘
);
  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘
DROP TRIGGER trig2_update
‘
);
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘
DROP TRIGGER trig2_insert
‘
);
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘
DROP TRIGGER trig2_delete
‘
);
EXCEPTION
   
WHEN
 OTHERS 
THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE
||
‘
—
‘
||
SQLERRM);

END
;

  
INSERT
 
INTO
 dept(deptno, dname, loc) 
    
VALUES
(
90
, 
‘
demo_dept
‘
, 
‘
none_loc
‘
);
  
INSERT
 
INTO
 emp(ename, deptno, empno, sal)
    
VALUES
(
USER
, 
90
, 
9999
, 
3000
);
  
INSERT
 
INTO
 emp(ename, deptno, empno, sal)
    
VALUES
(
USER
, 
90
, 
9998
, 
2000
);
  DBMS_OUTPUT.PUT_LINE(
‘
插入后
‘
);
  Disp_dept_summary();

  
UPDATE
 emp 
SET
 sal 
=
 sal
*
1.1
 
WHERE
 deptno
=
90
;
  DBMS_OUTPUT.PUT_LINE(
‘
修改后
‘
);
  Disp_dept_summary();

  
DELETE
 
FROM
 emp 
WHERE
 deptno
=
90
;
  
DELETE
 
FROM
 dept 
WHERE
 deptno
=
90
;
  DBMS_OUTPUT.PUT_LINE(
‘
删除后
‘
);
  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘
DROP TRIGGER trig2
‘
);
EXCEPTION
   
WHEN
 OTHERS 
THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE
||
‘
—
‘
||
SQLERRM);

END
;

    INSERT INTO myview VALUES (70, 
‘
demo
‘
, 
‘
D
‘
);
    INSERT INTO myview VALUES (9999, USER, 
‘
E
‘
);
    SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;
    DBMS_OUTPUT.PUT_LINE(
‘
员工编号：
‘
||TO_CHAR(no)||
‘
姓名：
‘
||name);
    SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;
    DBMS_OUTPUT.PUT_LINE(
‘
部门编号：
‘
||TO_CHAR(no)||
‘
姓名：
‘
||name);
  DELETE FROM emp WHERE empno=9999;
  DELETE FROM dept WHERE deptno=70;
    DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘
DROP
 
TRIGGER
 trig3
‘
);
END;

    
—
 创建DDL触发器trig4_ddl


    DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘

        CREATE OR REPLACE TRIGGER trig4_ddl
            AFTER CREATE OR ALTER OR DROP 
ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Typ VARCHAR2(20);
            Name VARCHAR2(30);
            Owner VARCHAR2(30);
        BEGIN
            — 读取DDL事件属性
            Event := SYSEVENT;
            Typ := DICTIONARY_OBJ_TYPE;
            Name := DICTIONARY_OBJ_NAME;
            Owner := DICTIONARY_OBJ_OWNER;
            –将事件属性插入到事件日志表中
            INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner)
                VALUES(event, typ, name, owner);
        END;
    
‘
);

    
—
 创建LOGON、STARTUP和SERVERERROR 事件触发器


    DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘

        CREATE OR REPLACE TRIGGER trig4_after
            AFTER LOGON OR STARTUP OR SERVERERROR 
      ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Instance NUMBER;
            Err_num NUMBER;
            Dbname VARCHAR2(50);
            User VARCHAR2(30);
        BEGIN
            Event := SYSEVENT;
            IF event = 
”
LOGON
”
 THEN
                User := LOGIN_USER;
                INSERT INTO eventlog(eventname, username)
                    VALUES(event, user);
            ELSIF event = 
”
SERVERERROR
”
 THEN
                Err_num := SERVER_ERROR(1);
                INSERT INTO eventlog(eventname, srv_error)
                    VALUES(event, err_num);
            ELSE
                Instance := INSTANCE_NUM;
                Dbname := DATABASE_NAME;
                INSERT INTO eventlog(eventname, inst_num, db_name)
                    VALUES(event, instance, dbname);
      END IF;
    END;
  
‘
);

  
—
 创建LOGOFF和SHUTDOWN 事件触发器


  DBMS_UTILITY.EXEC_DDL_STATEMENT(
‘

    CREATE OR REPLACE TRIGGER trig4_before
      BEFORE LOGOFF OR SHUTDOWN 
      ON DATABASE
    DECLARE
      Event VARCHAR2(20);
      Instance NUMBER;
      Dbname VARCHAR2(50);
      User VARCHAR2(30);
    BEGIN
      Event := SYSEVENT;
      IF event = 
”
LOGOFF
”
 THEN
        User := LOGIN_USER;
        INSERT INTO eventlog(eventname, username)
          VALUES(event, user);
      ELSE
        Instance := INSTANCE_NUM;
        Dbname := DATABASE_NAME;
        INSERT INTO eventlog(eventname, inst_num, db_name)
          VALUES(event, instance, dbname);
      END IF;
    END;
  
‘
);

END
;

CREATE
 
TABLE
 mydata(mydate 
NUMBER
);
CONNECT SCOTT
/
TIGER

COL eventname FORMAT A10
COL eventdate FORMAT A12
COL username FORMAT A10
COL obj_type FORMAT A15
COL obj_name FORMAT A15
COL obj_owner FORMAT A10

SELECT
 eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error
  
FROM
 eventlog;

DROP
 
TRIGGER
 trig4_ddl;

DROP
 
TRIGGER
 trig4_before;

DROP
 
TRIGGER
 trig4_after;

DROP
 
TABLE
 eventlog;

DROP
 
TABLE
 mydata;

CREATE
 
TABLE
 audit_table_val(
    Audit_id 
NUMBER
,
    Column_name 
VARCHAR2
(
10
),
    Old_val 
NUMBER
(
7
,
2
),
    New_val 
NUMBER
(
7
,
2
));

CREATE
 SEQUENCE audit_seq
    START 
WITH
 
1000

    INCREMENT 
BY
 
1

    NOMAXVALUE
    NOCYCLE NOCACHE;

CREATE
 
OR
 
REPLACE
 
TRIGGER
 audit_emp
    AFTER 
INSERT
 
OR
 
UPDATE
 
OR
 
DELETE
 
ON
 emp
    
FOR
 EACH ROW

DECLARE

    Time_now DATE;
    Terminal 
CHAR
(
10
);

BEGIN
 
    Time_now:
=
sysdate;
    Terminal:
=
USERENV(
‘
TERMINAL
‘
);
    
IF
 INSERTING 
THEN

        
INSERT
 
INTO
 audit_table
    
VALUES
(audit_seq.NEXTVAL, 
user
, time_now, 
           terminal, 
‘
EMP
‘
, 
‘
INSERT
‘
, :new.empno);
    ELSIF DELETING 
THEN

        
INSERT
 
INTO
 audit_table
    
VALUES
(audit_seq.NEXTVAL, 
user
, time_now, 
           terminal, 
‘
EMP
‘
, 
‘
DELETE
‘
, :old.empno);
    
ELSE

        
INSERT
 
INTO
 audit_table
    
VALUES
(audit_seq.NEXTVAL, 
user
, time_now, 
           terminal, 
‘
EMP
‘
, 
‘
UPDATE
‘
, :old.empno);
        
IF
 UPDATING(
‘
SAL
‘
) 
THEN

            
INSERT
 
INTO
 audit_table_val
                
VALUES
(audit_seq.CURRVAL, 
‘
SAL
‘
, :old.sal, :new.sal);
        
ELSE
 UPDATING(
‘
DEPTNO
‘
) 
            
INSERT
 
INTO
 audit_table_val
                
VALUES
(audit_seq.CURRVAL, 
‘
DEPTNO
‘
, :old.deptno, :new.deptno);
        
END
 
IF
;
    
END
 
IF
;

END
;

ALTER
 
TABLE
 emp
    
ADD
 update_id 
NUMBER
;

CREATE
 
OR
 
REPLACE
 PACKAGE integritypackage 
AS

    Updateseq 
NUMBER
;

END
 integritypackage;

CREATE
 
OR
 
REPLACE
 PACKAGE BODY integritypackage 
AS


END
 integritypackage;

CREATE
 
OR
 
REPLACE
 
TRIGGER
 dept_cascade1
    BEFORE 
UPDATE
 
OF
 deptno 
ON
 dept

DECLARE
 
    
Dummy
 
NUMBER
;

BEGIN
 
    
SELECT
 update_sequence.NEXTVAL 
INTO
 
dummy
 
FROM
 dual;
    Integritypackage.updateseq:
=
dummy
;

END
;

CREATE
 
OR
 
REPLACE
 
TRIGGER
 dept_cascade2
    AFTER 
DELETE
 
OR
 
UPDATE
 
OF
 deptno 
ON
 dept
    
FOR
 EACH ROW

BEGIN

    
IF
 UPDATING 
THEN

        
UPDATE
 emp 
SET
 deptno
=
:new.deptno, 
     update_id
=
integritypackage.updateseq
        
WHERE
 emp.deptno
=
:old.deptno 
AND
 update_id 
IS
 
NULL
;
    
END
 
IF
;
    
IF
 DELETING 
THEN

        
DELETE
 
FROM
 emp
            
WHERE
 emp.deptno
=
:old.deptno;
    
END
 
IF
;

END
;

CREATE
 
OR
 
REPLACE
 
TRIGGER
 dept_cascade3
    AFTER 
UPDATE
 
OF
 deptno 
ON
 dept 

BEGIN

    
UPDATE
 emp 
SET
 update_id
=
NULL

        
WHERE
 update_id
=
integritypackage.updateseq;

END
;

SELECT
 
*
 
FROM
 EMP 
ORDER
 
BY
 DEPTNO;

UPDATE
 dept 
SET
 deptno
=
25
 
WHERE
 deptno
=
20
;

INSERT
 
INTO
 company_holidays 
    
VALUES
(sysdate);

INSERT
 
INTO
 company_holidays 

VALUES
(TO_DATE(
‘
21-10月-01
‘
, 
‘
DD-MON-YY
‘
));

CREATE
 
OR
 
REPLACE
 
TRIGGER
 emp_permit_change
    BEFORE 
INSERT
 
OR
 
DELETE
 
OR
 
UPDATE
 
ON
 emp

DECLARE

    
Dummy
 
NUMBER
;
    Not_on_weekends EXCEPTION;
    Not_on_holidays EXCEPTION;
    Not_working_hours EXCEPTION;

BEGIN

    
/*
 check for weekends 
*/


IF
 TO_CHAR(SYSDATE, 
‘
DAY
‘
) 
IN
 (
‘
星期六
‘
, 
‘
星期日
‘
) 
THEN

    RAISE not_on_weekends;

END
 
IF
;
    
/*
 check for company holidays 
*/


SELECT
 
COUNT
(
*
) 
INTO
 
dummy
 
FROM
 company_holidays
    
WHERE
 TRUNC(
day
)
=
TRUNC(SYSDATE);

IF
 
dummy
 
>
0
 
THEN

    RAISE not_on_holidays;

END
 
IF
;
    
/*
 check for work hours(8:00 AM to 18:00 PM 
*/


IF
 (TO_CHAR(SYSDATE,
‘
HH24
‘
)
<
8
 
OR
 TO_CHAR(SYSDATE, 
‘
HH24
‘
)
>
18
) 
THEN

  RAISE not_working_hours;

END
 
IF
;
EXCEPTION
  
WHEN
 not_on_weekends 
THEN

    RAISE_APPLICATION_ERROR(
–
20324
, 

‘
May not change employee table during the weekends
‘
); 
  
WHEN
 not_on_holidays 
THEN

    RAISE_APPLICATION_ERROR(
–
20325
, 

‘
May not change employee table during a holiday
‘
); 
  
WHEN
 not_working_hours 
THEN

    RAISE_APPLICATION_ERROR(
–
20326
, 

‘
May not change employee table during no_working hours
‘
); 

END
;