我有三个表:
T1:(t1_ID,t2_ID,t3_ID,...)
T2: (t2_ID,...)
T3:(t3_ID,...)例如:
我现在要删除 T2中 t2_ID=2的数据时,同时要将表T1中t2_ID=2的数据删除,还要同时得到t3_ID的数据再将表T3的数据进行删除?详细例子:
T1:t1_ID t2_ID t3_ID ...
1 2 1
2 2 2
...T2: t2_ID ...
1
2
...
T3: t3_ID ...
1
2
3
就是说当删除 表T2 中 t2_ID=2的时候,将T1中 t2_ID=2 的进行删除,还要删除
T3前2行。
怎么做,我的数据库中表之间没有建立关系,请大家帮助写一下存储过程或者触发器
T1:(t1_ID,t2_ID,t3_ID,...)
T2: (t2_ID,...)
T3:(t3_ID,...)例如:
我现在要删除 T2中 t2_ID=2的数据时,同时要将表T1中t2_ID=2的数据删除,还要同时得到t3_ID的数据再将表T3的数据进行删除?详细例子:
T1:t1_ID t2_ID t3_ID ...
1 2 1
2 2 2
...T2: t2_ID ...
1
2
...
T3: t3_ID ...
1
2
3
就是说当删除 表T2 中 t2_ID=2的时候,将T1中 t2_ID=2 的进行删除,还要删除
T3前2行。
怎么做,我的数据库中表之间没有建立关系,请大家帮助写一下存储过程或者触发器
for delete
begin delete from t1 where id in (select id from deleted)
delete from t3 where id < (select max(id) from deleted)
end
不知道当删除T3表的时候是不是同样引发T2表的删除,我先当做是这样create trigger tr_T2_delete
on T2
for delete
as
delete T1
from T1,deleted d
where T1.T2_Id=d.T2_Id
gocreate trigger tr_T3_delete
on T3
for delete
as
delete T1
from T1,deleted d
where T1.T3_Id=d.T3_Id
gocreate trigger tr_T1_delete
on T1
for delete
as
delete T2
from T2,deleted d
where T2.T2_Id=d.T2_Id
delete T3
from T3,deleted d
where T3.T3_Id=d.T3_Id
go
ON T1
FOR DELETE
AS
declare
@t2id varchar(100);
@t3id varchar(100);
begin
select @t2id=t2_ID,@t3id=t3_ID from deleted;
delete from t2 where t2_id=@t2id;
delete from t3 where t3_id=@t3id;
end;
@T2_Id int
as--先删除T3
delete t3
from t3,t1
where t1.T2_Id=@T2_Id
and T1.T2_Id=t3.T3_Id--再删除T3
delete t1
where T2_Id=@T2_Idgo--调用
exec pr_delete 2