SQL大圣之路笔记——SQL 触发器
1 --作用,对表进行增、删、改操作时,自动进行一个操作 2 --根据触发机制不同,分为:after触发器,instead of替换触发器 3 --创建触发器: 4 create trigger 名称 5 on 表名 6 [After|instead of] [insert|delete|update] 7 as 8 Begin 9 ... 10 end 11 --两个临时表:inserted、deleted 12 --示例:在订单表插入数据时,在订单备份表中插入同一条数据 13 --建议:对于性能影响太大,所以要慎重使用 14 15 alter trigger [dbo].allen_test_trigger--触发器名称 16 on [dbo].test_allen--表名 17 after insert,delete,update 18 as 19 begin 20 --insert 21 insert INTO test_allen_back(id,name)--备份表名 22 SELECT id,name from INSERTED 23 --delete 24 insert INTO test_allen_back(id,name) 25 SELECT id,name from DELETED 26 27 end
1 -- Description: <this trigger is used to track the every change of [stdb_stor_sku_assrtm_reltn> 2 -- ============================================= 3 ALTER TRIGGER [dbo].[trigger_stdb_stor_sku_assrtm_reltn] 4 ON [dbo].[stdb_stor_sku_assrtm_reltn] 5 AFTER INSERT,DELETE,UPDATE 6 AS 7 8 BEGIN 9 10 insert into dbo.[stdb_stor_sku_assrtm_reltn_chg_adt] 11 ( 12 [stor_id] , 13 [sku_assrtm_type_id], 14 [last_upd_usr_id] , 15 [last_upd_dtm] , 16 [biz_pct] , 17 chg_dtm , 18 chg_type 19 20 ) 21 select 22 [stor_id] , 23 [sku_assrtm_type_id], 24 [last_upd_usr_id] , 25 [last_upd_dtm] , 26 [biz_pct] , 27 getdate() 28 ,'Delete' 29 from 30 Deleted 31 32 33 insert into dbo.[stdb_stor_sku_assrtm_reltn_chg_adt] 34 ( 35 [stor_id] , 36 [sku_assrtm_type_id], 37 [last_upd_usr_id] , 38 [last_upd_dtm] , 39 [biz_pct] , 40 chg_dtm , 41 chg_type 42 ) 43 select 44 [stor_id] , 45 [sku_assrtm_type_id], 46 [last_upd_usr_id] , 47 [last_upd_dtm] , 48 [biz_pct] , 49 50 getdate() 51 ,'Insert' 52 from 53 Inserted 54 55 56 57 58 END