CODEWITHSUNDEEP
sql-server-2008triggers
yrahman
yrahman

Reputation: 950

SQL Server trigger is working for only single effected row

i have two table. a customer table and a customer log table. when any row updated inserted or deleted in customer. trigger should put its id and operation to log table. my trigger is just handling one row at a time. can any one please tell me how to cater multiple effected rows scenario

ALTER TRIGGER [dbo].[tr_customer_updated] ON [dbo].[Customers]
AFTER UPDATE,INSERT,DELETE
AS 
DECLARE @action as char(10);
DECLARE @customerId as varchar(max);
DECLARE @customerName as varchar(max);
SET @action = 'Inserted'; -- Set Action to Insert by default.
IF EXISTS(SELECT * FROM DELETED)
BEGIN
    SET @action = 
        CASE
            WHEN EXISTS(SELECT * FROM INSERTED) 
            THEN 'Updated' -- Set Action to Updated.
            ELSE 'Deleted' -- Set Action to Deleted.       
        END
        select @customerId = Id, @customerName = Name from DELETED;

END
ELSE 
BEGIN
    IF EXISTS(SELECT * FROM INSERTED) 
    BEGIN
        select @customerId = Id, @customerName = Name from INSERTED;
    END
END
insert into CustomerLogs (CustomerId, CustomerName, ActionPerformed, PerformedOn)
VALUES(@customerId, @customerName, @action, GETDATE())

Upvotes: 0

Views: 1112

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

ALTER TRIGGER [dbo].[tr_customer_updated] ON [dbo].[Customers]
AFTER UPDATE,INSERT,DELETE
AS 
DECLARE @action as char(10);
SET @action = 'Inserted'; -- Set Action to Insert by default.
IF EXISTS(SELECT * FROM DELETED)
BEGIN
    SET @action = 
        CASE
            WHEN EXISTS(SELECT * FROM INSERTED) 
            THEN 'Updated' -- Set Action to Updated.
            ELSE 'Deleted' -- Set Action to Deleted.       
        END
        insert into CustomerLogs (CustomerId, CustomerName, ActionPerformed, PerformedOn)
        select Id, Name, @action, GETDATE() from DELETED;

END
ELSE 
BEGIN
    IF EXISTS(SELECT * FROM INSERTED) 
    BEGIN
        insert into CustomerLogs (CustomerId, CustomerName, ActionPerformed, PerformedOn)
        select Id, Name, @action, GETDATE() from INSERTED;
    END
END

Upvotes: 2

Related Questions