AFTER DELETE Trigger fires off but does not Insert a record into an Audit table

Question

I'm using SQL Server 2016. Below is the code that works for the UPDATE and INSERT actions. Meaning that after a record(s) is inserted or updated into MyDB.[dbo].[DocumentText] table a corresponding record(s) get inserted into the [AnotherDB].[dbo].[Audit]

But the same scenario is not working for the DELETE action on the same table. But I do see that the trigger is firing off when I'm deleting a record from MyDB.[dbo].[DocumentText] table. When deleting a single record the result for the DELETE action looks like:

(0 rows affected) (1 row affected)

At the same time, results for the UPDATE and INSERT actions look like this (considering a single record):

(1 rows affected) (1 row affected)

There are no other triggers defined on this table other than this one...

CREATE TRIGGER [dbo].[TR_DocumentText_UPDATE_INSERT_DELETE]
   ON MyDB.[dbo].[DocumentText] AFTER INSERT, DELETE, UPDATE
   NOT FOR REPLICATION 
AS 
BEGIN
            ;WITH CTE AS (
            SELECT ISNULL(ins.DocumentID, del.DocumentID) AS DocumentID,
                CASE 
                    WHEN ins.DocumentID IS NOT NULL AND ins.DocumentID  = del.DocumentID THEN 'UPDATE' 
                    WHEN ins.DocumentID IS NOT NULL AND del.DocumentID IS NULL THEN 'INSERT'
                    ELSE 'DELETE' END AS AuditType         
                FROM inserted ins 
                FULL OUTER JOIN deleted del 
                        ON ins.DocumentID = del.DocumentID
                        )

            ,AUDIT_CTE AS (
            SELECT          
                CTE.DocumentID AS PkId, 
                'ODRT' AS Code,
                CTE.AuditType,
                'TSRE' AS MainEntityCode,
                doc.OppId AS MainEntityPkId
            FROM [MyDB].[dbo].[DocumentText] t WITH (NOLOCK)
            INNER JOIN [MyDB].[dbo].[Document] doc WITH (NOLOCK)
                        ON t.DocumentID = doc.DocumentID
            INNER JOIN CTE 
                        ON CTE.DocumentID = t.DocumentID
                            WHERE doc.OppId IS NOT NULL
                        )

            INSERT [AnotherDB].[dbo].[Audit] (Code, PKID, AuditType, MainEntityCode, MainEntityPKID)
            SELECT
                Code,
                PKID, 
                AuditType,
                MainEntityCode,
                MainEntityPkId
            FROM AUDIT_CTE;

END

Uh, in the audit_cte, you're using an inner join between the first CTE (which in the case of a delete statement would grab the deleted documentID) and the original table (where the record is deleted). This would produce no results (as expected). I think what you want to do is remove FROM [MyDB].[dbo].[DocumentText] t WITH (NOLOCK) from the audit_cte and just join [MyDB].[dbo].[Document] with CTE... — ZLK
Thanks ZLK, you suggest as simple as logical. It solved the issue for the DELETE action. Thanks a lot. you can post this as the answer, so I could accept it. — enigma6205

enigma6205 enigma6205 · Accepted Answer · 2018-11-07T20:29:28

As suggested ZLK, I should just remove FROM [MyDB].[dbo].[DocumentText] t WITH (NOLOCK) and directly query as "FROM CTE...". That's it.

AFTER DELETE Trigger fires off but does not Insert a record into an Audit table

1 Answers