My first sql trigger in sql server 2008

Question

I tried to execute the following code when I ran into a wierd error. I hope you guys can help me out here :)

CREATE TRIGGER Vlucht_Duurtussenstop
   ON  dbo.Vlucht
   AFTER UPDATE,INSERT
AS 
BEGIN
 IF (new.Duurtussenstop <> old.Duurtussenstop)
    BEGIN
      EXECUTE dbo.testprocudure1
         @p_vluchtDuurtussenstop = Duurtussenstop
    END
END
GO

Gives

[15:50:03] Gert-Jan Bos: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ' '.

Answer 1

SQL Server doesn't have new and old. It has inserted and deleted pseudo-tables, but these may contain multiple rows if the original statement affected multiple rows.

It would also be unclear, from your usage, whether testprocudure1 is being called with the old value or the new value, so I can't re-write your query at this time.

---

Ideally, the body of the testprocudure1 could be expanded out in the trigger, to avoid having to use a cursor - but to find the rows that you're interested in, the query would resemble:

SELECT i.Duurtussenstop,d.Duurtussenstop
FROM
    inserted i
        join
    deleted d
        on
             i.Col1 = d.Col1 and
             i.Col2 = d.Col2

where I assume the primary key on the table is (Col1,Col2)