use variable in sql trigger creation

Question

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.

You don't need DB_NAME() because this would resolves to the same database containing the table UseType which has this trigger. And you write to the same table the trigger is on. What is the problem you actually want to solve? — gbn
– gbn, Commented Jul 12, 2011 at 1:58
Why include the database name at all? Shouldn't dbo.UseType give you the right table? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 12, 2011 at 1:59
the trigger is in a different database to the database in the join — Daniel Powell
– Daniel Powell, Commented Jul 12, 2011 at 2:05
@Daniel - but you can only create a trigger in the current database you're using - in which case, DB_NAME() will resolve to that same database — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 12, 2011 at 2:07

JNK · Accepted Answer · 2011-07-12 02:02:44Z

4

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!

answered Jul 12, 2011 at 2:02

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gbn Over a year ago

But DB_NAME() will be the same db that contains trigger and table.

JNK Over a year ago

@gbn - yep, that's why its pointless. I'm assuming he wants to do this for multiple DBs, and will need something like sp_msforeachdb

gbn Over a year ago

yep, saw that, still a nonsense request though. A technically correct solution though.. but for what?

JNappi · Accepted Answer · 2011-07-12 02:16:33Z

0

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

answered Jul 12, 2011 at 2:16

JNappi

1,6131 gold badge16 silver badges27 bronze badges

2 Comments

gbn Over a year ago

But it's the same database already! Read comments to the question

JNappi Over a year ago

Agreed, I was simply answering how you might do it. Not whether it should be done or has any value. There might be a similiar valid use for dynamic sql in a trigger.

Collectives™ on Stack Overflow

use variable in sql trigger creation

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related