SQL Server not accepting correct syntax on trigger

Question

I'm working on a trigger in Microsoft SQL Server 2012

Here is what I have so far, following what I know to be proper syntax:

CREATE TRIGGER restore_trigger
ON dbo.Lead 
AFTER DELETE OF id, firsname, lastname, postalcode, address,phone, email, title, managementlevel, lastcalldate, lastcallresult, companyid
REFERENCING OLD ROW as OldRow 
FOR EACH ROW
     INSERT INTO dbo.Lead 
     VALUES (OldRow.id, OldRow.firstname, OldRow.lastname, OldRow.postalcode, OldRow.address, OldRow.phone, OldRow.email,
OldRow.title, OldRow.managmentlevel, OldRow.lastcalldate, OldRow.lastcallresult, OldRow.companyid);

Microsoft SQL Server is throwing an error with the 'of' and all of the values from OldRow I want to insert in to dbo.Lead.

You can only use the OF keyword with an INSTEAD. msdn.microsoft.com/en-us/library/ms189799.aspx — R. Richards
– R. Richards, Commented Dec 3, 2016 at 20:55
This is NOT correct syntax for a trigger in SQL Server - plain and simple. SQL Server does NOT have a FOR EACH ROW clause, T-SQL does not support AFTER DELETE of ....... - and T-SQL doesn't support the OLD or NEW keywords - you need to read the official MSDN documentation on the exact trigger syntax for T-SQL !! — marc_s
– marc_s, Commented Dec 3, 2016 at 20:56
@marc_s This is the syntax my professor taught me. If I wanted to run a trigger like this what would I do? Can I get an example please? I was looking at the documentation earlier and I can't make much sense of it. — Megumi Ai
– Megumi Ai, Commented Dec 3, 2016 at 21:05
As I said: this is NOT T-SQL / SQL Server syntax! It looks more like Oracle to me. If your professor taught you this as SQL Server syntax, he is PLAIN WRONG. Read the documentation - I provided the link to it ! — marc_s
– marc_s, Commented Dec 3, 2016 at 21:06
Please...when working with languages, check your sources. It is not difficult to check up MSDN's documentation on SQL Server, and the detail is superb. If you care about learning programs...learn to read what the pros read! — clifton_h
– clifton_h, Commented Dec 4, 2016 at 8:54

marc_s · Accepted Answer · 2016-12-03 21:27:33Z

1

As I said - your current code is full of syntax and logic errors when it comes to T-SQL triggers.

If you want to "save" the old values (that are being deleted), you can use this code:

CREATE TRIGGER restore_trigger
ON dbo.Lead 
AFTER DELETE  
AS
    INSERT INTO dbo.Lead (id, firstname, lastname, postalcode, address, phone, email, title, managmentlevel, lastcalldate, lastcallresult, companyid)
        SELECT 
            id, firstname, lastname, postalcode, address, phone, email, title, managmentlevel, lastcalldate, lastcallresult,  companyid
        FROM
            Deleted;

Notice:

I would recommend to always specify the columns you're inserting into - no exception.
You only need to have a single SELECT statement that takes all the rows from the Deleted pseudo table and inserts those values into the target table - this will handle multi-row deletes, too (if a statement of yours deletes 20 rows)

But I'm not clear why you'd want to INSERT the deleted rows back into the table from which they're being deleted...... what's the purpose of that?? Typically, you'd much rather be storing those values into an Audit table or something.....

And if you want to basically undo the effects of a DELETE - why not just preventing the DELETE in the first place, by removing the permission to delete from that table from those users who are not allowed to delete?.... Seems a lot more efficient to prevent an unwanted operation, rather than undoing it in a trigger....

answered Dec 3, 2016 at 21:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Megumi Ai Over a year ago

Yes I realize that this trigger is kinda trivial and has no real practical application. I was required to write any kind of trigger I wanted for the database. This is the first thing I could come up with that did not involve having to make a new table to hold some value/statistics that were calculated.

Collectives™ on Stack Overflow

SQL Server not accepting correct syntax on trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related