CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008triggers
Tom
Tom

Reputation: 4577

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

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.

Upvotes: 2

Views: 12978

Answers (3)

Immu
Immu

Reputation: 786

we have only two magic tables called INSERTED and DELETED

update indirectly is a Delete statement followed by Insert statement. so you have to update the column's value which is present in INSERTED.

CREATE TRIGGER CatchCustomerRegionUpdate
ON RM00101
AFTER UPDATE
AS
BEGIN
    DECLARE @INSERTED INT, @DELETED INT
    SET @INSERTED = SELECT COUNT(*) FROM INSERTED
    SET @DELETED = SELECT COUNT(*) FROM DELETED

    IF @INSERTED = 1 AND @DELETED = 1
    BEGIN
        UPDATE TABLE1
        SET COL1 = INSERTED_COL1
        WHERE IDCOL = INSERTED_IDCOL

    END

END

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

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.

Upvotes: 3

KM.
KM.

Reputation: 103579

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

Upvotes: 5

Related Questions