Thursday, April 09, 2015

Sql Server Trigger to set Last Change Date & Last Change User

ALTER TRIGGER [dbo].[Holidays_SetModified] ON [dbo].[Holidays]
AFTER INSERT,UPDATE AS
BEGIN
  --PRINT '[sf_KISData].[sf_KISData_Cascade_Modified] BEGIN'
  DECLARE @id AS int
  DECLARE @mod AS DATETIME
  DECLARE @LUserId as int
  DECLARE curInserted CURSOR LOCAL FOR
        SELECT id, DateLastModified,LUserId FROM Inserted
  OPEN curInserted FETCH NEXT FROM curInserted INTO @id, @mod, @LUserId
  WHILE (@@FETCH_STATUS = 0)
   BEGIN
     IF NOT UPDATE(DateLastModified) OR @mod IS NULL
     BEGIN
        UPDATE Holidays SET DateLastModified= getutcdate(), LUserId=User_Id() WHERE id= @id      END
     FETCH NEXT FROM curInserted INTO @id, @mod, @LUserId
 END
 CLOSE curInserted
 --PRINT '[sf_KISData].[sf_KISData_Cascade_Modified] END'
END

No comments: