TheSQLGuru
3/20/2007 1:40:00 PM
The trigger failed to fire for INSERTs because it is only declared FOR
UPDATE. Triggers can fire for any combination of UPDATE, DELETE and INSERT.
--
TheSQLGuru
President
Indicium Resources, Inc.
"fhillipo" <fhillipo@discussions.microsoft.com> wrote in message
news:194946EB-FEC7-4D35-B6D1-B6D7E81BAEA5@microsoft.com...
>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
> were 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
>
> fhillipo