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
-sql
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
Asked by Joseph
(1 rep)
Nov 11, 2019, 12:19 PM
Last activity: Sep 18, 2025, 09:06 PM
Last activity: Sep 18, 2025, 09:06 PM