CODEWITHSUNDEEP
sql-servertriggers
Bethuel Mhlongo
Bethuel Mhlongo

Reputation: 47

SQL Server After Update Trigger Not firing

Kindly help, I have been trying to create an update trigger to insert data into a specific table once it's updated with a certain value. "Accepted". for somewhat reason the trigger is not firing

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TRGIU_CLEARING_SAMPLE_RESULT]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[TRGIU_CLEARING_SAMPLE_RESULT]
GO

CREATE TRIGGER [dbo].[TRGIU_CLEARING_SAMPLE_RESULT] ON [dbo].[CLEARING_BATCH]               
AFTER INSERT                
AS                

IF UPDATE(QM_STATUS)                
BEGIN                
  Declare @QM_Status Varchar(12),                
          @QM_Status_Old Varchar(12),                
          @Submission_Pk Uniqueidentifier            
                
  SELECT @QM_Status = I.QM_STATUS,                
         @QM_Status_old = D.QM_STATUS,                
         @Submission_Pk = I.PK               
         --@Lab_used = LE.ID                            
    FROM CLEARING_BATCH CB                
    LEFT JOIN SAMPLE_RESULT SR ON CB.PK = SR.PPK                 
    JOIN INSERTED I ON SR.PK = I.PPK                 
    JOIN DELETED D ON I.PK = D.PK 

             
  IF(@QM_Status = 'Accepted' and (@QM_Status <> @QM_Status_old or @QM_Status_old is null))                
  
  
  begin

  INSERT INTO BATCH(PPK,BATCH_ID)

  SELECT CB.PK,CB.BATCH_ID
  FROM LABORATORY L
  JOIN CLEARING_BATCH CB ON L.PK = CB.PPK
  JOIN CLEARING_SAMPLE_RESULT CSR ON CB.PK = CSR.PPK
  LEFT JOIN BATCH B ON CB.PK = B.PPK
  WHERE B.PPK IS NULL
 

  UPDATE BATCH
  SET BATCH_ID = CB.BATCH_ID

  FROM BATCH B
  JOIN CLEARING_BATCH CB ON B.PPK = CB.PPK

  END
  end
  GO

Upvotes: 1

Views: 1862

Answers (1)

marc_s
marc_s

Reputation: 754388

You have an AFTER INSERT trigger 

CREATE TRIGGER [dbo].[TRGIU_CLEARING_SAMPLE_RESULT] 
ON [dbo].[CLEARING_BATCH]               
AFTER INSERT                
AS

which will fire after any INSERT statement that inserts new rows - this will NOT be fired when you UPDATE something!

If you want to capture the UPDATE operations, you need an AFTER UPDATE trigger instead:

CREATE TRIGGER [dbo].[TRGIU_CLEARING_SAMPLE_RESULT] 
ON [dbo].[CLEARING_BATCH]
AFTER UPDATE
AS
   ....

Upvotes: 1

Related Questions