Trigger syntax (noob question)

Question

Trigger syntax (noob question)

tomiz

SSCommitted

Points: 1842
More actions
August 19, 2002 at 1:21 pm

#101224
Hello!
I'm totally new to triggers. I'm trying to make a trigger that fires when you delete something from a table called msgMain. The trigger would just delete the same rows that were deleted from msgMain from 2 other tables. This is what came up with (by studying BOL):
```
CREATE TRIGGER msgMainTrig1 ON msgMain

FOR DELETE 

AS

DELETE msgMessage FROM msgMessage INNER JOIN inserted ON msgMessage.msgID = inserted.msgID
```
I doesn't seem to work. I don't get it. I really don't know where "inserted" comes from. Is it a keyword or something?
Thank you!
/Tomi
Ps. Oh, and I'm running SLQ7

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20966 More actions · Answer 1

You can think of "inserted" like a table. It contains copies of the affected rows that change or are inserted during the execution of an INSERT and UPDATE statement. There is also a "deleted" table for that will contain those records affected by execution of a delete statement. Since you are building a delete trigger, you will need to replace the "inserted" table, with the "deleted" table.

CREATE TRIGGER msgMainTrig1 ON msgMainFOR DELETE AS DELETE msgMessage FROM msgMessage INNER JOIN deleted ON msgMessage.msgID = deleted.msgID

Hope this helps.

-------------------------

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

K. Brian Kelley SSC Guru Points: 114587 More actions · Answer 2

The best way to think of triggers is to consider them specialized stored procedures which fire on a particular data change operation. You've got the basic format correct.

The inserted and deleted recordsets are special recordsets which are only available in the trigger. Here is what they contain based on operation:

INSERT:

inserted: records added to table

deleted: not used

DELETE:

inserted: not used

deleted: records deleted from the table

UPDATE:

inserted: records as they appear AFTER the update statement

deleted: records as they appear BEFORE the update statement

Here is a link to Books Online (it's for SQL 2K, but this didn't change from SQL 7 except with regard to INSTEAD OF triggers which are new to SQL 2K and can be ignored for SQL 7) with respect to the inserted and deleted recordsets:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_0lo3.asp

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

Edited by - bkelley on 08/19/2002 1:56:04 PM

K. Brian Kelley
@kbriankelley

tomiz SSCommitted Points: 1842 More actions · Answer 3

doh!

You guru - me stupid. I really can't believe I missed that. I mean the inserted deleted thing. I think my eyes and brain had a LITTLE communication problem there. But still, thank you fellas, you rock.

/Tomi