delete records from multiple tables

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

delete records from multiple tables

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2011-08-11 : 11:33:23

Hi All.

I have table AAA witch hold IDs from table GGG, RRR, and UUU. I would like create procedure that in case if I will delete record from table AAA, then according records from tables GGG, and RRR also will be deleted. If is it possible how it to do?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:38:01

whats the purpose behind that? usually it should be other way around ie. once you delete an entry from parent table you delete references from child. Here what you're trying to achieve is just opposite. anyways you can achieve it using a trigger on AAA for delete

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2011-08-11 : 11:43:29

I have form with some tabs. Each tab has ListView. I would like when I delete record from first tab in rest tab record also will deleted. For instance,
table AAA
==================
11 45 86 91

where 11 - AAA_Id,
45 - RRR_Id
86 - GGG_Id
91 - UUU_Id

when I delete record from first tab ListView I'm deleteting record 11 from table AAA but I would like to delete according records from table RRR and GGG.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:49:11

you can write it inside trigger. the stub will be like


CREATE TRIGGER DeleteDependency ON AAA
FOR DELETE
AS
BEGIN
DELETE g
FROM GGG g 
INNER JOIN DELETED d
ON d.GGGID = g.GGGID

DELETE r
FROM RRR r 
INNER JOIN DELETED d
ON d.RRRID = r.RRRID

DELETE u
FROM UUU u 
INNER JOIN DELETED d
ON d.UUUID = u.UUUID
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2011-08-11 : 12:08:09

The first line of code should be

CREATE TRIGGER DeleteDependency ON AAA d or I'm wrong?

And is possible create procedure from your code?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 12:12:06

whats d? it should be for delete as i gave

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2011-08-11 : 12:30:56

If my procedure looks like

alret procedure sp_DeleteAAA
(
@AAA_Id
)
as
Delete from AAA
Where AAA_Id = @AAA_Id

How to modify that procedure based on your code?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 12:39:59

no need to modify procedure. you just need to create the trigger. it will take care of itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2011-08-11 : 13:01:00

I created trigger. How to use trigger in Visual Studio 2008 for Table Adapter? The configuration of Table Adapter has four option Select, Insert, Update, and Delete. If I would like that Table Adapter perform Insert data I add stored procedure for according option. But I don't know how to use trigger.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 13:22:07

you dont need to use it. it will be called automatically for every delete you do through procedure. thats the functionality of trigger. it executes a batch of code based on DML operations done on a table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2011-08-11 : 15:04:29

Thanks you very much. Your posts is very helpful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-12 : 02:02:32

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources