oracle触发器

oracle触发器

SET SERVEROUTPUT ON;

SQL> CREATE TABLE TEST_TRG
        (ID NUMBER, NAME VARCHAR2(20));
SQL> CREATE SEQUENCE SEQ_TEST;
SQL> CREATE OR REPLACE TRIGGER BI_TEST_TRG
     BEFORE INSERT OR UPDATE OF ID
     ON TEST_TRG
     FOR EACH ROW
     BEGIN
      IF INSERTING THEN
       SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
      ELSE
       RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');
      END IF;
     END;
/
--//insert 改写成序列的值,行级别
INSERT INTO test_trg VALUES(0,'AAA');
INSERT INTO test_trg VALUES(0,'AAA');
INSERT INTO test_trg VALUES(0,'AAA');
COMMIT;

SQL> SELECT * FROM test_trg;

        ID NAME
---------- --------------------
         1 AAA
         2 AAA
         3 AAA
--//update 阻止更新主键 ID
SQL> UPDATE test_trg SET id = 4 WHERE id = 1;
UPDATE test_trg SET id = 4 WHERE id = 1
       *
ERROR 位于第 1 行:
ORA-20020: 不允许更新ID值！
ORA-06512: 在"SCOTT.BI_TEST_TRG", line 5
ORA-04088: 触发器 'SCOTT.BI_TEST_TRG' 执行过程中出错

--//AFTER trigger
--//语句级触发器OLD NEW 不可用
CREATE OR REPLACE TRIGGER triceTest_trg
   AFTER INSERT OR UPDATE OR DELETE
   ON test_trg
BEGIN
   IF UPDATING THEN
     DBMS_OUTPUT.PUT_LINE('已更新 ORDER_MASTER 中的数据');
   ELSIF DELETING THEN
     DBMS_OUTPUT.PUT_LINE('已删除 ORDER_MASTER 中的数据');
   ELSIF INSERTING THEN
     DBMS_OUTPUT.PUT_LINE('已在 ORDER_MASTER 中插入数据');
   END IF;
END;
/

--//
DELETE FROM test_trg WHERE id = 3;
--//语句级别还是行级别 将会打印多少行 ： '已在 ORDER_MASTER 中删除数据'
DELETE FROM test_trg;

--//模式触发器
--//审计表
CREATE TABLE dropped_obj (
          obj_name VARCHAR2(30),
          obj_type VARCHAR2(20),
          ddl_date DATE);

--//审计触(DDL)发器  ORA_DICT_OBJ_NAME 记录 DDl操作 一般在触发器中使用
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP OR CREATE ON SCHEMA
BEGIN
  INSERT INTO dropped_obj
  VALUES( ORA_DICT_OBJ_NAME,
          ORA_DICT_OBJ_TYPE, SYSDATE);
END;

SELECT * FROM dropped_obj;
--//测试审计触发器
SQL> CREATE TABLE tempTab (a CHAR);

表已创建。

SQL> DROP TABLE tempTab;

表已丢弃。

SELECT * FROM dropped_obj;

--//触发器管理
--//禁止
SQL> ALTER TRIGGER  log_drop_obj DISABLE;

触发器已更改
--//查询
SQL> DESC user_triggers;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TRIGGER_NAME                                       VARCHAR2(30)
 TRIGGER_TYPE                                       VARCHAR2(16)
 TRIGGERING_EVENT                                   VARCHAR2(227)
 TABLE_OWNER                                        VARCHAR2(30)
 BASE_OBJECT_TYPE                                   VARCHAR2(16)
 TABLE_NAME                                         VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 REFERENCING_NAMES                                  VARCHAR2(128)
 WHEN_CLAUSE                                        VARCHAR2(4000)
 STATUS                                             VARCHAR2(8)
 DESCRIPTION                                        VARCHAR2(4000)
 ACTION_TYPE                                        VARCHAR2(11)
 TRIGGER_BODY                                       LONG

SQL> SELECT trigger_name,trigger_type,status FROM user_triggers;

TRIGGER_NAME                   TRIGGER_TYPE     STATUS
------------------------------ ---------------- --------
BI_TEST_TRG                    BEFORE EACH ROW  ENABLED
TRICETEST_TRG                  AFTER EACH ROW   ENABLED
LOG_DROP_OBJ                   BEFORE EVENT     DISABLED
--//删除
SQL> DROP TRIGGER  log_drop_obj;

触发器已丢弃