none
触发器 RRS feed

  • 问题

  • 表1
    ID  NAME   TEL1    TEL2
    01  AAA  4545454  7877855
    02  BBB  6556656  9831825

    UPDATE 表1 SET TEL1=4444444,TEL2=454554 WHERE ID=01

    当表1的字段 TEL1,TEL2 任意一个字段改变插入LOG表

    表LOG

    ID   NAME   更改前TEL1  更改后TEL1  更改前TEL2  更改后TEL2
    01   AAA     4545454    4444444     7877855     454554

    触发器如何实现?请高手指点,非常感谢!!!

    2011年8月23日 5:27

答案

  • Create table 表1
    (
     ID varchar(50),
     Name varchar(50),
     TEL1 varchar(50),
     TEL2 varchar(50)
    )
    Go
    /*測試數據*/
    Insert into 表1 values ('01','AAA','4545454','7877855'),('02','BBB','6556656','9831825')
    
    
    Create table 表Log
    (
    
     ID varchar(50),
     Name varchar(50),
     更改前TEL1 varchar(50),
     更改后TEL1 varchar(50),
     更改前TEL2 varchar(50),
     更改后TEL2 varchar(50)
    )
    Go
    


    以下才是重點

    Create trigger 表1_trigger
     on 表1
    After Update
     AS
    
    Declare @insertedID varchar(50)
    Select @insertedID = ID from inserted
    Declare @insertedName varchar(50)
    Select @insertedName = Name from inserted
    Declare @deletedTEL1 varchar(50)
    Select @deletedTEL1 = TEL1 from deleted
    Declare @insertedTEL1 varchar(50)
    Select @insertedTEL1 = TEL1 from inserted
    Declare @deletedTEL2 varchar(50)
    Select @deletedTEL2 = TEL2 from deleted
    Declare @insertedTEL2 varchar(50)
    Select @insertedTEL2 = TEL2 from inserted
    
    Insert into 表Log values (@insertedID,@insertedName,@deletedTEL1,@insertedTEL1,@deletedTEL2,@insertedTEL2)
    

    樓主您沒說表Log的ID、NAME,這兩個字段是抓更改前還是更改后?(以上範例我抓更改后的ID和Name)

     

    參考文件:http://msdn.microsoft.com/en-us/library/ms189799.aspx


    Shadowと愉快なコード達

    2011年8月23日 6:15
  • 提供另外一個寫法供您參考。

     

    if exists (select * from sys.objects where type='U' and name='tab1')
    	drop table tab1
    go
    --表1
    create table tab1
    (
    	id	varchar(10)
    	,name nvarchar(10)
    	,tel1	varchar(10)
    	,tel2 varchar(10)
    )
    go
    
    insert into tab1 values ('01','AAA','4545454','7877855'),('02','BBB','6556656','9831825')
    go
    
    if exists (select * from sys.objects where type='U' and name='tab2')
    	drop table tab2
    go
    --表2
    create table tab2
    (
    	id	varchar(10)
    	,name nvarchar(10)
    	,tel1	varchar(10) -- 更改前TEL1
    	,tel11	varchar(10) --更改後TEL1
    	,tel2 varchar(10) --更改前TEL2 
    	,tel21 varchar(10) --更改后TEL2
    )
    go
    
    if exists (select * from sys.objects where type = 'TR' and name = 'tr_update_tab1')
    	drop trigger tr_update_tab1
    go
    
    create trigger tr_update_tab1
    on tab1
    after update
    as
    	insert into tab2
    	select d.id,d.name,d.tel1,case when i.tel1 = d.tel1 then '未修改' else i.tel1 end
    			,d.tel2,case when i.tel2 = d.tel2 then '未修改' else i.tel2 end
    	from deleted d
    	inner join inserted i
    	on d.id = i.id
    
    go
    


     


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年8月23日 6:30

全部回复

  • 看过触发器的语法么?

    你创建一个update的触发器。

    在触发器内检查是否修改了这个列,如果修改就更新到log表内。

     

    建议在写代码前仔细看看sql server的联机丛书中的触发器。


    family as water
    2011年8月23日 6:12
  • Create table 表1
    (
     ID varchar(50),
     Name varchar(50),
     TEL1 varchar(50),
     TEL2 varchar(50)
    )
    Go
    /*測試數據*/
    Insert into 表1 values ('01','AAA','4545454','7877855'),('02','BBB','6556656','9831825')
    
    
    Create table 表Log
    (
    
     ID varchar(50),
     Name varchar(50),
     更改前TEL1 varchar(50),
     更改后TEL1 varchar(50),
     更改前TEL2 varchar(50),
     更改后TEL2 varchar(50)
    )
    Go
    


    以下才是重點

    Create trigger 表1_trigger
     on 表1
    After Update
     AS
    
    Declare @insertedID varchar(50)
    Select @insertedID = ID from inserted
    Declare @insertedName varchar(50)
    Select @insertedName = Name from inserted
    Declare @deletedTEL1 varchar(50)
    Select @deletedTEL1 = TEL1 from deleted
    Declare @insertedTEL1 varchar(50)
    Select @insertedTEL1 = TEL1 from inserted
    Declare @deletedTEL2 varchar(50)
    Select @deletedTEL2 = TEL2 from deleted
    Declare @insertedTEL2 varchar(50)
    Select @insertedTEL2 = TEL2 from inserted
    
    Insert into 表Log values (@insertedID,@insertedName,@deletedTEL1,@insertedTEL1,@deletedTEL2,@insertedTEL2)
    

    樓主您沒說表Log的ID、NAME,這兩個字段是抓更改前還是更改后?(以上範例我抓更改后的ID和Name)

     

    參考文件:http://msdn.microsoft.com/en-us/library/ms189799.aspx


    Shadowと愉快なコード達

    2011年8月23日 6:15
  • 提供另外一個寫法供您參考。

     

    if exists (select * from sys.objects where type='U' and name='tab1')
    	drop table tab1
    go
    --表1
    create table tab1
    (
    	id	varchar(10)
    	,name nvarchar(10)
    	,tel1	varchar(10)
    	,tel2 varchar(10)
    )
    go
    
    insert into tab1 values ('01','AAA','4545454','7877855'),('02','BBB','6556656','9831825')
    go
    
    if exists (select * from sys.objects where type='U' and name='tab2')
    	drop table tab2
    go
    --表2
    create table tab2
    (
    	id	varchar(10)
    	,name nvarchar(10)
    	,tel1	varchar(10) -- 更改前TEL1
    	,tel11	varchar(10) --更改後TEL1
    	,tel2 varchar(10) --更改前TEL2 
    	,tel21 varchar(10) --更改后TEL2
    )
    go
    
    if exists (select * from sys.objects where type = 'TR' and name = 'tr_update_tab1')
    	drop trigger tr_update_tab1
    go
    
    create trigger tr_update_tab1
    on tab1
    after update
    as
    	insert into tab2
    	select d.id,d.name,d.tel1,case when i.tel1 = d.tel1 then '未修改' else i.tel1 end
    			,d.tel2,case when i.tel2 = d.tel2 then '未修改' else i.tel2 end
    	from deleted d
    	inner join inserted i
    	on d.id = i.id
    
    go
    


     


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年8月23日 6:30
  • 另外也可以用Output子句來做到類似DML Trigger的功能。

    if exists (select * from sys.objects where type='U' and name='tab1')
    	drop table tab1
    go
    --表1
    create table tab1
    (
    	id	varchar(10)
    	,name nvarchar(10)
    	,tel1	varchar(10)
    	,tel2 varchar(10)
    )
    go
    
    insert into tab1 values ('01','AAA','4545454','7877855'),('02','BBB','6556656','9831825')
    go
    
    if exists (select * from sys.objects where type='U' and name='tab2')
    	drop table tab2
    go
    --表2
    create table tab2
    (
    	id	varchar(10)
    	,name nvarchar(10)
    	,tel1	varchar(10) -- 更改前TEL1
    	,tel11	varchar(10) --更改後TEL1 
    	,tel2 varchar(10) -- 更改前TEL2
    	,tel21 varchar(10) --更改後TEL2
    )
    go
    
    if exists (select * from sys.objects where type = 'TR' and name = 'tr_update_tab1')
    	drop trigger tr_update_tab1
    go
    
    select *
    from tab1
    select * 
    from tab2
    
    update tab1 
    set tel1 = '11111'
    output deleted.id,deleted.name
    		  ,deleted.tel1
    		  ,deleted.tel2
    		  ,case when inserted.tel1 = deleted.tel1 then '未修改' else inserted.tel1 end
    		 ,case when inserted.tel2 = deleted.tel2 then '未修改' else inserted.tel2 end 
    into tab2
    where id = '01'
    
    select *
    from tab2


     


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2011年8月23日 6:42
  • 请问一下,这样会影响表的查询速度吗?
    2011年8月23日 6:52
  • 请问一下,这样会影响表的查询速度吗?
    請問您問的是Output子句嗎?
    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    2011年8月23日 6:56
  • Output子句
    2011年8月23日 6:59
  • Output子句

    就我自己的使用上倒沒遇過因為Output影響查詢速度。不過MSDN上倒是有一些參考資料您可以看看,以下節錄自MSDN:

     

    將從 OUTPUT 子句傳回的資料插入至資料表
    當您要擷取巢狀 INSERT、UPDATE、DELETE 或 MERGE 陳述式中 OUTPUT 子句的結果,並且將這些結果插入目標資料表時,請記住下列資訊:
    
    整個作業是不可部分完成的。不是 INSERT 陳述式和包含 OUTPUT 子句的巢狀 DML 陳述式都可以執行,就是整個陳述式都失敗。
    
    下列限制適用於外部 INSERT 陳述式的目標:
    
    此目標不得為遠端資料表、檢視或通用資料表運算式。
    
    此目標不得具有 FOREIGN KEY 條件約束,或由 FOREIGN KEY 條件約束所參考。
    
    不得針對此目標定義觸發程序。
    
    此目標不得參與合併式複寫或是交易式複寫的可更新訂閱。
    
    下列限制適用於巢狀 DML 陳述式:
    
    此目標不得為遠端資料表或資料分割檢視。
    
    來源本身不得包含 <dml_table_source> 子句。
    
    含有 <dml_table_source> 子句的 INSERT 陳述式不支援 OUTPUT INTO 子句。
    
    @@ROWCOUNT 只會傳回外部 INSERT 陳述式所插入的資料列。
    
    @@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 只會傳回巢狀 DML 陳述式所產生的識別值,而不會傳回外部 INSERT 陳述式所產生的識別值。
    
    查詢通知會將此陳述式視為單一實體,而且所建立的任何訊息類型都將成為巢狀 DML 的類型,即使重大變更來自外部 INSERT 陳述式本身也一樣。
    
    在 <dml_table_source> 子句中,SELECT 和 WHERE 子句不得包含子查詢、彙總函式、排名函數、全文檢索述詞、執行資料存取的使用者定義函數或 TEXTPTR 函數。
    


     


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2011年8月23日 7:02