ORACLE 触发器 基础

            CREATE
            OR
            REPLACE
            TRIGGER
             TRIGGER_NAME
AFTER
            |BEFORE|INSTEAD OF[INSERT][OR UPDATE [OF COLUMN_LIST]] [OR [DELETE]]
ON TABLE_OR_VIEW_NAME
[REFERENCING{OLD[AS]OLD/NEW[AS]NEW} ]
[FOR EACH ROW][WHEN(condition)]
pl/sql_block;

--sqlserver 　　　　　　 oraceldeleted 　　　　　　　　 :old
inserted　　　　　　　　:new

            --
            创建实验环境
            CREATE
            TABLE SC(CODE NUMBER ,SNAME VARCHAR2(10),SCORE NUMBER);
INSERTINTO SC VALUES (1,‘AA‘,87);
INSERTINTO SC VALUES (2,‘BB‘,67);
INSERTINTO SC VALUES (3,‘CC‘,57);
INSERTINTO SC VALUES (4,‘DD‘,88);
COMMIT;
SELECT*FROM SC;

SET SERVEROUT ONCREATEORREPLACETRIGGER TRIG1
BEFORE INSERTORUPDATEON SC
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘TRIG1 IS VALID‘);
END;
/INSERTINTO SC VALUES (5,‘EE‘,90);
UPDATE SC SET SCORE=SCORE+1;
ROLLBACK;

--SCORE 必须在0-100中CREATEORREPLACETRIGGER TRIG2
BEFORE INSERTON SC 
FOR EACH ROW --使用了:NEW,:OLD时必须要有这句话BEGINIF :NEW.SCORE<0OR :NEW.SCORE>100THEN RAISE_APPLICATION_ERROR(-20001,‘ERROR‘);
ENDIF; 
END;
/INSERTINTO SC VALUES (6,‘EE‘,-1);
INSERTINTO SC VALUES (6,‘EE‘,101);
INSERTINTO SC VALUES (6,‘EE‘,10);
ROLLBACK;

 

            --
            当插入的CODE是负数时，将其转为正数再插入 必须在0-100中
            CREATE
            OR
            REPLACE
            TRIGGER
             TRIG3
BEFORE 
            INSERT
            ON
             SC 

            FOR
             EACH ROW

            BEGIN
            IF :NEW.CODE <0THEN :NEW.CODE :=-:NEW.CODE;
ENDIF;
END;
/INSERTINTO SC VALUES (-7,‘EE‘,90);

            --
            当用户对表进行操作时记录操作
            CREATE
            OR
            REPLACE
            TRIGGER
             TRIG5 
BEFORE 
            INSERT
            OR
            UPDATE
            OR
            DELETE
            ON
             SC

            FOR
             EACH ROW

            BEGIN
            IF INSERTING --关键词THEN DBMS_OUTPUT.PUT_LINE(‘INSERT:‘||:NEW.CODE||‘,‘||:NEW.NAME||‘,‘||:NEW.SCORE);
ENDIF;
IF DELETING --关键词THEN DBMS_OUTPUT.PUT_LINE(‘DELETE:‘||:OLD.CODE||‘,‘||:OLD.NAME||‘,‘||:OLD.SCORE);
ENDIF;
IF UPDATING --关键词THEN DBMS_OUTPUT.PUT_LINE(‘UPDATE_B:‘||:OLD.CODE||‘,‘||:OLD.NAME||‘,‘||:OLD.SCORE);
DBMS_OUTPUT.PUT_LINE(‘UPDATE_A:‘||:NEW.CODE||‘,‘||:NEW.NAME||‘,‘||:NEW.SCORE);
ENDIF;
END;
/INSERTINTO SC VALUES (10,‘EE‘,90);
UPDATE SC SET SCORE=100WHERE CODE=10;
DELETE SC WHERE CODE=10;

            --
            用触发器修改视图（非键保留表）
            
--
            创建实验环境
            CREATE
            TABLE STUDENT (SNO INT ,SNAME VARCHAR2(10),SAGE INT);
INSERTINTO STUDENT VALUES (1,‘AA‘,22);
INSERTINTO STUDENT VALUES (2,‘BB‘,23);
INSERTINTO STUDENT VALUES (3,‘CC‘,24);
INSERTINTO STUDENT VALUES (4,‘DD‘,25);
INSERTINTO STUDENT VALUES (5,‘EE‘,26);

CREATETABLE ADDRESS (SNO INT ,ZZ VARCHAR2(10));
INSERTINTO ADDRESS VALUES (1,‘ZZ‘);
INSERTINTO ADDRESS VALUES (2,‘LY‘);
INSERTINTO ADDRESS VALUES (3,‘KF‘);
INSERTINTO ADDRESS VALUES (4,‘XX‘);
INSERTINTO ADDRESS VALUES (5,‘XC‘);
COMMIT;

GRANTCREATEVIEWTO SCOTT ; --DBACREATEORREPLACEVIEW V1
ASSELECT A.SNO,A.SNAME ,A.SAGE,B.ZZ
FROM STUDENT A LEFTJOIN ADDRESS B 
ON A.SNO=B.SNO;
--实现更新操作UPDATE V1 SET ZZ=‘AY‘WHERE SNAME =‘BB‘;
-- ORA-01779: cannot modify a column which maps to a non key-preserved tableCREATEORREPLACETRIGGER TRIG4 
INSTEAD OFUPDATEON V1
FOR EACH ROW
DECLARE 
A NUMBER :=0; --用在存储SNOBEGINSELECT SNO INTO A FROM STUDENT WHERE SNAME = :OLD.SNAME; --赋值DELETE ADDRESS WHERE SNO=A; --删除原数据INSERTINTO ADDRESS VALUES (A,:NEW.ZZ); --插入新数据END;
/UPDATE V1 SET ZZ=‘AY‘WHERE SNAME =‘BB‘;

 

            --
            模式触发器
            
--
            例
            CREATE
            TABLE DROPPDE_OBJ(OBJ_NAME VARCHAR2(30),OBJ_TYPE VARCHAR2(20),DROP_DATE DATE);
CREATEORREPLACETRIGGER LOG_DROP_OBJ
AFTER DROP--类型ONSCHEMA--模式BEGININSERTINTO DROPPED_OBJ
VALUES (ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
END;
/CREATETABLE TT (ID INT);
DROPTABLE TT ;

ora_client_ip_address --返回客户端的IP
ora_database_name --返回当前数据库名
ora_login_user --返回登录用户名
ora_dict_obj_name --返回DDL操作对应的数据库对象名
ora_dict_obj_type --返回DDL操作对应的数据库对象类型

            --
            数据库启动、关闭 触发器 --SYS使用
            --
            数据库启动触发器 
            CREATE
            TABLE EVENT_TABLE (EVENT VARCHAR2(30),TIME DATE);

CREATEORREPLACETRIGGER TR_STARTUP
AFTER STARTUP --AFTERONDATABASEBEGININSERTINTO EVENT_TABLE VALUES (ora_sysevent,SYSDATE);
END;
/--数据库关闭触发器CREATEORREPLACETRIGGER TR_STARTUP
BEFORE SHUTDOWN--BEFOREONDATABASEBEGININSERTINTO EVENT_TABLE VALUES (ora_sysevent,SYSDATE);
END;
/

            --
            用户登录和退出触发器
            CREATE
            TABLE LOGIN_TABLE (USERNAME VARCHAR2(10),LOGON_TIME DATE,LOGOFF_TIME DATE,ADDRESS VARCHAR2(30));

--登陆CREATEORREPLACETRIGGER TRIG_LOGON
AFTER LOGON 
ONDATABASEBEGININSERTINTO LOGIN_TABLE(USERNAME,LOGON_TIME,ADDRESS)
VALUES (ora_login_user,SYSDATE,ora_client_ip_address);
END;
/--退出CREATEORREPLACETRIGGER TRIG_LOGOFF
BEFORE LOGOFF 
ONDATABASEBEGININSERTINTO LOGIN_TABLE(USERNAME,LOGOFF_TIME,ADDRESS)
VALUES (ora_login_user,SYSDATE,ora_client_ip_address);
END;
/

            --
            启用、禁用与删除
            ALTER
            TRIGGER
             TRIGGER_NAME DISABLE;

            ALTER
            TRIGGER
             TRIGGER_NAME ENABLE;

            DROP
            TRIGGER
             TRIGGER_NAME ;



            --
            查看触发器相关信息
            SELECT
            *
            FROM USER_TRIGGERS;