AFTER INSERT Trigger not firing

Question

I have the below trigger code, which is working for UPDATE, but not INSERT

(we get no errors)

CREATE TRIGGER [dbo].[tr_ClientHistoryTUPEEmployee] ON [dbo].t_HR_TUPEEmployee] 
AFTER INSERT, UPDATE
AS

DECLARE @Username int, @Inserted bit, @Deleted bit
SELECT @Inserted = 0, @Deleted = 0

DECLARE @fieldId int
SELECT @fieldId = FieldID  FROM t_ClientHistoryFieldConstants WHERE Descn = 'TUPE Start Date'
IF @fieldId IS NULL
    SET @fieldId = 9999  -- Improper value if field id not found

IF EXISTS ( SELECT TOP 1 1 INSERTED )
    SET @Inserted = 1           

IF EXISTS ( SELECT TOP 1 1 DELETED )
    SET @Deleted = 1            

--Get username
IF CHARINDEX('_',SUSER_SNAME()) = 0
    BEGIN
        SET @Username = CAST(SUSER_SID(SUSER_SNAME()) AS int)
    END
ELSE
    BEGIN
        SET @Username = SUBSTRING(SUSER_SNAME(),1,CHARINDEX('_',SUSER_SNAME()) - 1)
    END

IF ( @Username = 1 and SUSER_SNAME()='sa' )
        SET @Username = -2


IF ( @Inserted = 1 and @Deleted = 0 ) -- only insert
BEGIN
    INSERT  t_ClientHistory (ClientID, FieldID, OldValue, NewValue, ChangeDate, ChangedBy)
    SELECT  ClientID, @fieldId , '', convert(varchar,TUPEStartDate,103) , GetDate(), @Username 
    FROM INSERTED
END
ELSE IF ( @Inserted = 1 and @Deleted = 1 ) -- update
BEGIN
    INSERT  t_ClientHistory (ClientID, FieldID, OldValue, NewValue, ChangeDate, ChangedBy)
    SELECT  DEL.ClientID, @fieldId , IsNull(convert(varchar,DEL.TUPEStartDate,103),'(No Start Date)'), 
            IsNull(convert(varchar,INS.TUPEStartDate,103),'(No Start Date)'), GetDate(), @Username 
    FROM    DELETED DEL
            INNER JOIN INSERTED INS ON ( INS.TUPEID = DEL.TUPEID )
    WHERE   IsNull( INS.TUPEStartDate,'1900-01-01') != IsNull( DEL.TUPEStartDate,'1900-01-01')  
END

what could I have done here - it compiles ok...no errors

Answer 1

your delete will be always true

IF EXISTS ( SELECT TOP 1 1 DELETED )
    SET @Deleted = 1  

so this wont work

IF ( @Inserted = 1 and @Deleted = 0 ) -- only insert

You could use return statements like below

 --check for updated
    if exists(select 1 from inserted )  and exists (select from deleted)
    begin

    return;
    end

--check for inserted

if exists(select 1 from inserted) 
begin

return;
end

--check for deleted
if exists(select 1 from deleted) 
begin

return;
end