Earlier today, I realised that Microsoft has forgotten to include some keywords in the list of reserved keywords. Now, a wise developer will still take care to omit those names when naming tables – but a bored developer can have loads of fun exploring the effects!

The keywords I am referring to are inserted and deleted. Everyone who ever coded a trigger knows that they refer to the pseudo-tables that hold the before and after image of all rows affected by the triggering DML statement. But since they’re not reserved keywords, it’s perfectly legal to name a column “inserted”.

Of course, things get confusing when you name your table “inserted” and create a trigger on that table – how is SQL Server supposed to know what you mean when you write “inserted”? As an example, look at the following code. Quiz question: try to predict the results before executing the code; let me know if your prediction was right.

CREATE TABLE inserted (a int PRIMARY KEY);

INSERT INTO inserted VALUES(1);

go

CREATE TRIGGER tst

ON inserted AFTER INSERT

AS SELECT * FROM inserted;

go

INSERT INTO inserted VALUES(2);

SELECT * FROM inserted;

DROP TABLE inserted;

go

My prediction was an error message because of the ambiguous table name. Boy was I wrong!

The results of the code above prove that SQL Server will use the pseudo-table if I write “inserted” in the inside of a trigger, even if there is a table with the same name. So what do I do if I need to refer to the rows in that table from a trigger?

Actually, that’s a lot easier than it sounds – just follow long-standing best practice: prefix all table names with owner (SQL Server 2000) or schema (SQL Server 2005). Change the example above to the one below to see how both the “real” table “inserted” and the pseudo-table can be used within the trigger:

CREATE TABLE inserted (a int PRIMARY KEY);

INSERT INTO inserted VALUES(1);

go

CREATE TRIGGER tst ON inserted AFTER INSERT

AS

SELECT * FROM inserted;

SELECT * FROM dbo.inserted;

go

INSERT INTO inserted VALUES(2);

SELECT * FROM inserted;

DROP TABLE inserted;

go

Intriguingly, you can even join inserted and dbo.inserted in a single query and refer to columns from both tables, as long as you keep repeating the dbo qualifier each time you refer to a column from the real table. How’s that for hard to grasp coding, huh?

However, things get even more interesting if we leave SQL Server 2000 behind and explore one of the new features SQL Server 2005 has to offer: the OUTPUT clause for INSERT, UPDATE and DELETE statements. Since the SQL Server development team decided to overload the (still unreserved) keywords inserted and deleted with a second meaning, things start to get really interesting here!

The real problem in SQL Server 2005 (and the exact issue that caused me to start investigating this issue) is that you don’t even need to choose your table names badly to run into trouble. Regardless of table name, you are challenged by the ambiguity of the inserted keyword as soon as you have to use the OUTPUT clause within a trigger. (In fact, exactly that happened to me at work yesterday; this was what prompted me to do some further investigation today). Here’s a simplified example – anyone care to take a bet on the outcome?

CREATE TABLE testtab (pk int NOT NULL PRIMARY KEY,

                      a char(1) NOT NULL,

                      b char(1) NOT NULL);

go

CREATE TRIGGER testtrig

ON testtab AFTER INSERT

AS UPDATE     testtab

   SET        a = inserted.b,

              b = inserted.a

   OUTPUT     inserted.a, inserted.b

   FROM       inserted

   INNER JOIN testtab

         ON   testtab.pk = inserted.pk;

go

INSERT INTO testtab (pk, a, b) VALUES (1, ‘a’, ‘b’);

go

DROP TABLE testtab;

go

Running the code above shows that in this OUTPUT clause, the inserted keyword is taken to refer to the new version of the rows affected by the UPDATE statement, not to the trigger’s pseudo-table that holds the newly inserted rows. Now, what should I do if I actually wanted to output data from the trigger’s pseudo-table here? I can’t use dbo.inserted here, since that would refer to a real table. And yet I should be able to refer to the pseudo-table, as the documentation of the OUTPUT clause clearly states that tables used in the FROM clause can also be used in the OUTPUT clause.

The only solution I could find is to use an alias in the FROM clause, so that we can use the alias to refer to the inserted pseudo-table in the OUTPUT clause:

CREATE TABLE testtab (pk int NOT NULL PRIMARY KEY,

                      a char(1) NOT NULL,

                      b char(1) NOT NULL);

go

CREATE TRIGGER testtrig

ON testtab AFTER INSERT

AS UPDATE     testtab

   SET        a = i.b,

              b = i.a

   OUTPUT     i.a, i.b

   FROM       inserted AS i

   INNER JOIN testtab

         ON   testtab.pk = i.pk;

go

INSERT INTO testtab (pk, a, b) VALUES (1, ‘a’, ‘b’);

go

DROP TABLE testtab;

go

Now to get really overboard with ambiguity, I decided to create an example that refers to three versions of inserted on a single line – the new rows in the UPDATE statement, the rows in the trigger’s pseudo-table and the rows in the permanent table named “inserted”. Please, don’t ever try to do this at home, and even less at work – unless you are writing a blog or if you want to see your code on The Daily WTF.

CREATE TABLE inserted (pk int NOT NULL PRIMARY KEY,

                       a char(1) NOT NULL,

                       b char(1) NOT NULL,

                       c int NOT NULL);

CREATE TABLE other (pk int NOT NULL PRIMARY KEY,

                    a char(1) NOT NULL,

                    b char(1) NOT NULL,

                    c int NOT NULL)

INSERT INTO other (pk, a, b, c) VALUES (1, ‘a’, ‘b’, 1)

go

CREATE TRIGGER ugly

ON inserted AFTER INSERT

AS UPDATE     dbo.inserted

   SET        c = 5;

   UPDATE     other

   SET        a = i.b,

              b = i.a,

              c = dbo.inserted.c + 1

   OUTPUT     inserted.a, i.b, dbo.inserted.c

   FROM       other

   INNER JOIN inserted AS i

         ON   i.pk = other.pk

   INNER JOIN dbo.inserted

         ON   inserted.pk = i.pk;

go

INSERT INTO inserted (pk, a, b, c) VALUES (1, ‘a’, ‘b’, 1);

go

DROP TABLE inserted;

DROP TABLE other;

go