title: Sql Server trigger triggers with empty inserted and deleted tables
body: <p>I have defined a trigger on a table that is triggered</p>
  
  <p><code>AFTER INSERT, DELETE, UPDATE</code></p>
  
  <p>There are cases where the trigger fires, with both <code>INSERTED</code> AND <code>DELETED</code> tables being empty. How can this be possible?</p>
  
  <p>For the records, that's the trigger</p>
  
  <pre><code>CREATE TRIGGER [dbo].[AuditUsersTrigger] ON [dbo].[Users]
  AFTER INSERT, DELETE, UPDATE
  AS 
  BEGIN
      SET NOCOUNT ON
  
  
      DECLARE @type nchar(1), @hasChanges bit
      SET @hasChanges = 1
      IF EXISTS (SELECT * FROM INSERTED)
          IF EXISTS (SELECT * FROM DELETED)
          BEGIN
              SELECT @type = 'U'
  
              IF EXISTS (
                  SELECT *
                  FROM INSERTED i
                  INNER JOIN DELETED d ON
                      i.Name = d.Name AND
                      i.Pwd = d.Pwd AND
                      ...
              ) SELECT @hasChanges = 0
          END
          ELSE
              SELECT @type = 'I'
      ELSE
          SELECT @type = 'D'
  
  
      IF @type = 'D' OR (@type = 'U' AND @hasChanges = 1)
      BEGIN
          INSERT AuditUsers (
              New, Id, Name, ...
          )
          SELECT
              0, Id, Name, ...
          FROM DELETED
  
          IF @type = 'D'
          BEGIN
              INSERT AuditUsers (New)
              SELECT 1
          END
      END
  
      IF @type = 'I' OR (@type = 'U' AND @hasChanges = 1)
      BEGIN
          IF @type = 'I'
          BEGIN
              INSERT AuditUsers (New)
              SELECT 0
          END
  
          INSERT AuditUsers (
              New, Id, Name, ...
          )
          SELECT
              0, Id, Name, ...
          FROM INSERTED
      END
  
  
      IF Trigger_Nestlevel() &lt; 2
      BEGIN
          DECLARE @clientId TABLE (id INT)
          DECLARE @clientCode NVARCHAR(50), @shopId INT;
  
          IF @type = 'I' OR @type = 'U'
          BEGIN
              SELECT @clientCode = ClientCode, @shopId = ShopId FROM INSERTED;
              INSERT INTO @clientId SELECT id FROM Clients WHERE code = @clientCode;
  
              IF NOT EXISTS (SELECT 1 FROM @clientId)
              BEGIN
                  INSERT Clients (name, code, active, shopId) OUTPUT INSERTED.id INTO @clientId
                  VALUES (@clientCode, @clientCode, 1, @shopId);
              END
  
  
              UPDATE Users SET ClientId = (SELECT TOP 1 id FROM @clientId) WHERE ClientCode = @clientCode;
          END
      END
  END
  </code></pre>