触发器:有点类似于存储过程,是每次数据库做相关操作时,触发某个事件。
CREATE [OR REPLACE] TRIGGER 触发器名称
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]表名称 | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
例子:
CREATE OR REPLACE TRIGGER TRIGGER_UDSP_ZFMX_INSERT AFTER INSERT ON UDSP_ZFMX FOR EACH ROW BEGIN IF :NEW.IS_VALID='0' THEN DELETE FROM UDSP_ZFMX_RESULT where PAY_ID=:NEW.PAY_ID; ELSE DELETE FROM UDSP_ZFMX_RESULT where PAY_ID=:NEW.PAY_ID; END IF; END;
表级触发器
表级触发器指的是对全表数据的检查,每次更新数据表时,只会在更新之前或之后出发一次,表级触发器不需要配置for each row 选项。