Create Table from Trigger
-
I'm attempting to create a new table from and INSERT trigger on MSSQL Server. I've tried the following with no luck...any help out there /
ALTER TRIGGER [TRG_INS_CLIENTS] ON [dbo].[Clients]
FOR INSERT
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName+'_ParseTable' FROM insertedCREATE TABLE @ClientName (
[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) -
I'm attempting to create a new table from and INSERT trigger on MSSQL Server. I've tried the following with no luck...any help out there /
ALTER TRIGGER [TRG_INS_CLIENTS] ON [dbo].[Clients]
FOR INSERT
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName+'_ParseTable' FROM insertedCREATE TABLE @ClientName (
[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)One possible solution:
CREATE TRIGGER TRG_INS_CLIENTS ON [dbo].[Clients]
FOR INSERT
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName FROM insertedEXEC('CREATE TABLE ' + @ClientName + '(
[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL )')I'm assuming that your field name is ClientName. I'm not sure what
+'_ParseTable'is. If it is the field name, then just put ClientName_ParseTable in the select. Be careful with EXEC() - it opens a door to malicious code. ---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin -
One possible solution:
CREATE TRIGGER TRG_INS_CLIENTS ON [dbo].[Clients]
FOR INSERT
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName FROM insertedEXEC('CREATE TABLE ' + @ClientName + '(
[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL )')I'm assuming that your field name is ClientName. I'm not sure what
+'_ParseTable'is. If it is the field name, then just put ClientName_ParseTable in the select. Be careful with EXEC() - it opens a door to malicious code. ---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-RollinThanks ! I found another solution using sp_sqlexecute() but this one is more elligant.
-
Thanks ! I found another solution using sp_sqlexecute() but this one is more elligant.
Glad it helped. Here is some info on sp_sqlexec. SQL Books Online SQL Server 6.x sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application. SQL Server 2000 Removed; no longer available. Remove all references to sp_sqlexec. ---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin