sql 语句 如何同时删除两个表 里的行
Order 表中键值为 O_ID OrderDetail 表中有 O_ID 列
想删除 Order 表中的 一些行 同时 删除 Orderdetail表 中 与Order表的键值一样的行。
如何写这个sql 语句？
delete from Order，Orderdetail where？
不要用存储过程
[解决办法]
写到事务里面？
set xact_abort on
begin tran
delete Order where ……
delete Orderdetail where ……
commit tran
[解决办法]
貌似order、Orderdetail有主外键关系啊
那就先delete Orderdetail再delete Order
[解决办法]
级联删除,可以设置表order的约束; on delete cascade;
或
建个触发器;
create trigger tr_delete
on order
instead of delete
as
begin
delete from Orderdetail where id in(select id from deleted)
delete from Order where id in(select id from deleted)
end
go
[解决办法]
那就这样写吧。
set xact_abort on
begin tran
delete Orderdetail where O_ID in ……
delete Order where O_ID in ……
commit tran
