if object_id('cus') is not null drop table cus go create table cus( uname nvarchar(20) not null primary key ) go create trigger cus_ins on cus for insert as begin if not exists(select 1 from inserted) return; declare @sql nvarchar(max) select @sql=isnull(@sql,'')+'if object_id('''+uname+''') is null create table '+uname+'(id int)' from inserted exec(@sql) end goinsert into cus select 'aaaa' union all select 'bbbb';select * from aaaa select * from bbbb
当插入用户很多的时候,表会变得非常多。 USE test GO --准备环境 IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1CREATE TABLE t1 ( id tinyint identity ,value varchar(10) ) GO --建立触发器 CREATE TRIGGER tgCreateUserTable ON t1 FOR INSERT AS DECLARE @UserTableName varchar(20); DECLARE @s varchar(100); --数字不是有效的表名,所以加上'UserTable'前缀 SELECT @UserTableName = 'UserTable'+CAST ( id AS varchar) FROM inserted; --构造创建表的语句 SET @s = ' CREATE TABLE ' + @UserTableName +' ( id tinyint identity )' EXEC(@s) GO --插入数据到表t1,测试触发器 INSERT INTO t1 VALUES ('x') --验证触发器正常运行 SELECT name FROM sys.tables WHERE name LIKE 'UserTable'+'%'-------------------------------------------------------------------------------------------------------------------------------- UserTable1 UserTable2 UserTable3(3 行受影响)
创建表时,加个判断更好些--构造创建表的语句 IF OBJECT_ID(@UserTableName) IS NULL BEGIN SET @s = ' CREATE TABLE ' + @UserTableName +' ( id tinyint identity )' EXEC(@s) END
if object_id('cus') is not null drop table cus
go
create table cus(
uname nvarchar(20) not null primary key
)
go
create trigger cus_ins
on cus
for insert
as
begin
if not exists(select 1 from inserted) return;
declare @sql nvarchar(max)
select @sql=isnull(@sql,'')+'if object_id('''+uname+''') is null create table '+uname+'(id int)' from inserted
exec(@sql)
end
goinsert into cus
select 'aaaa'
union all select 'bbbb';select * from aaaa
select * from bbbb
USE test
GO
--准备环境
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1CREATE TABLE t1
(
id tinyint identity
,value varchar(10)
)
GO
--建立触发器
CREATE TRIGGER tgCreateUserTable
ON t1
FOR INSERT
AS
DECLARE @UserTableName varchar(20);
DECLARE @s varchar(100);
--数字不是有效的表名,所以加上'UserTable'前缀
SELECT @UserTableName = 'UserTable'+CAST ( id AS varchar)
FROM inserted;
--构造创建表的语句
SET @s = '
CREATE TABLE ' + @UserTableName +'
(
id tinyint identity
)'
EXEC(@s)
GO
--插入数据到表t1,测试触发器
INSERT INTO t1
VALUES
('x')
--验证触发器正常运行
SELECT name
FROM sys.tables
WHERE name LIKE 'UserTable'+'%'--------------------------------------------------------------------------------------------------------------------------------
UserTable1
UserTable2
UserTable3(3 行受影响)
IF OBJECT_ID(@UserTableName) IS NULL
BEGIN
SET @s = '
CREATE TABLE ' + @UserTableName +'
(
id tinyint identity
)'
EXEC(@s)
END