2

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?

  • 4
    What's the purpose of FOR UPDATE, UPDATE ? – LowlyDBA Mar 26 '15 at 14:20
  • @John Beats me, it's a legacy database that was being upgraded because of the OS going near end of life. It just looks like someone wanted to put in FOR UPDATE, INSERT and made a mistake. However it has been running like this for years now. – Reaces Mar 26 '15 at 14:21
  • 1
    My guess is that it's a previously unrecognized breaking change. Which should have been listed here: msdn.microsoft.com/en-us/library/ms143532.aspx A quick check shows me it's probably not listed there, which isn't too surprising. It is a pretty obscure bit of syntax. – Michael J Swart Mar 26 '15 at 14:39
  • @MichaelJSwart Thank you for the link! I indeed can't find any reference to triggers / updates that fits the situation I'm facing. – Reaces Mar 26 '15 at 14:57
  • Chances are it's an unrecognised bug in 2005 that's just gone away due to other code changes. I call it a bug since the the code contradicts the documented syntax. – Michael Green Mar 27 '15 at 11:40
4

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.

| improve this answer | |
  • 1
    I was actually more interested in knowing what it did before, as opposed to why it's wrong. I can't seem to find any kind of documentation saying this was a breaking change. I'm currently asking them to test if the date actually gets updated on the old environment, or if the trigger is just broken and has not worked for the last x years. – Reaces Mar 26 '15 at 14:43
  • 3
    You don't know the specifics of why it works in 2005. That's the question Reaces is asking. – Michael J Swart Mar 26 '15 at 14:58
  • @Reaces updated to cover what it actually does. – LowlyDBA Mar 26 '15 at 15:23
  • @John Thanks! Although I disagree that compiling just fine with syntax errors is harmless. As by now every INSERT should have probably updated the field, but didn't, for a period of years. Would upvote you twice if I could :) – Reaces Mar 26 '15 at 15:30
  • @Reaces Not ideal of course, but could be a lot worse! The true issue was caused by a lack of INSERT, but it functioned just as it was told to. Probably one of the happier endings to a long standing error – LowlyDBA Mar 26 '15 at 15:33
5

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].

| improve this answer | |
  • I wonder how many update will cause an error: FOR UPDATE, UPDATE, UPDATE, ... UPDATE – ypercubeᵀᴹ Mar 26 '15 at 21:36
  • Would a simple trigger with something like CREATE TRIGGER x ON foo FOR UPDATE, UPDATE AS UPDATE bar SET bar.x = bar.x + 1; be able to check if the double update actually does update twice? – ypercubeᵀᴹ Mar 26 '15 at 21:38
  • @ypercube I did FOR UPDATE, UPDATE, INSERT, UPDATE, ran an update, and the row was only updated once, so the subsequent / redundant actions are non-impactful noise. Kind of like the extra comma in CREATE TABLE dbo.a(b INT, c INT, d INT,); <-- no syntax error – Aaron Bertrand Mar 26 '15 at 21:50
  • @AaronBertrand Did the insert statement still work in that syntax?? – Reaces Mar 26 '15 at 21:52
  • @Reaces yes it did. – Aaron Bertrand Mar 26 '15 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.