[SQL SERVER][TSQL] 建立 Trigger For Update
網友問題,自己紀錄一下(雖然我很不喜歡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