Upgrading a database from SQL Server 2005 to 2012 uncovered a syntax error 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?
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].