Creating Triggers with Dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating Triggers with Dynamic SQL

Author

Topic

AceDBA
Starting Member

13 Posts

Posted - 2004-03-30 : 20:08:47

Hi,

First a little background info. I'm setting up DTS packages to update our database on Test with Production data. Before I transfer the data, I call a proc which stores the triggers in a table and then drops the triggers on Test. After the data has transferred, I call the same Proc with a different Flag to recreate these triggers. The dropping of the triggers works great, but I can't get the Proc to CREATE the triggers in the right database. I tried putting
'Use mydbname
GO
'

in front of the Create statement, and received an error saying the CREATE Trigger statement must be the first statement in the query batch.

Any ideas on how I can set the database name dynamically when using the Exec() statement to execute dynamic SQL to create triggers?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-30 : 20:09:58

I believe you'll need a GO before the CREATE TRIGGER statement.

If you are just copying data from Prod to Test, then why not use BACKUP/RESTORE?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-30 : 21:34:22

I can't really say I would "recommend" doing this, BUT:

USE Northwind
GO

IF (SELECT OBJECT_ID('djl_test_table')) IS NOT NULL
BEGIN
DROP TABLE djl_test_table
END

CREATE TABLE djl_test_table(
ident INT PRIMARY KEY)
GO

USE pubs
GO

IF (SELECT OBJECT_ID('djl_test_table')) IS NOT NULL
BEGIN
DROP TABLE djl_test_table
END

CREATE TABLE djl_test_table(
ident INT PRIMARY KEY)
GO

USE Northwind
GO
DECLARE @sql VARCHAR(2500)

SELECT @sql = '
BEGIN TRANSACTION TRAN1
USE pubs
COMMIT TRANSACTION TRAN1

BEGIN TRANSACTION TRAN2
DECLARE @sql1 VARCHAR(2500)
SELECT @sql1 = ''
CREATE TRIGGER trg_djl_test_table_ins ON djl_test_table
FOR INSERT, UPDATE
AS

UPDATE dt
SET dt.ident = dt.ident+1
FROM
djl_test_table dt
INNER JOIN inserted i ON dt.ident = i.ident--''

PRINT @sql1
EXEC(@sql1)
COMMIT TRANSACTION TRAN2'

PRINT @sql
EXEC(@sql)

USE Northwind
GO
USE pubs
GO
EXEC sp_helptext 'trg_djl_test_table_ins'
GO

Basically, you have to wrap the dynamic SQL inside another piece of dynamic SQL. That will work.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AceDBA
Starting Member

13 Posts

Posted - 2004-03-30 : 23:25:46

Tara, I already tried the GO and it didn't help. I still got the same error saying the CREATE TRIGGER statement needs to be the first statement in the query batch. I'll post my stored proc code tomorrow so you guys have a better idea on what I'm trying to accomplish and exactly where the problem is.

Thanks.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-31 : 00:57:09

GO won't work inside dynamic SQL. GO is a batch separator understood by Query Analyzer and other tools, it isn't TSQL (Tara, you should know that

).

Probably the best way to solve this is to have each create trigger statement in a separate row in your table. That way you can create each one as it's own batch.

Damian

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-31 : 01:09:40

It would be some work setting it up, but you could actually use the method I showed you if you have the dbname in the triggers table. It's the only way I can think of to get it to work on multiple databases. The problem is that you have to process each USE as a seperate transaction. Once that transaction is committed, you can process the trigger in the appropriate database.

You could also use osql or a command-line utility to process. Run each trigger create statement seperately with osql. It would probably be easier to setup.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-31 : 01:42:31

Umm... are you doing this to stop the trigger from firing? If so have a look at the ALTER TABLE statement..IE: {ENABLE | DISABLE } TRIGGER

DavidM

"If you are not my family or friend, then I will discriminate against you"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 12:12:37

But isn't this code being run inside Query Analyzer?

Tara

AceDBA
Starting Member

13 Posts

Posted - 2004-03-31 : 12:22:05

Tara, no this is a stored proc that gets fired off from inside the DTS pkg. DavidM, I was unaware of the Enable/Disable function...will look into that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 12:24:12

Ah I missed that part.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-31 : 12:57:28

>> Any ideas on how I can set the database name dynamically when using the Exec() statement to execute dynamic SQL to create triggers?

you could use

exec ('exec mydb..executesql N''create trigger...... ...''')

It will mean doubling the quotes inside the create statement but should run in the context of the correct datatbase.

But if you are going to do this condider dropping the database and creating it all from scripts.
It will be cleaner and enable you to use bcp instead of dts meaning that you would be able to re-run the creation on with the same data. It will also give you someting small to zip up and save as a record.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-31 : 14:10:15

Why don't you just tell us why you are trying to do this. We're kind of all stumbling on each other over this it sounds like. There are probably a couple million ways to do it. If you tell us what you want to accomplish we could give much better advice.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AceDBA
Starting Member

13 Posts

Posted - 2004-04-01 : 13:27:13

derrick, all I wanted to do was disable the triggers while transferring data. I was unaware of the DISABLE trigger method, so I tried to come up with a work-around. Now, I'm testing to make sure the Disable trigger works properly. If so, I should be set! Thanks for everyones input. I will be testing this later on tonight once all the developers have left.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-01 : 22:49:37

Ok. :) It does work btw. We all use it here, so we would be in trouble if it didn't.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources