CODEWITHSUNDEEP
sql-servertriggers
Alex
Alex

Reputation: 4473

SQL Server trigger does not recognize inserted row

I have simple SQL Server trigger and for some reason it does not recognize INSERTED row.

Here is code:

-- code
DROP TABLE a;
GO

CREATE TABLE a 
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    v INT
)
GO

DROP TABLE a_audit;

CREATE TABLE a_audit 
(
    id INT,
    v INT,
    [updated_at] [DATETIME] NOT NULL DEFAULT GETDATE()
)
GO

CREATE TRIGGER [dbo].[trg_a]
ON [dbo].[a]
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
BEGIN
    SET NOCOUNT ON;
    PRINT 'start';

    DECLARE @xmltmp xml = (SELECT * FROM inserted FOR XML AUTO);
    PRINT CONVERT(NVARCHAR(MAX), @xmltmp);

    -- INSERT INTO a_audit (id, v) VALUES (inserted.id, inserted.v);
END;
GO

INSERT INTO a (v) 
VALUES (1);

WAITFOR DELAY '00:00:01.11';

INSERT INTO a (v) 
VALUES (2);
GO

SELECT * FROM a;

PRINT 'done'

It produces this output

start
<inserted id="1" v="1"/>

(1 row affected)
start
<inserted id="2" v="2"/>

(1 row affected)

(2 rows affected)
done

So I see that INSERTED row does exist.

However, if I remove comment on insert statements the output is this:

Msg 4104, Level 16, State 1, Procedure trg_a, Line 14 [Batch Start Line 16]
The multi-part identifier "inserted.id" could not be bound.

Msg 4104, Level 16, State 1, Procedure trg_a, Line 14 [Batch Start Line 16]
The multi-part identifier "inserted.v" could not be bound.

What is wrong?

Upvotes: 0

Views: 657

Answers (1)

Thom A
Thom A

Reputation: 95564

inserted is a table, not a function. To INSERT from another table you need to use a INSERT INTO... SELECT ... FROM statement:

INSERT INTO a_audit (id,v)
SELECT id,
       v
FROM inserted;

You can't reference a table's columns unless you use a FROM. For example just running the below would give the error below:

SELECT a_audit.id, a.audit.v;

The multi-part identifier "a_audit.id" could not be bound.

You would have to a SELECT...FROM:

SELECT id, v
FROM a_audit;

Upvotes: 3

Related Questions