0

I am using a after insert, update delete trigger.

When I insert a record in my application called as clarity, it inserts a row in the test table with the value as 'Insert'

When I update the record in my application, it insert a row in my test table with the value as 'Update'

When I delete the record in my application, it adds two rows in my test table with the value as 'Update' and 'Delete' in that order.

Why would deleting a row call the update logic of the trigger.

My code is as below

USE [claritydb]
GO
/****** Object:  Trigger [niku].[TRIG_STATUSRPT]    Script Date: 11/11/2019 11:04:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @STATUSID INT,
            @PROJECTID INT,
            @SCHEDULE_STATUS INT,
            @SCOPE_STATUS INT,
            @EFT_STATUS INT,
            @OVERALL_STATUS INT,
            @ACTION_TYPE VARCHAR(50),
            @MAXSTATUSIDVAR INT,
            @DelCount int,
            @InsCount int,

   SELECT @InsCount = Count(*) FROM INSERTED
   SELECT @DelCount = Count(*) FROM DELETED

---- Get data from inserted/ updated
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS 
           FROM inserted
 ---- Get data from deleted
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS 
           FROM deleted
SELECT TOP 1 @MAXSTATUSIDVAR= ID FROM ODF_CA_COP_PRJ_STATUSRPT op where op.ODF_PARENT_ID = @PROJECTID  ORDER BY ID DESC

   If @InsCount > 0 and @DelCount = 0
   Begin
      -- At least 1 row inserted. Your Insert Trigger logic here
        INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Insert',GETDATE(),@MAXSTATUSIDVAR)

   End
   Else If @DelCount > 0 and @InsCount = 0
   Begin
      -- at least 1 row deleted. Your Delete Trigger logic here 
       INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Delete',GETDATE(),@MAXSTATUSIDVAR)
   End 
   Else If @DelCount > 0 and @InsCount > 0
   Begin
      -- old row deleted, new row inserted; both indicates an update.
      -- your update logic here.  
              INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Update',GETDATE(),@MAXSTATUSIDVAR)
   End 
END
  • sorry, how do I format this code – Joseph Nov 11 '19 at 12:25
  • you can begin a large code section by 3 backticks ` ...and end it the same 3x ` – eagle275 Nov 11 '19 at 12:41
  • Thanks all, There is no other trigger on that table, hence it is baffling as to why it it run two times for an update. – Joseph Nov 11 '19 at 13:11
  • You say "delete the record in my application". Does that mean that you know what SQL is sent to SQL Server? Perhaps your app is weird and submits both an UPDATE and a DELETE? (I agree that the trigger should be re-factored to handle multi-row modifications, btw.) – Tibor Karaszi Nov 11 '19 at 18:39
0

Instead of diagnosing your update/delete logic issue, I think it's better to just re-write your trigger. The biggest problem with your trigger is that it's not set based, so that any attempt to modify multiple records at a time will fail, or more accurately give you odd results. I suspect that this is at least part of the problem you are experiencing with it.

Please take a look at the one below. It should work for you, but I apologize if I got some column names incorrect. The CTE_Details at the top fuses the INSERTED and DELETED tables together and will properly return the Action taken based on whether it's an insert, update or delete. It then inserts that into your reporting table.

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID
            , I.ODF_PARENT_ID
            , I.COP_SCHEDULE_STATUS
            , I.COP_SCOPE_STATUS
            , I.COP_COST_EFT_STATUS
            , ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELTED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID
            , D.ODF_PARENT_ID
            , D.COP_SCHEDULE_STATUS
            , D.COP_SCOPE_STATUS
            , D.COP_COST_EFT_STATUS
            , ActionTaken = 'Delete'
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
    SELECT T.ID AS STATUSID
        , T.ODF_PARENT_ID AS PROJECTID
        , T.COP_SCHEDULE_STATUS AS SCHEDULE_STATUS
        , T.COP_SCOPE_STATUS AS SCOPE_STATUS
        , T.COP_COST_EFT_STATUS AS EFT_STATUS
        , T.ActionTaken
        , GETDATE() AS CREATED_DATE
        , OP.ID AS MAXSTATUSIDVAR
    FROM CTE_Details AS T
        OUTER APPLY (   SELECT TOP (1) M.ID 
                        FROM ODF_CA_COP_PRJ_STATUSRPT AS M
                        WHERE M.ODF_PARENT_ID = T.ODF_PARENT-ID
                        ) AS OP

END

** EDIT ** - I fixed a bug in my example code for the Insert action path, it wouldn't simulate the error you are seeing.

I have an example here below that shows it working correctly. I think you have something else going on. If you run the example code below you should see four entries. 2 inserts, 1 update and 1 delete.

DROP TABLE IF EXISTS dbo.TestMe;
DROP TABLE IF EXISTS dbo.TestMeReport;

CREATE TABLE dbo.TestMe
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestValue VARCHAR(100) NULL
    )

CREATE TABLE dbo.TestMeReport
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestMeID INT NOT NULL
    , TestValue VARCHAR(100) NULL
    , ActionTaken VARCHAR(20) NULL
    )

GO

CREATE TRIGGER trgTestMe ON dbo.TestMe
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID, I.TestValue, ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELETED AS D ON D.ID = I.ID 
        UNION ALL
        SELECT D.ID, D.TestValue, 'Delete' AS ActionTaken
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO dbo.TestMeReport 
    (TestMeID, TestValue, ActionTaken)
    SELECT T.ID, T.TestValue, T.ActionTaken
    FROM CTE_Details AS T

END

GO

TRUNCATE TABLE dbo.TestMe
TRUNCATE TABLE dbo.TestMeReport 

INSERT INTO dbo.TestMe (TestValue)
VALUES ('Test1')
    , ('Test2')

UPDATE dbo.TestMe 
SET TestValue = 'Test3' WHERE TestValue = 'Test1'

DELETE FROM dbo.TestMe WHERE TestValue = 'Test2'

SELECT * FROM dbo.TestMeReport 
  • Hi Jonathan, Thanks for that, but it still does a update when it is deleting a record. Where can I attach the screen shot for this? – Joseph Nov 11 '19 at 18:41
  • Thanks everyone, much appreciated. At the moment I don't know what is causing it. Jonathan, I tried your test tables and its working fine, just not in my application. will keep you all posted. Thanks everyone once again. – Joseph Nov 13 '19 at 12:36
  • Joseph, is it possible that other fields are being modified by the application before being deleted? I'm not sure if there are other fields on the table or not, but that could be one possibility. – Jonathan Fite Nov 13 '19 at 12:56
-1

Hm looking at your trigger code it looks like your trigger fires 2 times - because in a single run it can only deliver 1 result (IF .. then "INSERT" ELSE IF .. then "DELETE" ELSE IF.. then "UPDATE" )

Leaves the small problem to find out, why your trigger is fired 2 times with update first

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.