SQL大圣之路笔记—

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