CREATE SEQUENCE update_sequence 

    INCREMENT BY 1

    START WITH 1000

    MAXVALUE 5000 CYCLE;



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;



 



l         幫助實(shí)現(xiàn)安全控制；



例：保證對(duì)EMP表的修改僅在工作日的工作時(shí)間；



 



CREATE TABLE company_holidays(day DATE);



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;



 


    

       



CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name 

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;


    

      BEFORE INSERT



BEFORE INSERT FOR EACH ROW



AFTER INSERT



AFTER INSERT FOR EACH ROW



 



BEFORE UPDATE



BEFORE UPDATE FOR EACH ROW



AFTER UPDATE



AFTER UPDATE FOR EACH ROW



 



BEFORE DELETE



BEFORE DELETE FOR EACH ROW



AFTER DELETE



AFTER DELETE FOR EACH ROW


    

        INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

DELETE emp WHERE empno=7788;

DROP TABLE emp_his;

DROP TRIGGER del_emp;


    

      CREATE OR REPLACE TRIGGER tr_dept_time

BEFORE INSERT OR DELETE OR UPDATE 

ON departments

BEGIN

 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN

     RAISE_APPLICATION_ERROR(-20001, '不是上班時(shí)間，不能修改departments表');

 END IF;

END;


    

      CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

       OR DELETE

ON HR.employees

FOR EACH ROW

WHEN (old.department_id = 80)

BEGIN

 CASE

     WHEN UPDATING ('salary') THEN

        IF :NEW.salary < :old.salary THEN



           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的人員的獎(jiǎng)金不能降');

        END IF;

     WHEN DELETING THEN

          RAISE_APPLICATION_ERROR(-20003, '不能刪除部門80的人員記錄');

     END CASE;

END; 



/*

實(shí)例：

UPDATE employees SET salary = 8000 WHERE employee_id = 177;

DELETE FROM employees WHERE employee_id in (177,170);

*/


    

      CREATE OR REPLACE TRIGGER tr_reg_cou

AFTER update OF region_id

ON regions

FOR EACH ROW

BEGIN

 DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id

                  ||'、新的region_id值是'||:new.region_id);

 UPDATE countries SET region_id = :new.region_id

 WHERE region_id = :old.region_id;

END;


    

      CREATE OR REPLACE PROCEDURE add_job_history

 ( p_emp_id          job_history.employee_id%type

   , p_start_date      job_history.start_date%type

  , p_end_date        job_history.end_date%type

   , p_job_id          job_history.job_id%type

   , p_department_id   job_history.department_id%type

   )

IS

BEGIN

 INSERT INTO job_history (employee_id, start_date, end_date,

                           job_id, department_id)

  VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);

END add_job_history;



--創(chuàng)建觸發(fā)器調(diào)用存儲(chǔ)過程...

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;


    

      CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.] view_name --只能定義在視圖上

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ] --因?yàn)镮NSTEAD OF觸發(fā)器只能在行級(jí)上觸發(fā),所以沒有必要指定

[WHEN condition]

PL/SQL_block | CALL procedure_name;


    

      CREATE OR REPLACE VIEW emp_view AS 

SELECT deptno, count(*) total_employeer, sum(sal) total_salary 

FROM emp GROUP BY deptno;


    

      CREATE OR REPLACE TRIGGER emp_view_delete

   INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

   DELETE FROM emp WHERE deptno= :old.deptno;

END emp_view_delete; 



DELETE FROM emp_view WHERE deptno=10; 



DROP TRIGGER emp_view_delete;



DROP VIEW emp_view; 


    

      CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME")

AS

 SELECT r.region_id,

    r.region_name,

    c.country_id,

    c.country_name

 FROM regions r,

    countries c

 WHERE r.region_id = c.region_id;


    

      CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF

 INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER;

BEGIN

 SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;

 IF v_count = 0 THEN

    INSERT INTO regions

      (region_id, region_name

      ) VALUES

      (:new.r_id, :new.r_name

      );

 END IF;



 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 OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER} 

{ddl_event_list | database_event_list}

ON { DATABASE | [schema.]SCHEMA }

[WHEN condition]

PL/SQL_block | CALL procedure_name;


    

      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 TABLE log_event

(user_name VARCHAR2(10),

 address VARCHAR2(20), 

 logon_date timestamp,

 logoff_date timestamp); 


    

      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;

--創(chuàng)建退出觸發(fā)器

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;



 


    

      SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,

 TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,

 STATUS, ACTION_TYPE

 FROM user_triggers;


    

      CREATE TABLE dept_summary(

 Deptno NUMBER(2),

 Sal_sum NUMBER(9, 2),

 Emp_count NUMBER); 



INSERT INTO dept_summary(deptno, sal_sum, emp_count)

 SELECT deptno, SUM(sal), COUNT(*) 

FROM emp 

GROUP BY deptno;



--創(chuàng)建一個(gè)PL/SQL過程disp_dept_summary

--在觸發(fā)器中調(diào)用該過程顯示dept_summary標(biāo)中的數(shù)據(jù)。

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(''正在執(zhí)行trig1 觸發(fā)器…'');

      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;


    

      BEGIN

  DBMS_OUTPUT.PUT_LINE('插入前');

  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT(

    'CREATE OR REPLACE TRIGGER trig2_update

      AFTER UPDATE OF sal ON emp

      REFERENCING OLD AS old_emp NEW AS new_emp

      FOR EACH ROW

      WHEN (old_emp.sal != new_emp.sal)

    BEGIN

      DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_update 觸發(fā)器…'');

      DBMS_OUTPUT.PUT_LINE(''sal 舊值：''|| :old_emp.sal);

      DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);

      UPDATE dept_summary

        SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal

        WHERE deptno = :new_emp.deptno;

    END;'

  );

  

  DBMS_UTILITY.EXEC_DDL_STATEMENT(

    'CREATE OR REPLACE TRIGGER trig2_insert

      AFTER INSERT ON emp

      REFERENCING NEW AS new_emp

      FOR EACH ROW

    DECLARE

      I NUMBER;

    BEGIN

      DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2_insert 觸發(fā)器…'');

      SELECT COUNT(*) INTO I 

      FROM dept_summary WHERE deptno = :new_emp.deptno;

      IF I > 0 THEN

        UPDATE dept_summary 

        SET sal_sum=sal_sum+:new_emp.sal,

        Emp_count=emp_count+1

        WHERE deptno = :new_emp.deptno;

      ELSE

        INSERT INTO dept_summary

        VALUES (:new_emp.deptno, :new_emp.sal, 1);

      END IF;

    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(''正在執(zhí)行trig2_delete 觸發(fā)器…'');

      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;


    

      BEGIN

    DBMS_OUTPUT.PUT_LINE('插入前');

    Disp_dept_summary();

    DBMS_UTILITY.EXEC_DDL_STATEMENT(

        'CREATE OR REPLACE TRIGGER trig2

            AFTER INSERT OR DELETE OR UPDATE OF sal

ON emp

            REFERENCING OLD AS old_emp NEW AS new_emp

            FOR EACH ROW

        DECLARE

            I NUMBER;

        BEGIN

            IF UPDATING AND :old_emp.sal != :new_emp.sal THEN

            DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2 觸發(fā)器…'');

                DBMS_OUTPUT.PUT_LINE(''sal 舊值：''|| :old_emp.sal);

                DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);

                UPDATE dept_summary

                    SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal

                WHERE deptno = :new_emp.deptno;

            ELSIF INSERTING THEN

                DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');

                SELECT COUNT(*) INTO I 

        FROM dept_summary 

        WHERE deptno = :new_emp.deptno;

                IF I > 0 THEN

                    UPDATE dept_summary 

          SET sal_sum=sal_sum+:new_emp.sal,

              Emp_count=emp_count+1

          WHERE deptno = :new_emp.deptno;

            ELSE

          INSERT INTO dept_summary

            VALUES (:new_emp.deptno, :new_emp.sal, 1);

        END IF;

      ELSE

        DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig2觸發(fā)器…'');

        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 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');

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END;


    

      DECLARE

    No NUMBER;

    Name VARCHAR2(20);

BEGIN

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE OR REPLACE VIEW myview AS

            SELECT empno, ename, ''E'' type FROM emp

            UNION

            SELECT dept.deptno, dname, ''D'' FROM dept

    ');

    -- 創(chuàng)建INSTEAD OF 觸發(fā)器trigger3;

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE OR REPLACE TRIGGER trig3

            INSTEAD OF INSERT ON myview

            REFERENCING NEW n

            FOR EACH ROW

        DECLARE

            Rows INTEGER;

        BEGIN

            DBMS_OUTPUT.PUT_LINE(''正在執(zhí)行trig3觸發(fā)器…'');

            IF :n.type = ''D'' THEN

                SELECT COUNT(*) INTO rows

                    FROM dept WHERE deptno = :n.empno;

                IF rows = 0 THEN

                    DBMS_OUTPUT.PUT_LINE(''向dept表中插入數(shù)據(jù)…'');

                    INSERT INTO dept(deptno, dname, loc)

                        VALUES (:n.empno, :n.ename, ''none’’);

                ELSE

                    DBMS_OUTPUT.PUT_LINE(''編號(hào)為''|| :n.empno||

                     ''的部門已存在，插入操作失敗！'');

                 END IF;

            ELSE

                SELECT COUNT(*) INTO rows

                    FROM emp WHERE empno = :n.empno;

                IF rows = 0 THEN

                    DBMS_OUTPUT.PUT_LINE('’向emp表中插入數(shù)據(jù)…’’);

                    INSERT INTO emp(empno, ename)

                        VALUES(:n.empno, :n.ename);

                ELSE

                    DBMS_OUTPUT.PUT_LINE(''編號(hào)為''|| :n.empno||

                      ''的人員已存在，插入操作失敗!'');

                END IF;

            END IF;

        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('員工編號(hào)：'||TO_CHAR(no)||'姓名：'||name);

    SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;

    DBMS_OUTPUT.PUT_LINE('部門編號(hào)：'||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;


    

      BEGIN

    -- 創(chuàng)建用于記錄事件日志的數(shù)據(jù)表

    DBMS_UTILITY.EXEC_DDL_STATEMENT('

        CREATE TABLE eventlog(

            Eventname VARCHAR2(20) NOT NULL,

            Eventdate date default sysdate,

            Inst_num NUMBER NULL,

            Db_name VARCHAR2(50) NULL,

            Srv_error NUMBER NULL,

            Username VARCHAR2(30) NULL,

            Obj_type VARCHAR2(20) NULL,

            Obj_name VARCHAR2(30) NULL,

            Obj_owner VARCHAR2(30) NULL

        )

    ');



    -- 創(chuàng)建DDL觸發(fā)器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;

    ');



    -- 創(chuàng)建LOGON、STARTUP和SERVERERROR 事件觸發(fā)器

    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;

  ');



  -- 創(chuàng)建LOGOFF和SHUTDOWN 事件觸發(fā)器

  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(

    Audit_id     NUMBER,

    User_name VARCHAR2(20),

    Now_time DATE,

    Terminal_name VARCHAR2(10),

    Table_name VARCHAR2(10),

    Action_name VARCHAR2(10),

    Emp_id NUMBER(4));



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;