oracle教程36 触发器（INSTEAD OF 触发器和系统触发器）

柔情只为你懂 2022-05-27 07:14 214阅读 0赞

# **INSTEAD OF 触发器** #

![70][]

![70 1][]

举例：

--创建视图
    CREATE OR REPLACE VIEW emp_dept
    AS
    SELECT d.deptno,d.dname,e.empno,e.ename
    FROM dept d, emp e
    WHERE d.deptno = e.deptno;

--创建替代触发器
    CREATE OR REPLACE TRIGGER instead_of_trigger
    INSTEAD OF
    INSERT
    ON emp_dept
    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 emp_dept VALUES(50,'DEVELOPMENT',2222,'ALICE');
    
    SELECT * FROM EMP_DEPT

# **系统触发器（需要由系统用户才能创建）** #

![70 2][]

![70 3][]

举例：

--连接sys用户
    
    
    --创建事件表
    CREATE   TABLE   event_table(
    	event varchar2(50), 
    	event_time date
    );

--再创建一个系统触发器
    create or replace trigger startup_trigger
    after startup on database
    begin
      insert into event_table values(ora_sysevent,SYSDATE);
    end;

--在SQLPLUS窗口执行下列命令
    SHUTDOWN
    STARTUP
    SELECT    *   FROM event_table;

[70]: /images/20220527/d2316f444aa440b78d37984315ce4051.png
[70 1]: /images/20220527/811020c98e224041b694d5d3872b8f0d.png
[70 2]: /images/20220527/d12e3683bae94996afad44e51f5734b4.png
[70 3]: /images/20220527/f730de2d2536403d9627d6695d2b2bcb.png