title: AFTER DELETE Trigger fires off but does not Insert a record into an Audit table
body: <p>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]</p>
  
  <p>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: </p>
  
  <p>(0 rows affected)
  (1 row affected)</p>
  
  <p>At the same time, results for the UPDATE and INSERT actions look like this (considering a single record):</p>
  
  <p>(1 rows affected)
  (1 row affected)</p>
  
  <p>There are no other triggers defined on this table other than this one...</p>
  
  <pre><code>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
  </code></pre>