Delete Trigger - SQL Server Forums

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 2005 Forums

Other SQL Server Topics (2005)

Delete Trigger

Author

Topic

taniarto
Starting Member

27 Posts

Posted - 2013-10-12 : 00:32:12

I Have 2 Tables :
Table one is master and table 2 is stock as follow :
Master :
ID Desc unit
002 Ball Pcs
001 Book pcs

Stock
ID Qty
002 0
001 0

I want to make delete trigger, which is if the master where delete the stock will be deleted too. I try but still not working..please help..

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-12 : 04:52:17

you mean deleting a record in Master causing delete of the corresponding records in Stock

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

taniarto
Starting Member

27 Posts

Posted - 2013-10-14 : 05:28:17

Yes...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 07:05:50

You can do this in two ways

1 Making FOREIGN KEY in Stock table with ON DELETE CASCADE option on ID column
2 Creating a trigger like below


CREATE TRIGGER Trg_TodayUpStock
ON Master
FOR DELETE
AS
BEGIN 

DELETE s
FROM Stock s
WHERE EXISTS (SELECT 1
FROM DELETED
WHERE ID = s.ID)

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources