Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

Question

I recently upgraded from SQL Server 2005 to 2012. During validation however, a bug was discovered.

A certain trigger was coded as follows:

CREATE TRIGGER [dbo].[trigger] on [dbo].[foo]
FOR UPDATE, UPDATE
AS
    UPDATE foobar
    SET datetime = GetDate()
    FROM bar
    WHERE foobar.id = bar.id
GO

I can safely execute this (oddly) on SQL Server 2005.

However on SQL Server 2012 it throws (what I would expect) a syntax error.

Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

Why does this not throw a syntax error on SQL Server 2005? My google-fu on this has failed me.

Why does this seemingly work on SQL Server 2005?

score 5 · Answer 1 · edited Apr 13 '17 at 12:42

John is right that the updated syntax error message you get:

Msg 1034, Level 15, State 1, Procedure trigger
Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.

This is absolutely because the trigger is specified as FOR UPDATE, UPDATE. This syntax was allowed in older compatibility levels but not in modern ones.

In fact, I have tested this and the syntax was allowed in 80 compatibility mode (SQL Server 2000), but not in 90+. I've updated my canonical answer about compat level 80.

(As for why it's not a documented breaking change, well, shrug. You'll need to approach Microsoft directly if you want a meaningful answer to that one.)

As for the trigger itself, here's how I would write this:

UPDATE t
  SET t.datetime = GETDATE()
FROM dbo.table1 AS t
INNER JOIN dbo.table2 AS t2
ON t.id = t.id;

The syntax error might be a more strict parsing of the word table, not sure. If that really is the name of your table, you should change it, or [put it in square brackets].

LowlyDBA - John M · Accepted Answer · 2015-03-26T15:13:23.073

The actual syntax for the command shows that there is no such thing as FOR UPDATE, UPDATE - or rather that it just doesn't make any sense:

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
 [ WITH <dml_trigger_option> [ ,...n ] ] 
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

So remove that second UPDATE (or change it to an INSERT if that's the desired functionality) and it shouldn't throw an error. Also, Aaron's suggestion for the actual trigger contents is a good modification as well.

I don't know the specifics of why it works in 2005, but it seems that the requirements for a CREATE TRIGGER were a bit more lax back then when it came to syntax checking. If it's not a documented break, which it isn't, then it's hard to say unless you ask MS.

Regardless, it does function properly in my test 2005 instance:

CREATE TABLE [dbo].[trigger_test] (
NAME nvarchar(50) NOT NULL,
counter INT NOT NULL )
GO

CREATE TRIGGER [dbo].[trigger] on [dbo].[trigger_test]
FOR UPDATE, UPDATE
AS
UPDATE [dbo].[trigger_test]
  set name = 'Greg', counter = counter + 1
GO

INSERT INTO [dbo].[trigger_test]  
VALUES ('Alice', 1)
GO

UPDATE [dbo].[trigger_test] 
SET name = 'John'
GO

SELECT * FROM [dbo].[trigger_test]
GO

Results in Greg and 2 as the result. It's probably a harmless (in 2005) addition to the trigger as it won't change the end value at all and isn't performing the update twice.

Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error

2 Answers2

Linked