针对插入(insert)操作,增加名为tr_product_i的触发器:
CREATE TRIGGER tr_product_i ON product AFTER INSERT AS if @@rowcount = 0 --为了避免占用资源,当影响行数为0时,结束触发器 return insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'insert',getdate() from inserted GO
针对更新(update)操作,增加名为tr_product_u的触发器:
CREATE TRIGGER tr_product_u ON product AFTER UPDATE AS if @@rowcount = 0 --为了避免占用资源,当影响行数为0时,结束触发器 return /*更新前*/ insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'update',getdate() from deleted /*更新后*/ insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'update',getdate() from inserted GO
针对删除(delete)操作,增加名为tr_product_d的触发器:
CREATE TRIGGER tr_product_d ON product AFTER DELETE AS if @@rowcount = 0 --为了避免占用资源,当影响行数为0时,结束触发器 return insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'delete',getdate() from deleted GO
3.测试触发器
插入(insert)测试
INSERT INTO product(name,description,unit_cost,pub_time) VALUES('逗比','这是一个逗比的测试数据',200.5,'1990-11-18') GO SELECT * FROM product SELECT * FROM product_log GO
更新(update)测试
UPDATE product SET unit_cost=250.0 WHERE name='逗比' GO SELECT * FROM product SELECT * FROM product_log GO
删除(delete)测试
DELETE FROM product WHERE name='逗比' GO SELECT * FROM product SELECT * FROM product_log GO
好了这篇文章就介绍到这了,需要的朋友可以参考一下。
更多SQL内容来自木庄网络博客