CODEWITHSUNDEEP
sql-servertriggers
Mych
Mych

Reputation: 2553

How to modify my trigger to cope with bulk update

Inherited some work and have created a trigger. From what I've read I have done stuff I shouldn't have (declared variable etc) but the trigger works for update that affect one row only. If the update affects more than one row it fails.

Basically the trigger stores the 'deleted old/previous' values of specific fields from tblMachine into tblAudit.

My Trigger is...

    USE [MyDB]
GO
/****** Object:  Trigger [dbo].[tr_StatusChange_tblMachine]    Script Date: 12/07/2017 09:00:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_StatusChange_tblMachine] ON [dbo].[tblMachine]
    AFTER UPDATE
AS BEGIN 

    SET XACT_ABORT ON;
    SET NOCOUNT ON;
    SET ROWCOUNT 0;

    DECLARE @NewStatus nvarchar(20);
    DECLARE @OldStatus nvarchar(20);
    DECLARE @ChangeMade nvarchar(50);

    IF UPDATE (MachineStatus)
        BEGIN           

            SET @NewStatus = (SELECT i.MachineStatus FROM inserted i FULL OUTER JOIN deleted d ON i.ID = d.ID);
            SET @OldStatus = (SELECT d.MachineStatus FROM deleted d FULL OUTER JOIN inserted i ON i.ID = d.ID);
            SET @ChangeMade = CONCAT ( @OldStatus, '-', @NewStatus );

            -- Status Changed
            IF @OldStatus <> @NewStatus
                BEGIN

                    INSERT INTO dbo.tblAudit (InsertedDate, Month, Year, SupportTeam, MachineName, MachineStatus, 
                    DateBuilt, DismantledDate, ServiceCheck, ServiceCheckBit, ChangeMade, ChangedBy)
                    SELECT getdate() AS InsertedDate ,(datepart(month,getdate())) AS Month, (datepart(Year,getdate())) AS Year, 
                    i.SupportTeam, i.MachineName, i.MachineStatus, i.DateBuilt, i.DismantledDate, 
                    CASE i.ServiceCheck WHEN 0 THEN 'No' ELSE 'Yes' END AS ServiceCheck, i.ServiceCheck, @ChangeMade, i.ChangedBy
                    FROM inserted i JOIN deleted d
                    ON i.MachineName = d.MachineName
                    WHERE i.SupportTeamID <> 'mbe';

                END;

            -- NO STATUS CHANGE
            IF @OldStatus = @NewStatus
                BEGIN
                    -- NO STATUS CHANGE
                    -- Service Check may have changed!

                    -- INSERT INTO AUDIT IF ServiceCheck has changed AND MachineStatus is NOT DecommissionedX or Dismantled.

                    INSERT INTO dbo.tblAudit (InsertedDate, Month, Year, SupportTeam, MachineName, MachineStatus, 
                    DateBuilt, DismantledDate, ServiceCheck, ServiceCheckBit, ChangeMade, ChangedBy)
                    SELECT getdate() AS InsertedDate, (datepart(month,getdate())) AS Month, (datepart(Year,getdate())) AS Year, 
                    i.SupportTeam, i.MachineName, i.MachineStatus, i.DateBuilt, (SELECT getdate() AS DismantledDate), 
                    CASE i.ServiceCheck WHEN 0 THEN 'No' ELSE 'Yes' END AS ServiceCheck, i.ServiceCheck, 'SC Changed - Status Not Changed', i.ChangedBy
                    FROM inserted i JOIN deleted d
                    ON i.MachineName = d.MachineName
                    WHERE i.SupportTeamID <> 'mbe' And i.ServiceCheck <> d.ServiceCheck 
                    AND (i.MachineStatus <> 'DecommissionedX' AND i.MachineStatus <> 'Dismantled');

                END;

        END;

END

From my searches I have discovered that Triggers fire once per statement and not per row affected. I have read that a trigger can be modified to check how many rows are being updated and then use this information to do actions based on each row. Unfortunately I have not found a way to do this that I understand.

My forte is web front-end coding and my SQLServer coding is limited. The code needs to be in SQL and not the front end as there are some other tools that update these records apart from a web front-end.

Any help in adapting this trigger so that it also copes with multiple update in one statement would be appreciated.

Upvotes: 0

Views: 167

Answers (2)

Ben Thul
Ben Thul

Reputation: 32687

I think this does it. The trick is to just use MachineStatus from your inserted and deleted tables directly in the insert into your audit table rather than trying to calculate a universal old and new status up front.

USE [MyDB]
GO
/****** Object:  Trigger [dbo].[tr_StatusChange_tblMachine]    Script Date: 12/07/2017 09:00:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_StatusChange_tblMachine] ON [dbo].[tblMachine]
    AFTER UPDATE
AS BEGIN 

    SET XACT_ABORT ON;
    SET NOCOUNT ON;
    SET ROWCOUNT 0;

    IF UPDATE (MachineStatus)
        BEGIN           

            INSERT INTO dbo.tblAudit (InsertedDate, Month, Year, SupportTeam, MachineName, MachineStatus, 
            DateBuilt, DismantledDate, ServiceCheck, ServiceCheckBit, ChangeMade, ChangedBy)
            SELECT getdate() AS InsertedDate ,(datepart(month,getdate())) AS Month, (datepart(Year,getdate())) AS Year, 
            i.SupportTeam, i.MachineName, i.MachineStatus, i.DateBuilt, i.DismantledDate, 
            CASE i.ServiceCheck WHEN 0 THEN 'No' ELSE 'Yes' END AS ServiceCheck, i.ServiceCheck, CONCAT(d.MachineStatus, '-', i.MachineStatus), i.ChangedBy
            FROM inserted i JOIN deleted d
            ON i.MachineName = d.MachineName
            WHERE i.SupportTeamID <> 'mbe'
            -- Status Changed
            AND i.MachineStatus <> d.MachineStatus;


            -- NO STATUS CHANGE
            -- Service Check may have changed!

            -- INSERT INTO AUDIT IF ServiceCheck has changed AND MachineStatus is NOT DecommissionedX or Dismantled.

            INSERT INTO dbo.tblAudit (InsertedDate, Month, Year, SupportTeam, MachineName, MachineStatus, 
            DateBuilt, DismantledDate, ServiceCheck, ServiceCheckBit, ChangeMade, ChangedBy)
            SELECT getdate() AS InsertedDate, (datepart(month,getdate())) AS Month, (datepart(Year,getdate())) AS Year, 
            i.SupportTeam, i.MachineName, i.MachineStatus, i.DateBuilt, (SELECT getdate() AS DismantledDate), 
            CASE i.ServiceCheck WHEN 0 THEN 'No' ELSE 'Yes' END AS ServiceCheck, i.ServiceCheck, 'SC Changed - Status Not Changed', i.ChangedBy
            FROM inserted i JOIN deleted d
            ON i.MachineName = d.MachineName
            WHERE i.SupportTeamID <> 'mbe' And i.ServiceCheck <> d.ServiceCheck 
            AND (i.MachineStatus <> 'DecommissionedX' AND i.MachineStatus <> 'Dismantled')
            -- NO STATUS CHANGE
            AND (i.MachineStatus = d.MachineStatus);


        END;

END

Upvotes: 0

JMabee
JMabee

Reputation: 2300

Well this may not be the BEST option, but one way to solve this would be to use a temp table to store which machine changed and then join to the inserted or deleted table. Such as:

ALTER TRIGGER [dbo].[tr_StatusChange_tblMachine] ON [dbo].[tblMachine]
AFTER UPDATE
AS BEGIN 

SET XACT_ABORT ON;
SET NOCOUNT ON;
SET ROWCOUNT 0;

CREATE TABLE #tmp(MachineName nvarchar(50), NewStatus nvarchar(20) NULL, OldStatus nvarchar(20) NULL)

INSERT INTO #tmp(MachineName, NewStatus) 
   SELECT MachineName,MachineStatus from inserted

UPDATE #tmp SET OldStatus = d.MachineStatus
 From deleted d 
 INNER JOIN #tmp t on t.MachineName = d.MachineName

---You could delete from #tmp where OldStatus is null to ensure this was an update


        -- Status Changed
                INSERT INTO dbo.tblAudit (InsertedDate, Month, Year, SupportTeam, MachineName, MachineStatus, 
                DateBuilt, DismantledDate, ServiceCheck, ServiceCheckBit, ChangeMade, ChangedBy)
                SELECT getdate() AS InsertedDate ,(datepart(month,getdate())) AS Month, (datepart(Year,getdate())) AS Year, 
                i.SupportTeam, i.MachineName, i.MachineStatus, i.DateBuilt, i.DismantledDate, 
                CASE i.ServiceCheck WHEN 0 THEN 'No' ELSE 'Yes' END AS ServiceCheck, i.ServiceCheck, @ChangeMade, i.ChangedBy
                FROM inserted i JOIN #tmp t
                ON i.MachineName = t.MachineName and t.NewStatus <> t.OldStatus
                WHERE i.SupportTeamID <> 'mbe' ;

                -- NO STATUS CHANGE
                -- Service Check may have changed!

                -- INSERT INTO AUDIT IF ServiceCheck has changed AND MachineStatus is NOT DecommissionedX or Dismantled.

                INSERT INTO dbo.tblAudit (InsertedDate, Month, Year, SupportTeam, MachineName, MachineStatus, 
                DateBuilt, DismantledDate, ServiceCheck, ServiceCheckBit, ChangeMade, ChangedBy)
                SELECT getdate() AS InsertedDate, (datepart(month,getdate())) AS Month, (datepart(Year,getdate())) AS Year, 
                i.SupportTeam, i.MachineName, i.MachineStatus, i.DateBuilt, (SELECT getdate() AS DismantledDate), 
                CASE i.ServiceCheck WHEN 0 THEN 'No' ELSE 'Yes' END AS ServiceCheck, i.ServiceCheck, 'SC Changed - Status Not Changed', i.ChangedBy
                FROM inserted i JOIN #tmp t
                ON i.MachineName = t.MachineName and t.OldStatus = t.NewStatus
                WHERE i.SupportTeamID <> 'mbe' And i.ServiceCheck <> d.ServiceCheck 
                AND (i.MachineStatus <> 'DecommissionedX' AND i.MachineStatus <> 'Dismantled');

 END

Again you will need to check the syntax as I am just modifying your code, but this is the gist of the idea.

Upvotes: 1

Related Questions