Basic T-SQL trigger to populate an audit table

Question

Having read this page I having built a couple of tables and a trigger. The idea is that when an INSERT, UPDATE or DELETE is performed on the first table Matt the data operated upon will be inserted into the second, audit, table MattAudit.

The trigger must be failing and I don't know why; The evidence is that there is no entry made in the audit table, though the CREATE TRIGGER and subsequent ALTER TRIGGER statements complete successfully.

Main table Matt:

CREATE TABLE [dbo].[Matt](
    [MattID] [int] IDENTITY(1,1) NOT NULL,
    [Text] [nchar](10) NULL,
 CONSTRAINT [PK_Matt] PRIMARY KEY CLUSTERED 
(
    [MattID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Audit table MattAudit:

CREATE TABLE [dbo].[MattAudit](
    [MattAuditID] [int] IDENTITY(1,1) NOT NULL,
    [MattID] [int] NOT NULL,
    [Text] [nchar](10) NULL,
    [Action] [int] NOT NULL,
    [InsertedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_MattAudit] PRIMARY KEY CLUSTERED 
(
    [MattAuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Trigger on Matt:

ALTER TRIGGER TrgMattAudit ON Matt
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;

    INSERT INTO [dbo].[MattAudit]
        ( MattID, [Text], [Action], InsertedDate )
    SELECT
        ISNULL(i.MattID, d.MattID) as MattID,
        ISNULL(i.Text, d.Text) as Text,
        CASE ISNULL(i.MattID,0) WHEN 0 THEN 0 ELSE 1 END
        +
        CASE ISNULL(d.MattID,0) WHEN 0 THEN 0 ELSE -1 END
        as [Action],
        GETDATE() as InsertedDate
    FROM
        inserted i
        INNER JOIN deleted d ON i.MattID = d.MattID;

END

The following insert statement will insert rows into the Matt table but nothing appears in the MattAudit table.

INSERT INTO Matt ([Text]) VALUES ('Test4')

What am I missing or getting wrong in the trigger?

Answer 1

I think the problem is because of this:

FROM
    inserted i
    INNER JOIN deleted d ON i.MattID = d.MattID;

• When inserting you only have records in INSERTED.
• When you delete records you only have records in DELETED.
• When you update records you will have records in both INSERTED (new value) and DELETED (old value).

Joining the two will always result in 0 rows for INSERT or DELETE, because when doing an INSERT you will have 1 or more rows in INSERTED, but 0 rows in DELETED. And vice versa for the DELETE statement.

A suggestion I would make is to split the single trigger into a trigger for each situation (INSERT, UPDATE and DELETE) and have a single query in each of your new triggers.

A small caveat is that an AFTER UPDATE trigger will add rows in both INSERTED and DELETED table.

The value in INSERTED will be the value which was put in place, and the value in DELETED will be the old value, before the UPDATE query ran.

Answer 2

I think the error you are doing there is to do an inner join between inserted and deleted tables. As never should be same MattId in the 2 tables. I mean, you should do outer joins like left or right (Or load variables in 2 different selects instead of doing 1). Try that, hope you understood me!