触发器定义:触发器是特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。
触发器功能:1、答应/限制对表的修改;2、自动生成派生列,比如自增字段;3、强制数据一致性;4、提供审计和日志记录;5、防止无效的事务处理;6、启用复杂的业务逻辑。
触发器的组成部分:1、触发器名称;2、 触发语句;3、触发器限制;4、 触发操作。
触发器类型:1、语句触发器;2、 行触发器;3、 instead of 触发器;4、 系统条件触发器;5、用户事件触发器。
触发器中new和old默认值如下:
Data Operation Old value New value
insert null Inserted value
update Value before update Value after update
delete Value before delete null
实例---行触发器
create or replace trigger tri_em_branch
after insert or update or delete
--after在数据库动作之后触发器执行;insert,update,delete在插入,修改,删除后触发器执行。
on em_branch --数据库触发器所在的表
for each row --对表的每一行触发器执行一次。
/*將組別資料寫入生管組別檔*/
Declare
--变量声明
v_fact_no em_branch.fact_no%type;
v_sec_no em_branch.branch_no%type;
v_sec_name em_branch.branch_nm%type;
v_dept_no em_branch.dept_no%type;
v_floor_no em_branch.floor_no%type;
v_fact_no_d em_pri_fact_d.pri_fact_no%type ;
v_if_flag em_branch.if_flag%type;
begin
if updating or deleting then
v_fact_no :=:old.fact_no;
v_sec_no :=:old.branch_no;
v_if_flag :=:old.if_flag;
end if;
if updating or inserting then
v_fact_no :=:new.fact_no;
v_sec_no :=:new.branch_no;
v_sec_name :=:new.branch_nm;
v_dept_no :=:new.dept_no;
v_floor_no :=:new.floor_no;
v_if_flag :=:new.if_flag;
if substr(v_sec_no,1,1) = 'A' then
v_fact_no_d := '0213' ;
else
if substr(v_sec_no,1,1) = 'C' then
v_fact_no_d := '0215' ;
else
v_fact_no_d := '0214' ;
end if;
end if ;
end if;
if updating then
update sg_pro_sec@lkerp_43
set sec_name = v_sec_name,
dept_no = v_dept_no,
floor_no = v_floor_no,
if_flag = v_if_flag
where fact_no = v_fact_no
and sec_no = v_sec_no;
update em_pri_fact_d
set branch_nm = v_sec_name,
if_flag = v_if_flag
where fact_no = v_fact_no
and branch_no = v_sec_no ;
update em_pnl
set if_flag = v_if_flag
where fact_no = v_fact_no and branch_no = v_sec_no;
end if ;
if inserting then
insert into em_pri_fact_d(fact_no,pri_fact_no,branch_no,branch_nm,if_flag)
values(v_fact_no,v_fact_no_d,v_sec_no,v_sec_name,v_if_flag) ;
insert into sg_pro_sec@lkerp_43(fact_no,sec_no,sec_name,dept_no,floor_no,if_flag)
values(v_fact_no,v_sec_no,v_sec_name,v_dept_no,v_floor_no,v_if_flag);
end if;
if deleting then
delete from sg_pro_sec@lkerp_43 where fact_no = v_fact_no and sec_no = v_sec_no;
delete from em_pri_fact_d where fact_no = v_fact_no and branch_no = v_sec_no;
end if;
end em_branch;
SQLite创建触发器 CREATE TRIGGER
CREATE TRIGGER sql-statement ::=CREATE[TEMP|TEMPORARY]TRIGGERtrigger-name[BEFORE|AFTER]
database-eventON[database-name.]table-name
trigger-actionsql-statement ::=CREATE[TEMP|TEMPORARY]TRIGGERtrigger-nameINSTEAD OF
database-eventON[database-name.]view-name
trigger-actiondatabase-event ::=DELETE|
INSERT|
UPDATE|
UPDATE OFcolumn-listtrigger-action ::=[FOR EACH ROW|FOR EACH STATEMENT][WHENexpression]
BEGIN
trigger-step;[trigger-step;]*
ENDtrigger-step ::=update-statement|insert-statement|
delete-statement|select-statement
CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件(database-event) 发生时自动进行的数据库操作(trigger-action).
触发器可由在特殊表上执行的DELETE, INSERT, UPDATE等语句触发,或UPDATE表中特定的字段时触发。
现在SQLite仅支持FOR EACH ROW触发器,不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW .FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在(由WHEN子句决定的)数据库插入,更改或删除的每一行触发trigger.
WHEN子句和trigger-steps可以使用"NEW.column-name"和"OLD.column-name"的引用形式访问正在被插入,更改或 删除的行的元素,column-name是触发器关联的表中的字段名。OLD 和 NEW 引用只在触发器与之相关的trigger-event处可用,例如:
INSERTNEW可用UPDATENEW和OLD均可用DELETEOLD可用
当使用WHEN子句,trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。
trigger-time决定了trigger-steps执行的时间,它是相对于关联行的插入,删除和修改而言的。
作为的一部分trigger-step的UPDATE 或 INSERT可以使用ON CONFLICT子句。 但若触发trigger的语句使用了ON CONFLICT子句,则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。
关联表被撤销时触发器被自动删除。
不仅在表上,在视图上一样可以创建触发器,在CREATE TRIGGER语句中使用INSTEAD OF即可。 若视图上定义了一个或多个ON INSERT, ON DELETE, ON UPDATE触发器,则相应地对视图执行INSERT,DELETE 或UPDATE语句 不会出错,而会触发关联的触发器。视图关联的表不会被修改。(除了由触发器进行的修改操作)。
Example:
假设"customers"表存储了客户信息,"orders"表存储了订单信息,下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END;
定义了该触发器后执行如下语句:
UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’;
会使下面的语句自动执行:
UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;
注意,目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的 INTEGER PRIMARY KEY域,而该域将由触发该触发器的语句进行修改,则可能根本不会修改该域。 可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。
一个特殊的SQL函数RAISE()可用于触发器程序,使用如下语法:
raise-function ::=RAISE ( ABORT,error-message)|
RAISE ( FAIL,error-message)|
RAISE ( ROLLBACK,error-message)|
RAISE ( IGNORE )
当触发器程序执行中调用了上述前三个之一的形式时,则执行指定的ON CONFLICT进程(ABORT, FAIL或者ROLLBACK) 且终止当前查询,返回一个SQLITE_CONSTRAINT错误并说明错误信息。
当调用RAISE(IGNORE),当前触发器程序的余下部分,触发该触发器的语句和任何之后的触发器程序被忽略并且 不恢复对数据库的已有改变。 若触发触发器的语句是一个触发器程序本身的一部分,则原触发器程序从下一步起继续执行。
使用DROP TRIGGER删除触发器