Delete cascade - 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 2000 Forums

Transact-SQL (2000)

Delete cascade

Author

Topic

sqlhunter123
Starting Member

2 Posts

Posted - 2008-01-14 : 08:41:49

Hi I have two tables A and B.
Table A has a foreign key which refers to primary key of Table B.
Table B has a reference to table A using a field LastAid which refers to primary key Of A. Both these relations have no cascade on delete set.
My problem is I have to delete some data from table A. But the constraint on LastAid in table B does not allow me to delete the data. How can I do this? Please help me.....
Thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 10:48:39

create a instead of delete trigger on A which deletes all reffering columns in B referring to A and then delete A's records.Something like this.

something like:-

CREATE TRIGGER MyTriff ON TableA
INSTEAD OF DELETE
AS

DELETE a
FROM TableA a
INNER JOIN TableB b
ON b.LastAid=a.LastAid
INNER JOIN DELETED d
ON d.ForKeyA=b.PrimKey

DELETE b
FROM TableB b
ON b.LastAid=a.LastAid
INNER JOIN DELETED d
ON d.ForKeyA=b.PrimKeyB

DELETE a
FROM TableA a
INNER JOIN DELETED d
ON d.prmkeyA=a.PrimkeyA

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources