Alternative for BEFORE DELETE trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Alternative for BEFORE DELETE trigger?

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-11-07 : 08:52:32

When I delete a row from table1 I want the referencing rows to be deleted in table2, how can I manage this using a trigger?

table1
x int primary key
val char(50)
primary key(x)

table2
p int identity(1,1)
val2 char(50)
table1_x referencing table1(x)
primary key(p)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 09:05:14

TRIGGER or referential integrity.
See http://www.sqlteam.com/article/using-set-null-and-set-default-with-foreign-key-constraints

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 09:06:57

the best way to do this is to give ON DELETE CASCADE option while definning the foreignkey constraint in table2.this will make sure deletion will happen automatically

And if you want to do it by means of trigger

CREATE TRIGGER YourTrigger
ON table1
AFTER DELETE
AS
DELETE t
FROM table2 t
INNER JOIN DELETED d
ON d.x=t.table1_x
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources