Delet Sub Table Record Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delet Sub Table Record Trigger

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-01-09 : 07:52:08

I need to Create a trigger that will delete the records in a sub table When the record in the master table is deleted.

But I need to be sure it only works when the entire record in the master Table is deleted not just a field or two.

Will this work ???

CREATE TRIGGER "SpecOrder_DelrecTrigger"
ON dbo.SpecOrder
FOR DELETE
AS
DELETE FROM dbo.SpecOrderdetail
From Deleted

WHERE (dbo.SpecOrderdetail.SpecorderID = Deleted.SpecorderID)

Jim
Users <> Logic

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-09 : 07:57:10

You might consider
DELETE FROM dbo.SpecOrderdetail
From dbo.SpecOrderdetail
join Deleted
on dbo.SpecOrderdetail.SpecorderID = Deleted.SpecorderID

Note that if there is a foreign key involved then thye error will occur before the trigger is fired. In this case you will need an instead of trigger and include the delete from the master table in the trigger.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-01-09 : 10:13:37

Thanks Nigel

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources