RiCo技術農場

網友問題，自己紀錄一下(雖然我很不喜歡SQL Server Trigger...XD)

 

需求

1.有人把status改成n的時候，自動update該筆closedate = getdate()

2.有人update modifydate也會等於getdate()

 

CREATE trigger ut_mytestupdate ON mytest for UPDATE 
as
begin
SET NOCOUNT ON;
declare @updatestatus char(1)
declare @tblstatus char(1)
declare @updateno int
declare @updatemodifydate datetime
declare @tblmodifydate datetime
select @updatestatus=[status],@updateno=no,@updatemodifydate=modifydate from inserted
select @tblstatus= [status],@tblmodifydate=modifydate  from deleted 

IF(@updatestatus='n' and @updatestatus <> @tblstatus )
    begin
     update myTest SET [closedate]=getdate() where no=@updateno
    end    
IF( @tblmodifydate <> @updatemodifydate )   
    begin
     update myTest SET [modifydate]=getdate() where no=@updateno  
    end 
end

 

 

 

 

 

測試

UPDATE mytest SET  [status]='n' WHERE NO=2
SELECT * FROM mytest

 

UPDATE mytest SET  [modifydate]='20120903' WHERE NO=1 
SELECT * FROM mytest