＋设为首页 ＋添加到收藏夹
表1roles
roleid description
1 gdsfasdf
2 dsgsdfsd
3 adfasdfad
4 sdfasdfa
表2permission
roleid permisid
1 1
1 2
1 3
2 1
2 2
2 3
现在要删除表1里面roleid＝1
同时也要删除表2里面所有的roleid＝1的sql语句怎么写啊
试一下：
delete * from 1roles a,2permission b where a.roleid=1 and b.roleid=1
数据库本身有这个功能啊!
这两个表建立主、外键关系
然后建立删除级联
有两种方式：
1、在sql server 中设置级连删除，然后语句这样写即可
delete from lroles where roleid = 1
2、不使用级连删除，然后语句这样写即可
delete from lpermission where roleid= 1
delete from lroles where roleid = 1
3、这类问题以后最好放到数据库专区去问。
如果关系不是级联删除，先删除附表的记录，再删除主表的
delete from permission where roleid = 1
delete from roles where roleid = 1
go
就用上面方法
放在事务里去执行
CREATE PROCEDURE aaa @bb varchar(10)
AS
BEGIN TRAN T1
delete from permission where roleid = @bb
delete from roles where roleid = @bb
SELECT @del_error = @@ERROR
IF @del_error = 0
BEGIN
PRINT "The information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
IF @ins_error <> 0
PRINT "An error occurred ."
ROLLBACK TRAN
End
GO
CREATE PROCEDURE aaa @bb varchar(10)
AS
BEGIN TRAN
delete from permission where roleid = @bb
delete from roles where roleid = @bb
SELECT @del_error = @@ERROR
IF @del_error = 0
BEGIN
PRINT "The information has been replaced"
COMMIT TRAN
End
ELSE
BEGIN
ROLLBACK TRAN
End
GO
