Create trigger Tri_A_Del on A for delete AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
declare @ID varchar(max) --declare @ select @ID = id from deleted delete from b where a_id in (@ID) END 触发器执行
create trigger tri_del on a for delete as begin declare @id int select @id=id from deleted delete from b where a_id=@id end
我的 declare @ID varchar(max) 换为 declare @ID int也可以再事务里面做。 begin tran delete from a where id = 6 delete from b where a_id in (6) if @@error<>0 begin rollback return end Commit tran
用两个句子或者外键或者触发器实现,否则不可能一个delete语句删两张表的数据的
create table a (id int, name varchar(20)) create table b (id int, address varchar(20)) insert into a select 1001,'张三' union select 1002,'李四' union select 1003,'王五'insert into b select 1001,'四川成都' union select 1002,'四川眉山' union select 1003,'四川资阳'create trigger tri_del on a for delete as begin delete b where id in (select id from deleted)end
create trigger tri_del on a for delete as begin delete b where id in (select id from deleted)end用触发器,或者用外键关联,直接级联删除
Create Table tbl1 (name varchar(10),id Integer,primary key (id)) Insert Into tbl1 values('a',1) Insert Into tbl1 values('b',2) Insert Into tbl1 values('c',3) Create Table tbl2 (name varchar(10),id Integer,primary key (id)) Insert Into tbl2 values('aa',1) Insert Into tbl2 values('bb',2) Insert Into tbl2 values('cc',3)delete from tbl2 from tbl2 left join tbl1 on tbl1.id=tbl2.id where tbl1.id=1 delete from tbl1 from tbl1 left join tbl2 on tbl1.id=tbl2.id where tbl1.id=1select * from tbl1;select * from tbl2 drop table tbl1;drop table tbl2
Create trigger Tri_A_Del
on A
for delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @ID varchar(max)
--declare @
select @ID = id from deleted
delete from b where a_id in (@ID)
END
触发器执行
create trigger tri_del on a
for delete
as
begin
declare @id int
select @id=id from deleted
delete from b where a_id=@id
end
declare @ID varchar(max) 换为 declare @ID int也可以再事务里面做。 begin tran
delete from a where id = 6
delete from b where a_id in (6)
if @@error<>0
begin
rollback
return
end
Commit tran
create table a
(id int,
name varchar(20))
create table b
(id int,
address varchar(20))
insert into a
select 1001,'张三' union
select 1002,'李四' union
select 1003,'王五'insert into b
select 1001,'四川成都' union
select 1002,'四川眉山' union
select 1003,'四川资阳'create trigger tri_del on a
for delete
as
begin delete b where id in (select id from deleted)end
for delete
as
begin delete b where id in (select id from deleted)end用触发器,或者用外键关联,直接级联删除
Insert Into tbl1 values('a',1)
Insert Into tbl1 values('b',2)
Insert Into tbl1 values('c',3)
Create Table tbl2 (name varchar(10),id Integer,primary key (id))
Insert Into tbl2 values('aa',1)
Insert Into tbl2 values('bb',2)
Insert Into tbl2 values('cc',3)delete from tbl2 from tbl2 left join tbl1 on tbl1.id=tbl2.id where tbl1.id=1
delete from tbl1 from tbl1 left join tbl2 on tbl1.id=tbl2.id where tbl1.id=1select * from tbl1;select * from tbl2
drop table tbl1;drop table tbl2