Creating a Trigger on a table by a stored procedure in another database

Question

Creating a Trigger on a table by a stored procedure in another database

harmonica1313

Mr or Mrs. 500

Points: 511
More actions
August 27, 2008 at 10:45 pm

#196941

Hi everybody!
I'm trying to create the stored procedure CrTr on database 'a'. This stored procedure must create a trigger on the table 'b' on the database 'b'.
My script looks like this:
use [a]
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[CrTr]
AS
BEGIN
SET NOCOUNT ON;
declare @cmd1 nvarchar(1000)
set @cmd1 = '
use
go
CREATE TRIGGER Tr
ON b.dbo.b
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into a.dbo.a select * from inserted
END'
print @cmd1
exec sp_executesql @cmd1
This stored procedure is created without any error. But when I execute it I get the below error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 3
'CREATE TRIGGER' must be the first statement in a query batch.
I do know that the CREATE TRIGGER must be the first statement in a query batch. That's why I add
'USE b'
Go
to my code.
I eagerly would be appreciated if anybody help.
Thnx
Safa

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

harmonica1313 (8/27/2008)
...
set @cmd1 = '
use
go
CREATE TRIGGER Tr
...
END'

"GO" is NOT a SQL Server command. It is a client flag/command. In this case it is a flag to SSMA's Query window tend the current batch and execute and to start a new one. This has two consequences for your code:

1. You cannot include "GO" in dynamic SQL (or stored procedures), because it is not a SQL Server command, thus you cannot use GO to start a new batch in dynamic SQL or stored procedures.

2. Anytime a line begins with "GO" in a query window, it will terminate the current batch, no matter how you try to hide it: in text quotes ('...'), or in comments (/*..*/). If the line begins with "GO ", it will flag the client query window to terminate the batch.

So, in summary: you cannot use GO like this.

If you want to control batch creation and termination in stored procedures and dynamic sql, you need to nest the dynamic SQL statment: "EXEC(string)". To start a new batch, use the EXEC('...') statement which will start a new nested batch command stream inside the current one. To terminate a batch, just make sure that the string being executed dynamically, ends there. Then the nested dynamic SQL batch will terminate and return to the calling context/batch.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

harmonica1313 Mr or Mrs. 500 Points: 511 More actions · Answer 2

Hi again

Thank you for your cooperation.

I believe that your solution do work; although I tried to apply your solution to my problem, I couldn't get the expected results. May you kindly help me further to find the solution?

Actually I cannot realize how to nest the commands.

Thanks again

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

OK, off-hand, here is how I would do it (not tested):

use [a]

go

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[CrTr]

AS

BEGIN

SET NOCOUNT ON;

declare @cmd1 nvarchar(1000)

set @cmd1 = '

use

EXEC (''

CREATE TRIGGER Tr

ON b.dbo.b

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

insert into a.dbo.a select * from inserted

END'')'

print @cmd1

exec sp_executesql @cmd1

Note the need to double the quotes every time you nest inward another level.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

harmonica1313 Mr or Mrs. 500 Points: 511 More actions · Answer 4

Woow

Thank you very much,

It seems that it would work, However there is still some problem with "quotes". I get the error : "Incorrect syntax near '@cmd1'." I doubled the quotes as you told, but the error is still there.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

oops, sorry. It's missing the "END" statement at the end of the procedure.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

harmonica1313 Mr or Mrs. 500 Points: 511 More actions · Answer 6

Alright,

thank you very much

it is solved now,

You saved me guy 😉

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Glad I could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]