Click here to Skip to main content
15,071,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
on single procdure Delete multiple Table


i have 3 table
1]tbl_Ag_Master,
Given_Id,
Assignment_Id,

2]tbl_Ag_Child
ID,
Given_Id

3]tbl_Bach_Ag
Assignment_Id,


i have to create Delete procdure with passing single parameter @Given_Id,

where all related row delete in that 3 table
How it Done?
Posted
Comments
Karen Mitchelle 9-May-14 1:51am
   
I think it's not possible for you to delete a row on the 3rd table. You don't have @given_ID there.

Maybe, you could use joins?
King Fisher 9-May-14 1:54am
   
use triggger
Rajnish D mishra 9-May-14 1:56am
   
if i Add given_id on that Table so how it Possible please help Karen

The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger. (On Delete=CASCADE).

So just delete the Given_Id(main table) and it will automatically delete the related one.

Hope it helps :)
   
v2
SQL
you can use Trigger


create trigger [dbo].[tri_Name] on tbl_Ag_Master
for delete
as
declare @Given_Id bigint;
declare @Assignment_Id bigint

select @Assignment_Id=d.Assignment_Id from deleted d;
select @Given_Id =d.Given_Id  from deleted d;

delete  from tbl_Ag_Child where Given_id=@Given_Id

delete from tbl_Bach_Ag where Assignment_Id_id=@Assignment_Id
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900