Advanced Search

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

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

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.