SQL Server – Syntax Error When Upgrading Database from 2005 to 2012

sql-server-2005sql-server-2012syntaxtrigger

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?

Best Answer

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.

Related Question