欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

PL/SQL触发器

程序员文章站 2022-05-08 18:33:38
...
触发器
1、触发器相当于触发按钮发生的事件,它是在事件发生时隐式地运行的,并且触发器不能接收参数。
2、触发的事件可以是对数据库表的DML操作或某个视图操作,也可以是系统事件,如数据库的启动和关闭及一些DDL操作。
3、触发器分三种类型: DML触发器(邮表上执行的INSERT/UPDATE/DELETE操作触发);INSTEAD OF替代触发器(用于视图的操作);系统触发器(用于系统事件触发)。
4、触发器的作用:安全性方面,确定用户的操作是否可继续执行;产生对数据值修改的审计,将修改的信息记录下来,产生数据改动记录;提供更灵活的完整性校验规则,能够根据
更复杂的规则校验数据;提供表数据的同步复制,使多个表的数据达到同步;事件日志记录,记录数据库的重要操作信息。
5、创建语句级DML触发器语法"create [or replace] trigger 触发器名 触发时机 事件 on 表名 when 条件 触发器执行体",如:
 --创建DML触发器,阻止在非工作时间对EMP表插入记录
 SQL>CREATE OR REPLACE TRIGGER secure_emp  
  BEFORE INSERT ON emp  /*触发时机可以是AFTER或BEFORE*/
 BEGIN 
  IF (TO_CHAR(SYSDATE,'DY') IN ('星期六','星期日')) OR
  (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
  THEN
   RAISE_APPLICATION_ERROR(-20500,'你只能在工作时间对EMP表执行插入操作'); /*将应用程序的错误从服务器端传递到客户端应用程序*/
  END IF;
 END;
 用户如果在非工作时间给EMP表增加记录,触发器会自动触发并报错。
6、行级触发器:增加了for each row子句,表示每次对表中的每行操作时都会触发这个触发器。如:
 --创建行级触发器,当降低薪水时引发异常
 SQL>CREATE OR REPLACE TRIGGER check_salary  
  BEFORE UPDATE OF sal ON emp  /*触发时机可以是AFTER或BEFORE*/
  FOR EACH ROW
  WHEN (NEW.sal<OLD.sal)
 BEGIN 
  RAISE_APPLICATION_ERROR(-20501,'不允许降低薪水'); /*-20501为错误编号,自己定义的,取值范围从-20000到-20999之间,为避免与oracle冲突*/
 END;
7、创建替代触发器:当需要通过视图影响它所对应的基表时,可以通过替代触发器来实现,如:
 --创建替代触发器,将对视图的删除操作,转换为对emp表的删除操作
 SQL>CREATE OR REPLACE TRIGGER emp_view_delete  
  INSTEAD OF delete ON emp_view  /*emp_view为视图名*/
  FOR EACH ROW
 BEGIN 
  DELETE FROM empWHERE deptno= :old.deptno;
 END;
8、系统触发器分为两种:用户触发事件(DDL命令;登录或者退出数据库连接)和系统触发事件(启动、关闭数据库;特殊错误发生)。如:
 --创建系统事件触发器,记录用户的登录登出
 SQL>conn sys/oracle as sysdba /*连接登录数据库*/
 SQL>create table event(eventuser varchar2(20),
 action varchar2(20),
 logtime date default sysdate); /*默认为系统时间*/
 --创建数据表event,记录用户的登录
 SQL>CREATE OR REPLACE TRIGGER trig_logon  
  AFTER logon ON DATABASE  
  BEGIN
   --将事件属性插入到事件日志表中
   INSERT INTO sys.event(eventuser,action) VALUES(USER,'log on');
  END;
  
 --创建触发器,记录用户的登出
 SQL>CREATE OR REPLACE TRIGGER trig_logoff  
  AFTER logoff ON DATABASE  
  BEGIN
   --将事件属性插入到事件日志表中
   INSERT INTO sys.event(eventuser,action) VALUES(USER,'log off');
  END;
9、在PL/SQL触发器中调用存储过程的访求是通过CALL命令,如:
 SQL>CREATE OR REPLACE TRIGGER trig_ddl  
  AFTER CREATE OR ALTER OR DROP ON DATABASE  
  CALL 过程名
 注意此种方式创建触发器不需BEGIN和END,程序末尾出无需写分号";"。
10、在数据字典中查看触发器"select object_name from user_objects where object_type='TRIGGER'"
11、触发器默认是启用状态,禁用触发器"alter trigger 触发器名 disable",禁用某表上所有的触发器"alter table 表名 disable all triggers"。
12、删除触发器"drop trigger 触发器名"。
13、触发器的执行顺序,表上所有触发事件的触发顺序如下:
 (1)执行所有的before statement触发器。
 (2)根据操作的行循环地执行:所有的before row 触发器;所有after row触发器。
 (3)执行DML语句,进行完整性约束条件检测。
 (4)执行所有的after statement触发器。
 作用完善多表之间的完整性约束校验,当对主表操作时,如果主表被子表引用,则先对子表操作,这样就不会违反完整性约束了,如:
 --建立触发器之前执行修改操作,此时会报错,违反完整性约束条件
 SQL>UPDATE dept SET deptno=70 WHERE deptno=20;
 --建立触发器
 SQL>CREATE OR REPLACE TRIGGER trig_update  
  AFTER UPDATE OF deptno ON dept /**/
  FOR EACH ROW
  BEGIN
   --先将emp表中所有属于部门20的员工更改到新的部门70下,再修改主表的部门编号
   UPDATE emp SET emp.deptno= :NEW.deptno WHERE emp.deptno= :OLD.deptno;
  END;
 --建立触发器之后执行修改操作,成功
 SQL>UPDATE dept SET deptno=70 WHERE deptno=20; /*把部门编号为20的修改成70*/