Create audit fields in table and create triggers to insert and update them


/ Published in: SQL



Copy this code and paste it in your HTML
  1. --Create audit fields
  2. ALTER TABLE [TABLE]
  3.  
  4. ADD FirstSysDate DATETIME NULL,
  5. FirstUserID VARCHAR(20) NULL,
  6. LastSysDate DATETIME NULL,
  7. LastUserID VARCHAR(20) NULL
  8.  
  9.  
  10. --Create insert and update triggers
  11. CREATE TRIGGER [Table_Insert]
  12. ON [TABLE]
  13. AFTER INSERT
  14. AS
  15. BEGIN
  16. SET NOCOUNT ON;
  17. UPDATE [TABLE]
  18. SET FirstSysDate=GETDATE(),
  19. FirstUserID=SYSTEM_USER
  20. FROM inserted
  21. WHERE inserted.TableID=[TABLE].TableID
  22.  
  23. END
  24. GO
  25. -----------------
  26.  
  27. CREATE TRIGGER [Table_Update]
  28. ON [TABLE]
  29. AFTER UPDATE
  30. AS
  31. BEGIN
  32. SET NOCOUNT ON;
  33. UPDATE [TABLE]
  34. SET LastSysDate=GETDATE(),
  35. LastUserID=SYSTEM_USER
  36. FROM inserted
  37. WHERE inserted.TableID=[TABLE].TableID
  38. END
  39. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.