Posted By

lance on 08/05/10


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

hchattaway


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


 / Published in: SQL
 

  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  

You need to login to post a comment.