IF OBJECT_ID('a') IS NOT NULL DROP TABLE a GO IF OBJECT_ID('b') IS NOT NULL DROP TABLE b GO CREATE TABLE a(c VARCHAR(10)) INSERT a SELECT 'b' UNION ALL SELECT 'c' GO CREATE TABLE b(c VARCHAR(10)) INSERT b SELECT 'd' IF OBJECT_ID('tr_a_delete') IS NOT NULL DROP TRIGGER tr_a_delete GO CREATE TRIGGER tr_a_delete ON a FOR DELETE AS IF EXISTS(SELECT 1 FROM DELETEd WHERE c='b') DROP TABLE b GODELETE a WHERE c='b'SELECT * FROM b /* 消息 208,级别 16,状态 1,第 1 行 对象名 'b' 无效。*/
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a GO IF OBJECT_ID('b') IS NOT NULL DROP TABLE b GO CREATE TABLE a(c VARCHAR(10)) INSERT a SELECT 'b' UNION ALL SELECT 'c' GO CREATE TABLE b(c VARCHAR(10)) INSERT b SELECT 'd'IF OBJECT_ID('tr_a_delete') IS NOT NULL DROP TRIGGER tr_a_delete GO CREATE TRIGGER tr_a_delete ON a FOR DELETE AS declare @c varchar(255) select @c=c from DELETED exec('drop table'+@c ) GO
IF OBJECT_ID('tabledictionary') IS NOT NULL DROP TABLE tabledictionary GO IF OBJECT_ID('a') IS NOT NULL DROP TABLE a GO IF OBJECT_ID('b') IS NOT NULL DROP TABLE b GO CREATE TABLE a(c VARCHAR(10)) INSERT a SELECT 'b' UNION ALL SELECT 'c' GO CREATE TABLE b(c VARCHAR(10)) INSERT b SELECT 'd' go create table tabledictionary(id int identity,tablename varchar(255)) insert tabledictionary select 'a' union select 'b' goIF OBJECT_ID('trd_tabledictionary') IS NOT NULL DROP TRIGGER trd_tabledictionary GO CREATE TRIGGER trd_tabledictionary ON tabledictionary FOR DELETE AS begin declare @t varchar(255) declare cur cursor for select tablename from DELETED open cur fetch next from cur into @t while @@fetch_status=0 begin exec('IF OBJECT_ID('''+@t+''') IS NOT NULL DROP TABLE '+@t) fetch next from cur into @t end close cur deallocate cur end GOselect * from tabledictionary /* id tablename ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 a 2 b(所影响的行数为 2 行) */ delete from tabledictionary select * from tabledictionary select * from a select * from b服务器: 消息 208,级别 16,状态 1,行 1 对象名 'a' 无效。
DROP TABLE a
GO
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE a(c VARCHAR(10))
INSERT a
SELECT 'b' UNION ALL
SELECT 'c'
GO
CREATE TABLE b(c VARCHAR(10))
INSERT b
SELECT 'd'
IF OBJECT_ID('tr_a_delete') IS NOT NULL
DROP TRIGGER tr_a_delete
GO
CREATE TRIGGER tr_a_delete ON a
FOR DELETE
AS
IF EXISTS(SELECT 1 FROM DELETEd WHERE c='b')
DROP TABLE b
GODELETE a WHERE c='b'SELECT * FROM b
/*
消息 208,级别 16,状态 1,第 1 行
对象名 'b' 无效。*/
IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE a(c VARCHAR(10))
INSERT a
SELECT 'b' UNION ALL
SELECT 'c'
GO
CREATE TABLE b(c VARCHAR(10))
INSERT b
SELECT 'd'IF OBJECT_ID('tr_a_delete') IS NOT NULL
DROP TRIGGER tr_a_delete
GO
CREATE TRIGGER tr_a_delete ON a
FOR DELETE
AS
declare @c varchar(255)
select @c=c from DELETED
exec('drop table'+@c )
GO
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
GO
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
GO
CREATE TABLE a(c VARCHAR(10))
INSERT a
SELECT 'b' UNION ALL
SELECT 'c'
GO
CREATE TABLE b(c VARCHAR(10))
INSERT b
SELECT 'd'
go
create table tabledictionary(id int identity,tablename varchar(255))
insert tabledictionary
select 'a' union select 'b'
goIF OBJECT_ID('trd_tabledictionary') IS NOT NULL DROP TRIGGER trd_tabledictionary
GO
CREATE TRIGGER trd_tabledictionary ON tabledictionary
FOR DELETE
AS
begin
declare @t varchar(255)
declare cur cursor for select tablename from DELETED
open cur
fetch next from cur into @t
while @@fetch_status=0
begin
exec('IF OBJECT_ID('''+@t+''') IS NOT NULL DROP TABLE '+@t)
fetch next from cur into @t
end
close cur
deallocate cur
end
GOselect * from tabledictionary
/*
id tablename
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a
2 b(所影响的行数为 2 行)
*/
delete from tabledictionary
select * from tabledictionary
select * from a
select * from b服务器: 消息 208,级别 16,状态 1,行 1
对象名 'a' 无效。