1 触发器
资料来源：《http://baike.baidu.com/view/71791.htm?fr=ala0_1_1》
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
1.1 数据库领域名词
触发器可以查询其他表，而且可以包含复杂的 SQL 语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。
触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。
1.2 创建触发器的SQL语法
DELIMITER |
CREATE TRIGGER ` < [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] > ON FOR EACH ROW BEGIN --do something END | 1.3 触发器的优点 触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。 1.4 比较触发器与约束 约束和触发器在特殊情况下各有优势。触发器的主要好处在于它们可以包含使用 Transact-SQL 代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能；但它在所给出的功能上并不总是最好的方法。实体完整性总应在最低级别上通过索引进行强制，这些索引或是 PRIMARY KEY 和 UNIQUE 约束的一部分，或是在约束之外创建的。假设功能可以满足应用程序的功能需求，域完整性应通过 CHECK 约束进行强制，而引用完整性 (RI) 则应通过 FOREIGN KEY 约束进行强制。在约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。 例如：除非 REFERENCES 子句定义了级联引用操作，否则 FOREIGN KEY 约束只能以与另一列中的值完全匹配的值来验证列值。 CHECK 约束只能根据逻辑表达式或同一表中的另一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器。 约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器。 触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。 触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。当更改外键且新值与主键不匹配时，此类触发器就可能发生作用。例如，可以在 titleauthor.title_id 上创建一个插入触发器，使它在新值与 titles.title_id 中的某个值不匹配时回滚一个插入。不过，通常使用 FOREIGN KEY 来达到这个目的。 如果触发器表上存在约束，则在 INSTEAD OF 触发器执行后但在 AFTER 触发器执行前检查这些约束。如果约束破坏，则回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器。 触发器到底可不可以在视图上创建 在 SQL Server™ 联机丛书中，是没有说触发器不能在视图上创建的， 并且在语法解释中表明： 在 CREATE TRIGGER 的 ON 之后可以是视图。 然而，事实似乎并不是如此，很多专家也说触发器不能在视图上创建。我也专门作了测试，的确如此，不管是普通视图还是索引视图，都无法在上面创建触发器，真的是这样吗？请点击详细，但是无可厚非的是：当在临时表或系统表上创建触发器时会遭到拒绝。 深刻理解 FOR CREATE TRIGGER 语句的 FOR 关键字之后可以跟 INSERT、UPDATE、DELETE 中的一个或多个，也就是说在其它情况下是不会触发触发器的， 包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。相关内容 一个有趣的应用我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名, 在触发器中利用回滚就可以巧妙地实现无法更改用户名……详细内容 触发器内部语句出错时…… 这种情况下，前面对数据更改操作将会无效。举个例子，在表中插入数据时触发触发器，而触发器内部此时发生了运行时错误，那么将返回一个错误值，并且拒绝刚才的数据插入。不能在触发器中使用的语句 触发器中可以使用大多数 T-SQL 语句，但如下一些语句是不能在触发器中使用的。 CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。 ALTER 语句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。 DROP 语句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。 DISK 语句，如：DISK INIT、DISK RESIZE。 LOAD 语句，如：LOAD DATABASE、LOAD LOG。 RESTORE 语句，如：RESTORE DATABASE、RESTORE LOG。 RECONFIGURE TRUNCATE TABLE 语句在sybase的触发器中不可使用！ （本人：说的似乎不正确） 1.5 慎用触发器 触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作…… 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。 1.6 触发器命名规则 命名规则：对象名称_状态_DML命令_类型 对象名称：指表名或者视图名称； 状态：after(AFT)、before(BEF)、instead(INS)； DML命令：insert(INS)、update(UPD)、delete(DEL)， 两个DML命令的写法（INS_UPD）， 三具DML命令的写法IUD； 类型：语句、行（ROW）。 2 触发器应用 参考资料：《http://blog.csdn.net/defonds/archive/2009/05/21/4205381.aspx》 2.1 功能 1、 允许/对表的修改 2、 自动生成派生列，比如自增字段 3、 强制数据一致性 4、 提供审计和日志记录 5、 防止无效的事务处理 6、 启用复杂的业务逻辑 2.2 触发器的组成部分 1、 触发器名称 2、 触发语句 3、 触发器 4、 触发操作 2.2.1 触发器名称 create trigger biufer_employees_department_id 命名习惯： biufer（before insert update for each row） employees 表名 department_id 列名 2.2.2 触发语句 比如： 表或视图上的DML语句 DDL语句 数据库关闭或启动,startup shutdown 等等 before insert or update of department_id on employees referencing old as old_value for each row 说明： 1、 无论是否规定了department_id ，对employees表进行insert的时候 2、 对employees表的department_id列进行update的时候 3、 触发器 when (new_value.department_id<>80 )，不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。其中的new_value是代表跟新之后的值。 4、 触发操作 是触发器的主体 begin :new_value.commission_pct :=0; end; 主体很简单，就是将更新后的commission_pct列置为0 触发： insert into employees(employee_id, last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct ) values( 12345,’Chen’,’Donny’, sysdate, 12, ‘*****************’,60,10000,.25); select commission_pct from employees where employee_id=12345; 触发器不会通知用户，便改变了用户的输入值。 2.3 触发器类型 1、 语句触发器 2、 行触发器 3、 INSTEAD OF 触发器 4、 系统条件触发器 5、 用户事件触发器 2.3.1 语句触发器 是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。 2.3.1.1 安全检查 需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的。 1、创建数据表 Create table foo(a number); 2、创建触发器 Create Or Replace Trigger biud_foo Before insert or update or delete On foo declare not_library_user exception; Begin If user not in ('WHN') then RAISE not_library_user; End if; EXCEPTION WHEN not_library_user THEN RAISE_APPLICATION_ERROR(-20001, 'You don t have access to modify this table.'); End; / 3、测试 即使SYS，SYSTEM用户也不能修改foo表中的数据。 insert into whn.foo values (11,sysdate); 2.3.1.2 记录dml操作日志 对修改表的时间、人物进行日志记录。 1、建立试验表 create table employees_copy as select *from hr.employees 2、建立日志表 -- Create table create table EMPLOYEES_LOG ( WHO VARCHAR2(30), WHEN DATE, ACTION CHAR(1) ); 3、创建触发器 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。 Create Or Replace Trigger biud_employee_copy Before insert or update or delete On employees_copy Declare L_action employees_log.action%type; Begin if inserting then L_action := 'I'; elsif updating then L_action := 'U'; elsif deleting then L_action := 'D'; else raise_application_error(-20001, 'You should never ever get this error.'); end if; Insert into employees_log (Who, when, action) Values (user, sysdate, L_action); End; / 4、测试 update employees_copy set salary= salary*1.1; 5、查询 select *from employess_log; 2.3.2 行触发器 是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外： 1、 定义语句中包含FOR EACH ROW子句 2、 在BEFORE……FOR EACH ROW触发器中，用户可以引用受到影响的行值。 比如： 定义： create trigger biufer_employees_department_id before insert or update of department_id on employees_copy referencing old as old_value for each row when (new_value.department_id<>80 ) begin :new_value.commission_pct :=0; end; / Referencing 子句： 执行DML语句之前的值的默认名称是 :old ,之后的值是 :new insert 操作只有:new delete 操作只有 :old update 操作两者都有 referencing子句只是将new 和old重命名为new_value和old_value，目的是避免混淆。比如操作一个名为 new的表时。 作用不很大。 2.3.2.1 为主健生成自增序列号 1、创建数据表 create table foo(id number, data varchar2(20)); 2、创建序列 create sequence foo_seq; 3、创建触发器 create or replace trigger bifer_foo_id_pk before insert on foo for each row begin select foo_seq.nextval into :new.id from dual; end; / 4、插入数据测试 insert into foo(data) values(‘donny’); insert into foo values(5,’Chen’); 5、查询 select * from foo; 2.3.2.2 备份数据（插入、修改、删除） 1、创建测试表 create table employees_copy as select *from hr.employees;（上面已经创建） 2、创建备份表 create table Employees_bak as select * from employees_copy where 1=0; 3、创建触发器 create or replace trigger employees_copy_bef_dui before insert or update or delete on employees_copy referencing old as old_value new as new_value for each row --when (new_value.department_id <> 80) begin if inserting then insert into employees_bak values (:new_value.EMPLOYEE_ID, :new_value.FIRST_NAME, :new_value.LAST_NAME, :new_value.EMAIL, :new_value.PHONE_NUMBER, :new_value.HIRE_DATE, :new_value.JOB_ID, :new_value.SALARY, :new_value.COMMISSION_PCT, :new_value.MANAGER_ID, :new_value.DEPARTMENT_ID); else insert into employees_bak values (:old_value.EMPLOYEE_ID, :old_value.FIRST_NAME, :old_value.LAST_NAME, :old_value.EMAIL, :old_value.PHONE_NUMBER, :old_value.HIRE_DATE, :old_value.JOB_ID, :old_value.SALARY, :old_value.COMMISSION_PCT, :old_value.MANAGER_ID, :old_value.DEPARTMENT_ID); end if; end; / 4、测试 插入、删除、修改employees_copy表中的数据。 5、查看 select * from employees_bak t; 2.3.2.3 对数据操作的约束 根据用户和数据表中某一个字段的值来限定修改和删除，只有指定的用户才可以修改和删除。 1、创建数据表 create table TRG_EMPLOYESS as select* from HR.EMPLOYEES; 2、创建触发器 当TRG_EMPLOYESS中HIRE_DATE大于1999-1-1或者SALARY大于10000时，数据不能被其它用户修改或删除，只有USER_NAME这个用户可以删除。 create or replace trigger TRG_EMPLOYESS_BEF_DU before update or delete on TRG_EMPLOYESS referencing old as old_value new as new_value for each row when (old_value.HIRE_DATE > to_date('1990-1-1','yyyy-mm-dd') or old_value.SALARY > 10000) begin if updating and (:old_value.SALARY > 10000 or :old_value.HIRE_DATE > to_date('1999-1-1','yyyy-mm-dd')) and user<>'WHN' then RAISE_APPLICATION_ERROR (-20002,'你无权修改此数据，请联系管理员！'); end if; if deleting and (:old_value.SALARY > 10000 or :old_value.HIRE_DATE > to_date('1999-1-1','yyyy-mm-dd')) and user<>'WHN' then RAISE_APPLICATION_ERROR (-20001,'你无权删除此数据，请联系管理员！'); end if; delete from whn.trg_employess t where t.employee_id=199; update whn.trg_employess t set t.last_name=t.last_name||'11' where t.employee_id=199; delete from whn.trg_employess t where t.employee_id=205; update whn.trg_employess t set t.last_name=t.last_name||'11' where t.employee_id=205; 2.3.3 INSTEAD OF 触发器更新视图 1、创建视图 Create or replace view vw_trg_employess as Select first_name || ', ' || last_name name, email, phone_number, employee_id emp_id --name无法修改，提示“ORA-01733：此处不请允许虚拟列” update vw_trg_employess set name='Chen1, Donny1',email='163' where emp_id=202; --email可以修改 update vw_trg_employess set email='163' create or replace trigger VW_TRG_EMPLOYESS_INS_UPD INSTEAD OF Update on VW_TRG_EMPLOYESS Begin Update TRG_EMPLOYESS Set EMPLOYEE_ID = :new.EMP_ID, FIRST_NAME = substr(:new.NAME, instr(:new.NAME, ',') + 2), LAST_NAME = substr(:new.NAME, 1, instr(:new.NAME, ',') - 1), PHONE_NUMBER = :new.PHONE_NUMBER, EMAIL = :new.EMAIL --通过测试，此列必须得写，要不然无法更新此值，即使更新语句中已经写了更新此列 Where EMPLOYEE_ID = :old.EMP_ID; update vw_trg_employess set name='Chen1, Donny1',email='163' 用户事件：CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE / RENAME / TRUNCATE / LOGOFF 1、创建日志表 -- Create table create table TRG_DDL_LOG ( OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(30), ALTER_BY_USER VARCHAR2(30), SYS_EVENT VARCHAR2(30), ALTERATION_TIME DATE ); / 1、创建触发器 --DDL所有操作日志 create or replace trigger trg_ddl_database after ddl on database BEGIN if sys.sysevent in ('CREATE', 'ALTER', 'DROP', 'ANALYZE', 'AUDIT', 'GRANT', 'REVOKE', 'RENAME', 'TRUNCATE') then INSERT INTO trg_ddl_log VALUES (sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_type, sys.login_user, sys.sysevent, sysdate); end if; END; --某个用户的删除操作 create or replace trigger trg_ddl_username_drop before DROP on username.schema BEGIN INSERT INTO trg_ddl_log VALUES (sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_type, sys.login_user, sys.sysevent, sysdate); END; / 3.、测试 create table drop_me(a number); create view drop_me_view as select *from drop_me; drop view drop_me_view; drop table drop_me; 4、查询 select * from trg_ddl_log t; 2.3.5 数据库触发器 可以创建在数据库事件上的触发器，包括错误、登录、注销、关闭和启动。 2.3.5.1 数据库启动、关闭 1、创建日志表 -- Create table create table TRG_SYSTEM_LOG ( INSTANCE_NUM NUMBER, DATABASE_NAME VARCHAR2(50), LOGIN_USER VARCHAR2(30), SYSEVENT VARCHAR2(20), TIME DATE ) tablespace TEST_DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial K minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column TRG_SYSTEM_LOG.INSTANCE_NUM is '实例名'; comment on column TRG_SYSTEM_LOG.DATABASE_NAME is '数据库名'; comment on column TRG_SYSTEM_LOG.LOGIN_USER is '登录用户名'; comment on column TRG_SYSTEM_LOG.SYSEVENT is '系统事件'; comment on column TRG_SYSTEM_LOG.TIME is '操作时间';; / 2、创建触发器 --启动日志 create or replace trigger trg_system_startup after startup on database begin insert into trg_system_log values (ora_instance_num, ora_database_name, ora_login_user, ora_sysevent, sysdate); end; --关闭日志 create or replace trigger trg_system_shutdown before shutdown on database begin insert into trg_system_log values (ora_instance_num, ora_database_name, ora_login_user, ora_sysevent, sysdate); end; 3、查询日志信息 select * from trg_system_log t 2.3.5.2 数据库连接触发器 1、创建日志表 -- Create table create table TRG_LOGONOFF_LOG ( USERNAME VARCHAR2(20), LOG_TIME DATE, ONOFF VARCHAR2(6), ADDRESS VARCHAR2(30) ); 2、创建触发器 记录用户TEST的连接日志信息。 --登录日志 create or replace trigger trg_logon after logon on database begin if ora_login_user <> 'TEST' then insert into trg_logonoff_log values (ora_login_user, sysdate, 'logon', ora_client_ip_address); end if; end; / --退出日志 create or replace trigger trg_logoff before logoff on database begin if ora_login_user <> 'TEST' then insert into trg_logonoff_log values (ora_login_user, sysdate, 'logoff', ora_client_ip_address); end if; end; / 3、查询日志 select * from trg_logonoff_log t 2.4 禁用和启用触发器 方法一： 要使用此命令，必须拥有表或者拥有ALTER ANY TRIGGER 系统权限。 alter trigger alter trigger 方法二： 使用alter table命令，必须拥有有或者拥有ALTER ANY TABLE系统权限。 Alter table Alter table 事务处理： 在触发器中，不能使用commit / rollback 因为ddl语句具有隐式的commit，所以也不允许使用下载本文
3、测试end;
2、尝试更新email和name From trg_employess;
3、创建触发器where emp_id=202
4、测试end;
2.3.4 模式触发器where emp_id=202;
