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

Revision: 29925

at August 5, 2010 05:50 by lance

Initial Code

--Create audit fields
ALTER TABLE [Table]

     ADD FirstSysDate DATETIME NULL,
     FirstUserID varchar(20) NULL,
     LastSysDate DATETIME NULL,
     LastUserID  varchar(20) NULL


--Create insert and update triggers
CREATE TRIGGER [Table_Insert] 
   ON  [Table] 
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
UPDATE [Table]
SET	FirstSysDate=GETDATE(),
 FirstUserID=SYSTEM_USER
FROM inserted
WHERE inserted.TableID=[Table].TableID

END
GO
-----------------

CREATE TRIGGER [Table_Update] 
   ON  [Table] 
  AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
UPDATE [Table]
SET	LastSysDate=GETDATE(),
 LastUserID=SYSTEM_USER
FROM inserted
WHERE inserted.TableID=[Table].TableID
END
GO

Initial URL

Initial Description

Initial Title

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

Initial Tags

Initial Language

SQL

Choose a language for easy browsing: