Member Avatar for sonia sardana

I was asked Suppose there is table A in DB,Suppose when we update that table,whose are two tables(Magic tables) that are updated other than table A?

  • 1 Contributor
  • 1 Reply
  • 264 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by sonia sardana

Dani AI

Generated

Answer to : the two "magic" tables are the pseudo-tables inserted and deleted that SQL Server exposes inside DML triggers. They are not real tables on disk — they exist only during the trigger execution and contain the rows affected by the statement.

Behavior summary:

  • INSERT: inserted contains new rows; deleted is empty.
  • DELETE: deleted contains old rows; inserted is empty.
  • UPDATE: deleted contains the prior versions; inserted contains the new versions (one-to-one for each affected row).
    These pseudo-tables are available in both AFTER and INSTEAD OF triggers, and MERGE statements populate them according to the action performed.

Example (audit inside an AFTER UPDATE trigger):

CREATE TRIGGER trg_TableA_Audit
ON TableA
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO TableA_Audit (Id, OldValue, NewValue, ChangedBy, ChangedAt)
  SELECT d.Id, d.SomeCol, i.SomeCol, SUSER_SNAME(), GETUTCDATE()
  FROM deleted d
  JOIN inserted i ON d.Id = i.Id;
END;

Important notes:

  • Triggers are statement-level, not row-level — inserted/deleted can contain many rows. Always write set-based logic (no assumptions of a single row).
  • Keep triggers small and efficient; large updates can make triggers a performance bottleneck.
  • For change capture alternatives consider the OUTPUT clause, Change Data Capture / Change Tracking, or system-versioned temporal tables for built-in history without custom triggers.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.