Bruge inserted i trigger med variabelt tabelnavn

Jeg mener ikke at man kan bruge parametre i tabel navne.

Jeg er bange for at du bliver noedt til at lave grim/farlig SQL streng konkatenering og saa kalde EXEC.

Man kan faktisk godt lave tabeller med en variabel.

Et lille hint fra mit personlige lager :)

create table foo(id int)

declare @n varchar(25) = 'bar'
exec sp_rename  'foo', @n


Sig det ikke til alt for mange :)

snedigt !

Hmm... Ser jo fikst ud... Men hvordan ville jeg anvende det i det ovenstående?

Jeg endte ud med det her (inden Arne skrev, men tak for svar alligevel)... Det virker faktisk fint, men ville jo gerne koge det ned hvis man kan.

----------------------------------------------
-- Check if trigger exists and delete
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DistributeLogs]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP trigger [dbo].[DistributeLogs]
GO

-- Make trigger
CREATE TRIGGER dbo.DistributeLogs ON dbo.LogData
AFTER INSERT
AS
DECLARE @UNCNAME varchar(5)
SET @UNCNAME = CONVERT(varchar(5),(SELECT Unc FROM INSERTED))
DECLARE @sqlCommand varchar(1000)

SET @sqlCommand = 'IF OBJECT_ID('''+@UNCNAME+''', ''U'') IS NULL
BEGIN
CREATE TABLE "'+@UNCNAME+'"(
    [Unc] [int] NULL,
    [Obj] [int] NULL,
    [Mnemonic] [varchar](16) NULL,
    [ObjType] [int] NULL,
    [Server] [int] NULL,
    [TimeStamp] [datetime] NULL,
    [Value] [float] NULL,
    [Message] [varchar](21) NULL
)
END'
EXEC (@sqlCommand)

DECLARE @Unc int
DECLARE @Obj int
DECLARE @Mnemonic varchar(16)
DECLARE @ObjType int
DECLARE @Server int
DECLARE @TimeStamp datetime
DECLARE @Value float
DECLARE @Message varchar(21)
SET @Unc = (SELECT CAST(Unc AS int) FROM inserted)
SET @Obj = (SELECT CAST(Obj AS int) FROM inserted)
SET @Mnemonic = (SELECT Mnemonic FROM inserted)
SET @ObjType = (SELECT CAST(ObjType AS int) FROM inserted)
SET @Server = (SELECT CAST(Server AS int) FROM inserted)
SET @TimeStamp = (SELECT CAST(TimeStamp AS datetime) FROM inserted)
SET @Value = (SELECT Value FROM inserted)
SET @Message = (SELECT [Message] FROM inserted)

SET @sqlCommand = 'INSERT INTO "' + @UNCNAME + '" VALUES('+CONVERT(varchar,@Unc)+','+CONVERT(varchar,@Obj)+','''+@Mnemonic+''','+CONVERT(varchar,@ObjType)+','+CONVERT(varchar,@Server)+','''+CONVERT(varchar,@TimeStamp)+''','+CONVERT(varchar,@Value)+','''+@Message+''')'
EXEC (@sqlCommand)

Når du nu har valgt at gør det sådan dynamisk, så afkort evt. lidt sådan her:

DECLARE @Unc int, @Obj int, @ObjType int, @Server int osv...
DECLARE @Mnemonic varchar(16)... osv.

select @Unc = Unc, @Obj = Obj. osv from inserted

SET @sqlCommand = 'INSERT INTO "' + @UNCNAME + '" VALUES('+CONVERT(varchar,@Unc)+','+CONVERT(varchar,@Obj)+','''+@Mnemonic+''','+CONVERT(varchar,@ObjType)+','+CONVERT(varchar,@Server)+','''+CONVERT(varchar,@TimeStamp)+''','+CONVERT(varchar,@Value)+','''+@Message+''')'
EXEC (@sqlCommand)

Ville egentlig gerne have givet Janus_007 point, men jeg mangler uddybende forklaring.