独角兽企业重金招聘Python工程师标准>>>

简介

触发器是当特定事件出现时自动执行的存储过程，特定事件可以是执行更新的DML语句和DDL语句，触发器不能被显式调用。

触发器的功能：

1.自动生成数据

2.自定义复杂的安全权限

3.提供审计和日志记录

4.启用复杂的业务逻辑

创建触发器的语法

CREATE [OR REPLACE] TRIGGER trigger_nameAFTER | 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;

:new --为一个引用最新的列值;

:old --为一个引用以前的列值; 这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在.

且update语句两个都有,而insert只有:new ,delect 只有:old;

触发器由三部分组成：

触发器语句（事件）

定义激活触发器的 DML 事件和 DDL 事件

触发器限制

执行触发器的条件，该条件必须为真才能激活触发器

触发器操作（主体）

包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

触发器语句

CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF empsal ON salary_records --在更新 emp_sal 列之后激活触发器…

触发器限制

…FOR EACH ROWWHEN (NEW.empsal>OLD.empsal) --只有在WHEN子句中的条件得到满足时，才激活trig_sal 触发器DECLARESal_diff NUMBER;…

触发器操作

…BEGINsal_diff:=:NEW.empsal-:OLD.empsal; --如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff);END;

BEFORE 触发器的工作原理

创建触发器

CREATE OR REPLACE TRIGGER aiu_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGINIF (:NEW.qty_hand =0) THENDBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入记录');END IF;END;

查看表的触发器

select * from all_triggers where table_name =upper('tbname')

CREATE OR REPLACE TRIGGER TR_SEC_EMPBEFOR INSERT OR UPDATE OR DELETE ON EMP2BEGIN IF TO_CHAR(SYSDATE,'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR(-20002,’禁止修改数据！’);END IF;END;

工资一般来说都是往上调整，写个触发器禁止降低工资？

create or replace trigger guo_trigger----创建触发器 before update on emp2 ----指明触发器时机 for each row ----行触发器标识 when (new.sal<old.sal ) ----触发条件 begin raise_application_error(-20500,'不能给员工减少工资'); end;

触发器类型

触发器的类型有：

DDL 触发器

在模式中执行 DDL 语句时执行

数据库级触发器

在发生打开、关闭、登录和退出数据库等系统事件时执行

DML 触发器

在对表或视图执行DML语句时执行

语句级触发器

无论受影响的行数是多少，都只执行一次

行级触发器

对DML语句修改的每个行执行一次

INSTEAD OF 触发器

用于用户不能直接使用 DML 语句修改的视图

综述：

触发器组成:

l 触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

l 触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

l 触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

l 触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

l 触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

l 触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

触发器不接受参数。

l 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

行级触发器

CREATE TABLE TEST_TRG(ID NUMBER, NAME VARCHAR2(20));CREATE SEQUENCE SEQ_TEST;CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGIN--函数UPDATING, DELETING判断触发器是由哪个操作触发的 IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;ELSERAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');END IF;END;

修改数据前备份员工工资

CREATE OR REPLACE TIGGER TR_SAL_BACK AFFTER UPDATE OF SAL ON EMP2 FOR EACH ROW DECLARE V_TEM INT ;BEGIN SELECT COUNT(*) INTO V_TEMP FROM NEW_BACK WHERE ENAME =:OLD.ENAME;IF V_TEMP =0THEN INSERT INTO NEW_BACK VALUES (:OLD.ENAME,:OLD.SAL, :NEW.SAL,SYSDATE);ELSE UPDATE NEW_BACK VALUES SET OLDSAL=:OLDSAL, NEWSAL=:NEW.SAL,TIME=SYSDATE WHERE NAME=:OLD.ENAME;END IF; END;

建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE1=2; CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定触发时机为删除操作前触发ON scott.emp FOR EACH ROW --说明创建的是行级触发器 BEGIN--将修改前数据插入到日志记录表 del_emp ,以供监督使用。INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :m, :old.hiredate );END;DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp;

语句级触发器

CREATE OR REPLACE TRIGGER trgdemoAFTER INSERT OR UPDATE OR DELETEON order_masterBEGINIF UPDATING THENDBMS_OUTPUT.PUT_LINE('已更新 ORDER_MASTER 中的数据');ELSIF DELETING THENDBMS_OUTPUT.PUT_LINE('已删除 ORDER_MASTER 中的数据');ELSIF INSERTING THENDBMS_OUTPUT.PUT_LINE('已在 ORDER_MASTER 中插入数据');END IF;END;

行级与语句区别

行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。

INSTEAD OF 触发器

CREATE OR REPLACE TRIGGER upd_ord_viewINSTEAD OF UPDATE ON ord_viewFOR EACH ROWBEGINUPDATE order_masterSET vencode=:NEW.vencode WHERE orderno = :NEW.orderno;DBMS_OUTPUT.PUT_LINE('已激活触发器');END;

模式触发器

CREATE TABLE dropped_obj (obj_name VARCHAR2(30),obj_type VARCHAR2(20),drop_date DATE);CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGININSERT INTO dropped_objVALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;

启用、禁用和删除触发器

启用和禁用触发器

ALTER TRIGGER aiu_itemfile DISABLE;

ALTER TRIGGER aiu_itemfile ENABLE;

删除触发器

DROP TRIGGER aiu_itemfile;

查看有关触发器的信息

USER_TRIGGERS 数据字典视图包含有关触发器的信息

SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME='EMP';SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';