SQL大圣之路笔记——SQL 触发器

--作用，对表进行增、删、改操作时，自动进行一个操作
--根据触发机制不同，分为：after触发器，instead of替换触发器
--创建触发器：
    create trigger 名称
    on 表名
    [After|instead of] [insert|delete|update]
    as 
    Begin
    ...
    end
--两个临时表：inserted、deleted
--示例：在订单表插入数据时，在订单备份表中插入同一条数据
--建议：对于性能影响太大，所以要慎重使用

 alter trigger [dbo].allen_test_trigger--触发器名称
  on [dbo].test_allen--表名
  after insert,delete,update
  as 
  begin
    --insert
    insert INTO test_allen_back(id,name)--备份表名
    SELECT id,name from INSERTED
    --delete
    insert INTO test_allen_back(id,name)
    SELECT id,name from DELETED
 
  end

-- Description:    <this trigger is used to track the every change of [stdb_stor_sku_assrtm_reltn>
-- =============================================
ALTER TRIGGER [dbo].[trigger_stdb_stor_sku_assrtm_reltn]
   ON  [dbo].[stdb_stor_sku_assrtm_reltn]
   AFTER INSERT,DELETE,UPDATE
AS 

BEGIN

insert into dbo.[stdb_stor_sku_assrtm_reltn_chg_adt]
(
    [stor_id] ,
    [sku_assrtm_type_id],
    [last_upd_usr_id] ,
    [last_upd_dtm] ,
    [biz_pct] ,
    chg_dtm ,
    chg_type 
  
)
  select 
      [stor_id] ,
    [sku_assrtm_type_id],
    [last_upd_usr_id] ,
    [last_upd_dtm] ,
    [biz_pct] ,
      getdate()
      ,'Delete'
    from
       Deleted    
        

insert into dbo.[stdb_stor_sku_assrtm_reltn_chg_adt]
(
[stor_id] ,
    [sku_assrtm_type_id],
    [last_upd_usr_id] ,
    [last_upd_dtm] ,
    [biz_pct] ,
    chg_dtm ,
    chg_type 
)
  select 
    [stor_id] ,
    [sku_assrtm_type_id],
    [last_upd_usr_id] ,
    [last_upd_dtm] ,
    [biz_pct] ,

      getdate()
      ,'Insert'
    from
       Inserted    


    
    
END