有表如下: A
+----------+
| aID |
+----------+
| bID |
+----------+
| C 表名称 |
+----------+
B
+----------+
| bID |
+----------+
| DATA |
+----------+
-- C表表名是随机产生的
C
+----------+
| cID |
+----------+
| DATA |
+----------+
问题 当删除B表的某条记录时,删除A表中的记录并删除所有C表
SQL语句该怎么写
+----------+
| aID |
+----------+
| bID |
+----------+
| C 表名称 |
+----------+
B
+----------+
| bID |
+----------+
| DATA |
+----------+
-- C表表名是随机产生的
C
+----------+
| cID |
+----------+
| DATA |
+----------+
问题 当删除B表的某条记录时,删除A表中的记录并删除所有C表
SQL语句该怎么写
解决方案 »
- 如何判断多个表是否数据量超过50比较快?
- 面试题查 询语句的问题
- 关于生成数据统计表单的问题
- 一个关联两个表的查询
- SQL数据库中视图view是不是不可以包含if语句?为什么我写的语句它提示IF附近有错误,但是可以出来数据,而且视图不可以保存
- 请教:、在SQL Server 数据库中,有一个表StudentAchievement(学生成绩),它有三个字段:StudentID(varchar(8),学生编号),CourseID(var
- sqlServer 2005 一个baidu,google了很多天多没解决的问题 强人进来看看
- 求SQL语句或存储过程
- 请问怎么才能把DBF类型的表转换成SQL类型的 表?
- 即刻给分!!!--如何在VB中返回SQL Server存储过程的OUTPUT返回值和Return值
- 关于SQLSERVER2005备份还原的问题
- 关于distributor_admin登录失败
C表表名都不确定,那如何找到它呢?跟AB表什么关系?
+----------+
| bID |
+----------+
| bID |
+----------+
| DATA |
+----------+
写一个触发器create trigger tr_B
on B for delete
as
begin
delete froM A where bID IN(SELECT bID from deleted)
delete from C
end
A
+----------+
| aID |
+----------+
| bID |
+----------+
| C 表名称 |
+----------+
B
+----------+
| bID |
+----------+
| DATA |
+----------+
-- C表表名是随机产生的
C
+----------+
| cID |
+----------+
| DATA |
+----------+当 C 表名称(随机且唯一的)产生时比如AAA 此时根据B表的bID 更新A表 那么A表的记录就是aID,bID, AAA
由于外部条件要求删除B表中指定bID的记录并要求一起删除A表中所有bID相关的数据并且不保留C表数据
Declare @tbname char(10)
Select @tabname=C 表名称 From a where a.bID=@bID
Drop table @tabname
Delete from a where bid=@bID
Delete From b where bID=@bID
IF @@error=0
Commit tran tran1
else
rollback tran tran1应该可以完成你的操作了
set @bid=1 -- 比如删除bid=1
Declare @tbname varchar(20)
Select @tabname=[C表名称] From a where a.bID=@bID
declare @sql varchar(4000)set @sql='truncate table '+@tabname
Begin Tran
Delete from a where bid=@bID
Delete From b where bID=@bID
exec(@sql)IF @@error=0
Commit tran tran1
else
rollback tran tran1
aId bID C表表名
1 1 AAA
2 1 BBB
3 1 CCC
4 2 DDD
5 3 EEE
6 3 FFF
...
当要求删除B表中bID = 3的记录时同时删除 bID=3的A表记录及C表(表EEE、FFF)
(
AID INT,
BID INT REFERENCES #B ON DELETE CASCADE ,---关联B表,实现级联删除
CNAME VARCHAR(10)
)CREATE TABLE #B
(
BID INT,
DATA VARCHAR(10)
)这个方法配合了级联删除,这边只要删除C表即可
CREATE TRIGGER trig_CTable
ON #A
FOR DELETE
AS
BEGIN
DECLARE @SQL VARCHAR(100)
IF EXISTS(SELECT 1 FROM DELETED)
BEGIN
SET @SQL =' DROP TABLE ' + DELETED.CNAME
EXEC (@SQL)
END
END方法二:该方法不需要设定级联关系,在B上建立触发器,同时处理A表,C表。
CREATE TRIGGER TEST ON B
FOR DELETE
BEGIN
DECLARE @SQL VARCHAR(100)
IF EXISTS(SELECT 1 FROM DELETED)
BEGIN
DELETE FROM A WHERE BID=DELETED.BID ----删除A表记录
SET @SQL =' DROP TABLE ' + DELETED.CNAME----drop C表
EXEC (@SQL)
END
END
我等一下用你的方法尝试一下
我现在自己用笨方法解决了 采用了 游标 具体调试代码如下use tzyj_motortest_data
GOif (exists (select * from sysobjects where name = N'mt_A'))
drop table mt_A
GOcreate table mt_A(
aID int identity primary key not null,
bID int not null,
cName varchar(50) not null
)
GOif (exists (select * from sysobjects where name = N'mt_B'))
drop table mt_B
GOcreate table mt_B(
bID int identity primary key not null,
yyyData varchar(100)
)
GO
/*
使用游标删除所有符合条件的表
*/
declare @name varchar(128)
declare @icount int
declare curTableName CURSOR for select name from sysobjects where name like 'mt_tbl%'
open curTableNamefetch next from curTableName into @name
while (@@fetch_status = 0)
begin
set @icount = @icount + 1
exec ('drop table ' + @name)
fetch next from curTableName into @name
end
print @icount
close curTableName
deallocate curTableName
GO declare @tblName varchar(50)
declare @icount int
declare @sql nvarchar(1000)
declare @rowcount int
set nocount on
set @icount = 0
while (@icount < 50)
begin
exec mt_pCreateTableNameWithRand @tblName output
SET @sql=N'SELECT @A=COUNT(*) FROM sysobjects WHERE [NAME] = ''' + @tblName + ''''
EXEC sp_executeSQL @sql, N'@A INT OUTPUT', @rowCount OUTPUT
-- 如果参数为NULL 或者 存在相同表名的表 则重新生成一个表名
IF @tblName IS NULL AND @rowCount <= 0
BEGIN
waitfor delay '00:00:01.000'
EXEC mt_pCreateTableNameWithRand @tblName OUTPUT
END exec ('create table ' + @tblName + '(dataID int identity primary key not null,
data varchar(100))')
insert into mt_A
(bID,cName) values (@icount % 10 + 1, @tblName) waitfor delay '00:00:00.100'
set @icount = @icount + 1
end
set nocount off
GOselect * from mt_A order by bID
GOdeclare @icount int
declare @data varchar(100)
set nocount on
set @icount = 0
while (@icount < 10)
begin
set @data = 'ABC' + str(@icount)
insert into mt_B
(yyyData) values(@data)
set @icount = @icount + 1
end
GO
set nocount off
select * from mt_B
GOif (exists (select * from sysobjects where name = 'mt_pDelXXXTable'))
drop procedure mt_pDelXXXTable
GOcreate proc mt_pDelXXXTable
(
@bID int
)
AS
declare @errCde int
declare @tblName varchar(128)
begin
set nocount on declare curTblName CURSOR for select cName from mt_A where bID = @bID
open curTblName
fetch next from curTblName into @tblName
while(@@Fetch_Status = 0)
begin
print @tblName
fetch next from curTblName into @tblName
end
close curTblName
deallocate curTblName
delete from mt_A where bID = @bID
delete from mt_B where bID = @bID
end
GOexec mt_pDelXXXTable 5select * from mt_A order by bID
select * from mt_B
print @tblName
替换成
exec ('drop table ' + @tblName)