use variable in sql trigger creation

I'm trying to create a trigger dynamically and need to replace part of the create statement, in particular the database name that a join is done on.

CREATE TRIGGER [dbo].[tr_UseType_update] ON [dbo].[UseType] FOR UPDATE AS
BEGIN
  SET NOCOUNT ON 
  INSERT [dbo].[UseType]
  SELECT 'Updated', i.*
    FROM inserted
   INNER JOIN [(SELECT DB_NAME())].[dbo].[UseType] i ON inserted.[UseTypeID] 开发者_运维知识库= i.[UseTypeID]
END

I've tried this but obviously it just uses the text it doesn't actually evaluate SELECT DB_NAME(), is there a way to get it to evaluate this value.

I'm using sql 2005/8. We have a deployment tool that uses sqlcmd so a fallback will be to change the tool to take in variables that can be passed to sqlcmd but if I can reduce the amount of work and do it in the script itself (which is generated) that would be handy.

---

The only way would be to use dynamic sql.

There's no other way to parameterize database names or table names.

That being said, dynamically creating a trigger like this seems like a perilous idea.

The easiest way to do what you want will be something like:

USE MyDataBase

DECLARE @DB varchar(100) = 'MyOtherDatabaseThatIAmJoiningToInTheInsert'
DECLARE @SQL Varchar (MAX)

SET @SQL = '
CREATE TRIGGER [dbo].[tr_UseType_update] ON [dbo].[UseType] FOR UPDATE AS
BEGIN
  SET NOCOUNT ON 
  INSERT [dbo].[UseType]
  SELECT ''Updated'', i.*
    FROM inserted
   INNER JOIN ' + @DB + '.[dbo].[UseType] i ON inserted.[UseTypeID] = i.[UseTypeID]
END'

It's mandatory to read this before doing any dynamic sql code, though!

---

You could potentially execute dynamic sql and load that into a temp table which in turn would be available to the trigger.

CREATE TRIGGER [dbo].[tr_UseType_update] ON [dbo].[UseType]
    FOR UPDATE
AS
    BEGIN
        SET NOCOUNT ON
        CREATE TABLE #t (userTypeId INT)
        DECLARE @sql NVARCHAR(500) SET @sql = 'insert into #t select userTypeID from ' + DB_NAME() + '.dbo.UseType'
        EXEC sp_executeSQL @sql

        INSERT  [dbo].[UseType]
                SELECT  'Updated',
                        i.*
                FROM    inserted
                        INNER JOIN #t i ON inserted.[UseTypeID] = i.[UseTypeID]
    END