触发器的大组件:
触发器事件 定义激活触发器的事件。触发器事件包括INSERT、UPDATE与DELETE
定时条件 定义触发器何时执行。它可以在触发器事件执行之前(BEFORE)执行,也可以在触发器事件执行之后(AFTER)执行或在触发器事件执行过程中(FOR EACH ROW)执行。
触发器操作 定义激活触发器时执行的SQL语句,包括INSERT,DELETE,UPDATE与EXECUTE PROCEDURE
触发器事件 定义激活触发器的事件。触发器事件包括INSERT、UPDATE与DELETE
定时条件 定义触发器何时执行。它可以在触发器事件执行之前(BEFORE)执行,也可以在触发器事件执行之后(AFTER)执行或在触发器事件执行过程中(FOR EACH ROW)执行。
触发器操作 定义激活触发器时执行的SQL语句,包括INSERT,DELETE,UPDATE与EXECUTE PROCEDURE
INSERT触发器的语法如下:
CREATE TRIGGER trigger_name INSERT ON table_name
BEFORE [WHEN (condition)] (trig_action1,trig_action2,...)|
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)|
AFTER [WHEN (condition)] (trig_action1,trig_action2,...)|
[DISABLED|ENABLED]
or
CREATE TRIGGER trigger_name INSERT ON table_name
REFERENCING NEW AS correlation_name
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)
[BEFORE [WHEN (conditon)](trig_action3,trig_action4,...)]|
[AFTER [WHEN (conditon)] (trig_action3,trig_action4,...)]
[DISABLED|ENABLED]
CREATE TRIGGER trigger_name INSERT ON table_name
BEFORE [WHEN (condition)] (trig_action1,trig_action2,...)|
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)|
AFTER [WHEN (condition)] (trig_action1,trig_action2,...)|
[DISABLED|ENABLED]
or
CREATE TRIGGER trigger_name INSERT ON table_name
REFERENCING NEW AS correlation_name
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)
[BEFORE [WHEN (conditon)](trig_action3,trig_action4,...)]|
[AFTER [WHEN (conditon)] (trig_action3,trig_action4,...)]
[DISABLED|ENABLED]
DELET触发器的语法如下:
CREATE TRIGGER trigger_name DELETE ON table_name
BEFORE [WHEN (condition)] (trig_action1,trig_action2,...)|
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)|
AFTER [WHEN (condition)] (trig_action1,trig_action2,...)|
[DISABLED|ENABLED]
or
CREATE TRIGGER trigger_name DELETE ON table_name
REFERENCING NEW AS correlation_name
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)
[BEFORE [WHEN (conditon)](trig_action3,trig_action4,...)]|
[AFTER [WHEN (conditon)] (trig_action3,trig_action4,...)]
[DISABLED|ENABLED]
BEFORE [WHEN (condition)] (trig_action1,trig_action2,...)|
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)|
AFTER [WHEN (condition)] (trig_action1,trig_action2,...)|
[DISABLED|ENABLED]
or
CREATE TRIGGER trigger_name DELETE ON table_name
REFERENCING NEW AS correlation_name
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)
[BEFORE [WHEN (conditon)](trig_action3,trig_action4,...)]|
[AFTER [WHEN (conditon)] (trig_action3,trig_action4,...)]
[DISABLED|ENABLED]
UPDATE触发器的语法如下:
CREATE TRIGGER trigger_name UPDATE [OF (column,column,...)] ON table_name
BEFORE [WHEN (condition)] (trig_action1,trig_action2,...)|
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)|
AFTER [WHEN (condition)] (trig_action1,trig_action2,...)|
[DISABLED|ENABLED]
or
CREATE TRIGGER trigger_name UPDATE [OF (column,column,...)] ON table_name
REFERENCING NEW AS correlation_name OLD AS correlation_name
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)
[BEFORE [WHEN (conditon)](trig_action3,trig_action4,...)]|
[AFTER [WHEN (conditon)] (trig_action3,trig_action4,...)]
[DISABLED|ENABLED]
CREATE TRIGGER trigger_name UPDATE [OF (column,column,...)] ON table_name
BEFORE [WHEN (condition)] (trig_action1,trig_action2,...)|
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)|
AFTER [WHEN (condition)] (trig_action1,trig_action2,...)|
[DISABLED|ENABLED]
or
CREATE TRIGGER trigger_name UPDATE [OF (column,column,...)] ON table_name
REFERENCING NEW AS correlation_name OLD AS correlation_name
FOR EACH ROW [WHEN (condition)] (trig_action1,trig_action2,...)
[BEFORE [WHEN (conditon)](trig_action3,trig_action4,...)]|
[AFTER [WHEN (conditon)] (trig_action3,trig_action4,...)]
[DISABLED|ENABLED]
要了解触发器的语法,就要将其分解如下:
CREATE TRIGGER 生成触发器对象所需的语句。
trigger_name 用户定义的触发器名,在数据库中唯一,不超过18个字符,以字母开始。
INSERT是一个触发器事件,表中插入行时触发器被激活,一个表只有一个INSERT触发器,
DELETE是一个触发器事件,表中删除行时触发器被激活,一个表只有一个DELETE触发器。
UPDATE是一个触发器事件,表中根性列时触发器被激活,如果包括列清单,则更新列清单中的列时触发器被激活。否则更形表中任何列时触发器被激活,一个表可以有多个UPDATE触发器。但是列清单要相互排斥。
ENABLED指定启动触发器,即执行触发器事件时即激活触发器。这是生成触发器时的默认值。
DISABLED指定生成但关闭触发器,即执行触发器事件时不激活触发器。
trigger_name 用户定义的触发器名,在数据库中唯一,不超过18个字符,以字母开始。
INSERT是一个触发器事件,表中插入行时触发器被激活,一个表只有一个INSERT触发器,
DELETE是一个触发器事件,表中删除行时触发器被激活,一个表只有一个DELETE触发器。
UPDATE是一个触发器事件,表中根性列时触发器被激活,如果包括列清单,则更新列清单中的列时触发器被激活。否则更形表中任何列时触发器被激活,一个表可以有多个UPDATE触发器。但是列清单要相互排斥。
ENABLED指定启动触发器,即执行触发器事件时即激活触发器。这是生成触发器时的默认值。
DISABLED指定生成但关闭触发器,即执行触发器事件时不激活触发器。