if exists(select * from sysobjects where name ='myTest')
drop table myTest
create table myTest
(
test varchar(20)
)
go drop trigger tyMycreate trigger tyMy
on myTest
for insert
as
declare @tablename varchar(20)
declare @tablename2 varchar(20)
declare @table varchar(20)select @tablename = test from myTest
set @table = CONVERT(varchar(5),'Sort')+@tablename
if exists(select * from sysobjects where name = @table)
drop table @table
create table @table --错误
(
test2 int
)
go
insert into myTest values('医疗')
select * from myTest2
select * from 医疗Sort我问过我老师,他说不行
我问过我表叔,他说可以
于是我就晕了
drop table myTest
create table myTest
(
test varchar(20)
)
go drop trigger tyMycreate trigger tyMy
on myTest
for insert
as
declare @tablename varchar(20)
declare @tablename2 varchar(20)
declare @table varchar(20)select @tablename = test from myTest
set @table = CONVERT(varchar(5),'Sort')+@tablename
if exists(select * from sysobjects where name = @table)
drop table @table
create table @table --错误
(
test2 int
)
go
insert into myTest values('医疗')
select * from myTest2
select * from 医疗Sort我问过我老师,他说不行
我问过我表叔,他说可以
于是我就晕了
drop table myTest
create table myTest
(
test varchar(20)
)
go
if object_id('tyMy') IS NOT NULL
drop trigger tyMy
go
create trigger tyMy
on myTest
for insert
as
declare @tablename varchar(20)
declare @tablename2 varchar(20)
declare @table varchar(20)select @tablename = test from INSERTED
set @table = CONVERT(varchar(5),'Sort')+@tablename
if exists(select * from sysobjects where name = @table)
EXEC('drop table '+@table)
exec('create table '+@table+'(test2 int)')
goinsert into myTest values('医疗')
select * from myTest
select * from Sort医疗
你表叔是干嘛的?
支持批量新增表
字符串超过4000时,改为游标create trigger tyMy
on myTest
for insert
as
declare @s nvarchar(4000)
set @s=''
select
@s=@s+'if object_id('''+quotename('Sort'+test)+''',''U'') is not null
drop table T
create table '+quotename('Sort'+test)+'(ID int)'+char(10)
from
inserted
exec(@s)
drop table myTest
create table myTest
(
test varchar(20)
)
go --drop trigger tyMy create trigger tyMy
on myTest
for insert
as
declare @s nvarchar(4000)
set @s=''
select
@s=@s+'if object_id('''+quotename('Sort'+test)+''',''U'') is not null
drop table T
create table '+quotename('Sort'+test)+'(ID int)'+char(10)
from
inserted
exec(@s)
go --测试
insert into myTest
select '医疗' union all
select '卫生' union all
select '健康'
--查询
select * from Sort医疗
select * from Sort卫生
select * from Sort健康
ID
----------- (所影响的行数为 0 行)ID
----------- (所影响的行数为 0 行)ID
----------- (所影响的行数为 0 行)
drop table myTest
create table myTest
(
test varchar(20)
)
go --drop trigger tyMy create trigger tyMy
on myTest
for insert
as
declare @s nvarchar(4000)
set @s=''
select
@s=@s+'if object_id('''+quotename('Sort'+test)+''',''U'') is not null
drop table '+quotename('Sort'+test)-- 改表名
+' create table '+quotename('Sort'+test)+'(ID int)'+char(10)
from
inserted
exec(@s)
go --测试重复执行
insert into myTest
select '医疗' union all
select '卫生' union all
select '健康'
--查询
select * from Sort医疗
select * from Sort卫生
select * from Sort健康