on single procdure删除多个表 [英] on single procdure Delete multiple Table

问题描述

on single procdure删除多个表





i有3个表

1] tbl_Ag_Master，

Given_Id，

Assignment_Id，



2] tbl_Ag_Child

ID，
Given_Id



3] tbl_Bach_Ag

Assignment_Id，





i必须创建Delete procdure并传递单个参数@Given_Id，



其中所有相关行删除在该3表中

如何完成？

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?

推荐答案

获得此效果的最佳方法是在具有ON DELETE触发器的表之间指定FOREIGN KEYS。 （在删除= CASCADE上）。



所以只需删除Given_Id（主表），它就会自动删除相关的。



希望它有所帮助：）

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 :)

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

这篇关于on single procdure删除多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！