--自治触发器日志插入语句

DROP TABLE emp;

CREATE TABLE emp AS SELECT * FROM employees;

DROP TABLE log;

CREATE TABLE log (

    log_id   NUMBER(6),

    up_date  DATE,

    new_sal  NUMBER(8,2),

    old_sal  NUMBER(8,2)

);

-- 表上的自治触发器

CREATE OR REPLACE TRIGGER log_sal

    BEFORE UPDATE OF salary ON emp FOR EACH ROW

DECLARE

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    INSERT INTO log (log_id, up_date, new_sal, old_sal)

    VALUES (:old.employee_id, SYSDATE, :new.salary,:old.salary);

    COMMIT;

END;

/

UPDATE emp

SET salary = salary * 1.05

WHERE employee_id = 115;

COMMIT;

UPDATE emp

SET salary = salary * 1.05

WHERE employee_id = 116;

ROLLBACK;

--查看提交和回滚的更新

SELECT * FROM log

WHERE log_id = 115 OR log_id = 116;

--自治触发器为了执行DDL而使用本地动态SQL

DROP TABLE temp;

CREATE TABLE temp

(

    temp_id NUMBER(6),

    up_date DATE

);

CREATE OR REPLACE TRIGGER drop_temp_table

    AFTER INSERT ON log

DECLARE

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE temp';

    COMMIT;

END;

/

--测试

SELECT * FROM temp;

INSERT INTO log VALUES(999, SYSDATE, 5000, 4500);

SELECT * FROM temp;