Trigger

fhillipo · March 20th, 2007, 06:05 AM

I have a requirement to create an audit trail for a set of SQL Server 2000 database tables. The tables have no primary keys.

The audit fields are

createdBy
dateCreated
UpdatedBy
dateUpdated

I successfully updated the table with the 4 fields for audit trail
The first 2 audit fields (createdBy, dateCreated) I've populated successfully using Default [User_Name() and Getdate()]
To populated the UpdatedBy and DateUpdated fields I've opted for triggers.
However when it comes to the trigger part there is a slight problem.

The trigger fires to update the UpdatedBy and dateUpdated fields for all records that existed before the 4 audit trail fields were added.
However, for newly inserted records, the UpdatedBy and DateUpdated fields are not updated because the trigger failed to fire.

Any assistance appreciated.

______________________________________________

Please find below the table update and trigger definition:

--Table Update

ALTER TABLE [dbo].[Table_1] ADD
[createdBy] [varchar] (40) DEFAULT USER_NAME() NOT NULL,
[dateCreated] [datetime] DEFAULT GETDATE() NOT NULL,
[updatedBy] [varchar] (40),
[dateUpdated] [datetime] NULL

--Trigger Definition

CREATE TRIGGER [TR_Table_1]
ON Table_1
FOR UPDATE
AS
IF EXISTS (SELECT d.col_a, d.col_b, d.col_c, d.col_d, d.col_e FROM deleted d, Table_1)
--IF (SELECT COUNT(*) FROM deleted, Table_1) > 0
BEGIN
    Update Database_1..Table_1
    SET updatedBy = USER_NAME(),
        dateUpdated = GETDATE()
    WHERE col_a IN (SELECT deleted.col_a FROM deleted, Table_1)
    AND col_b IN (SELECT deleted.col_b FROM deleted, Table_1)
    AND col_c IN (SELECT deleted.col_c FROM deleted, Table_1)
    AND col_d IN (SELECT deleted.col_d FROM deleted, Table_1)
    AND col_e IN (SELECT deleted.col_e FROM deleted, Table_1)
END

happygv · March 20th, 2007, 07:44 AM

This is how you should be writing it. I dont understand why you wanted to use the DELETED table in your code mentioned above.

Code:

CREATE TRIGGER [TR_Table_1] ON [dbo].[Table_1]
FOR UPDATE
AS
BEGIN
    Update dbo.Table_1
    SET updatedBy = USER_NAME(),
    dateUpdated = GETDATE()
    where Table_1.Primary_or_reference_Column = INSERTED.Primary_or_reference_Column
END

The one marked in red is the primary key column or the column that is has unique value in the table that suits your scenario.

Hope that helps.
Cheers.
_________________________
- Vijay G
Strive for Perfection