trigger学习小节

创建触发器,触发器是一种特殊的存储过程,在用户试图对指定的表执行指定的数据修改语句时自动执行。

语法:
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]database-event ON [database-name .] table-nametrigger-action
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OFdatabase-event ON [database-name .] view-nametrigger-action
database-event ::= DELETE |
INSERT |
UPDATE |
UPDATE OF column-list
trigger-action ::= [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]
BEGIN trigger-step ; [ trigger-step ; ]*
END
trigger-step ::= update-statement | insert-statement | delete-statement | select-statement

例子:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
说明:创建了一个名为update_customer_address的触发器,当用户更新customers表中的address字段时,将触发并更新orders表中的address字段为新的值。
比如执行如下一条语句:UPDATE customers SET address = ‘1 Main St.’ WHERE name = ‘Jack Jones’;
数据库将自动执行如下语句:
UPDATE orders SET address = ‘1 Main St.’ WHERE customer_name = ‘Jack Jones’;

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据