Sql-server – on deleting a row, the update logic is run also

sql servertrigger

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

Best Answer

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

Related Solutions

Sql-server – Passing info on who deleted record onto a Delete trigger

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) to triggers as well as back and forth between sub-proc / EXEC calls. And I have used it before for this exact same situation.

Context info is a VARBINARY(128)
Set via: SET CONTEXT_INFO
Get via: CONTEXT_INFO()

Test with the following to see how it works. Notice that I am converting to CHAR(128) before the CONVERT(VARBINARY(128), ... This is to force blank-padding to make it easier to convert back to VARCHAR when getting it out of CONTEXT_INFO() since VARBINARY(128) is right-padded with 0x00s.

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                          );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
       RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

Results:

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

PUTTING IT ALL TOGETHER:

The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.

The "Delete" stored procedure does:

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), @UserName)
                          );
SET CONTEXT_INFO @EncodedUser;

-- DELETE STUFF HERE

The audit trigger does:

-- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
   SELECT del.ID, COALESCE(
                     LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                     '<unknown>')
   FROM DELETED del;

Please note that, as @SeanGallardy pointed out in a comment, due to other procedures and/or ad hoc queries deleting records from this table, it is possible that either:
- CONTEXT_INFO has not been set and is still NULL:
  
  For this reason I have updated the above INSERT INTO AuditTable to use a COALESCE to default the value. Or, if you don't want a default and require a name, then you could do something similar to:
```
DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);

IF (@UserName IS NULL)
BEGIN
   ROLLBACK TRAN; -- cancel the DELETE operation
   RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
END;

-- use @UserName in the INSERT...SELECT
```
- CONTEXT_INFO has been set to a value that is not a valid UserName, and hence might exceed the size of the AuditTable.[UserWhoMadeChanges] field:
  
  For this reason I added a LEFT function to ensure that whatever is grabbed out of CONTEXT_INFO will not break the INSERT. As noted in the code, you just need to set the 50 to the actual size of the UserWhoMadeChanges field.

UPDATE FOR SQL SERVER 2016 AND NEWER

SQL Server 2016 added an improved version of this per-session memory: Session Context. The new Session Context is essentially a hash table of Key-Value pairs with the "Key" being of type sysname (i.e. NVARCHAR(128) ) and the "Value" being SQL_VARIANT. Meaning:

There is now a separation of values so less likely to conflict with other uses
You can store various types, no longer needing to worry about the odd behavior when getting the value back out via CONTEXT_INFO() (for details, please see my post: Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?)
You get a lot more space: 8000 bytes max per "Value", up to 256kb total across all keys (compared to the 128 bytes max of CONTEXT_INFO)

For details, please see the following documentation pages:

Sql-server – how to use trigger to make SQL Job’s step success or fail

If you are storing records in a permanent table, you can make use of a trigger below.

--to check status of inserted record  

create trigger triggername   
on tablename    
for insert     
as    
begin     
if exists(select 1 from inserted)    
--do your logic here    
end       

--to check status of deleted record    

create trigger triggername  
on tablename    
for delete
as    
begin   
if exists( select 1 from deleted)    
--do your logic here to start another process     
end

Just for your information, inserted and deleted tables can be used to know what records are inserted/deleted as the result of the trigger action, and in spite of how many rows are inserted or deleted, the trigger will fire only once per statement. Further, you can combine both of the above statements into one by just doing like below:

create trigger triggername    
on tablename    
for insert,delete    
as    
if exists(select 1 from inserted)    
begin   
--what you want to do when a record is inserted   
end      

if exists(select 1 from deleted)    
begin    
--what you want to do when a record is deleted    
end

Best Answer

Related Solutions

Sql-server – Passing info on who deleted record onto a Delete trigger

Sql-server – how to use trigger to make SQL Job’s step success or fail

Related Question