Creating a trigger on SQL Server 2008 R2 to catch a record update

Question

Using Great Plains here and one of our users keeps screwing up customer data so we want to put a trigger on the customer table and so we can find out who it is.

Anyway, I created a table called audit_RM00101 as follows:

DATE        nchar(10)   
CUSTNMBR    char(15)    
CUSTNAME    char(65)    
UPSZONE     char(3) 
SALSTERR    char(15)    
USERID      nchar(100)

I want to capture those same fields from the table I want to audit so I wrote the trigger as follows:

CREATE TRIGGER CatchCustomerRegionUpdate
ON RM00101
FOR UPDATE
AS
DECLARE @UserID VARCHAR(128)
SELECT @UserID = system_user
INSERT INTO audit_RM00101 
SELECT DATE, CUSTNMBR, CUSTNAME, UPSZONE, SALSTERR, @UserID FROM UPDATED

The trigger gets created just fine but when I try to test it by updating a customer record in Great Plains, Great Plains throws up an ugly error and the trigger doesn't get fired.

What am I doing wrong here?

Thanks.

Answer 1

in a trigger, you get the DELETED and INSERTED tables, there is no UPDATED, so replace FROM UPDATED with FROM INSERTED

also try to fix your USERID column, your audit_RM00101.USERID is a nchar(100) while @UserID is a VARCHAR(128).

EDIT based on OPs comment: Ah, so there is no way to audit when a table is updated by using a trigger?

• in a trigger when deleting, DELETED is populated, but INSERTED is empty
• in a trigger when updating, DELETED is populated with the original value, and INSERTED is populated with the newly updated values
• in a trigger when inserting, DELETED is empty, but INSERTED has the newly inserted values

Answer 2

There is no UPDATED in SQL Server; just inserted and deleted. Also, it makes sense to add IF @@ROWCOUNT = 0 RETURN in the very beginning of triger's body. When UPDATE takes place, both inserted and deleted tables are not empty. You may add the following code to make sure you handle UPDATE, not insert/delete:

IF EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) 
BEGIN
 -- handle update
END ;

It's not really important for your trigger because you specify just FOR UPDATE, it would be important if you had, for instance, FOR UPDATE, INSERT, DELETE.