Discussion Locked

Trigg. & Stor. Proc. inside Stored Proc.

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to create a stored procedure in a master database that is responsible for creating stored procedures and triggers in other databases in the same SQL Server.

The problem is that the database name can not be used in the create trigger statement,
my solution was the example below:

Declare @CTrigProc varchar(4000)
/***************************************************************************/
/* Creation of Triggers */
/***************************************************************************/

/****** Object: Trigger dbo.trgIDTtpeUpdate Script Date: 2/15/01 12:14:58 PM ******/
SELECT @CTrigProc = "Use Praemis_Master" + Char(13) + Char(10) +
"CREATE TRIGGER trgIDTtpeUpdate ON IDType FOR UPDATE AS" + Char(13) +
"SET NOCOUNT ON" + Char(13) +
"/* * CASCADE UPDATES TO 'IDType' */" + Char(13) +
"IF UPDATE(IDocType)" + Char(13) +
" BEGIN" + Char(13) +
" UPDATE Student" + Char(13) +
" SET Student.IDocType = inserted.IDocType" + Char(13) +
" FROM Student, deleted, inserted" + Char(13) +
" WHERE deleted.IDocType = Student.IDocType" + Char(13) +
" END"
EXECUTE (@CTrigProc)

************************************
I get the following error:

Server: Msg 111, Level 15, State 1, Line 2
'CREATE TRIGGER' must be the first statement in a query batch.
Server: Msg 140, Level 15, State 1, Line 2
Can only use IF UPDATE within a CREATE TRIGGER statement.

****************************************
Any suggestions or references will be useful

thanks

4 replies

by jmiller1

You need to use EXEC in order to use the 'USE' command in the stored proc...Here's how I would write it:

CREATE PROCEDURE CTrigProc
AS

EXEC ('
Use Praemis_Master
EXEC (''CREATE TRIGGER trgIDTtpeUpdate ON IDType FORUPDATE AS
SET NOCOUNT ON
/* * CASCADE UPDATE TO "IDType" */
IF UPDATE(IDocType)
BEGIN
UPDATE Student
SET Student.IDocType = inserted.IDocType
FROM Student, deleted, inserted
WHERE deleted.IDocType = Student.IDocType
END
'')
')

Then all you have to do is change the code inside the second EXEC statement.

jm

by jmiller1

That didn't work well, here it is again...

CREATE PROCEDURE CTrigProc
AS

EXEC ('
Use Praemis_Master
EXEC (''CREATE TRIGGER trgIDTtpeUpdate ON IDType FOR UPDATE AS
SET NOCOUNT ON
/* * CASCADE UPDATE TO "IDType" */
IF UPDATE(IDocType)
BEGIN
UPDATE Student
SET Student.IDocType = inserted.IDocType
FROM Student, deleted, inserted
WHERE deleted.IDocType = Student.IDocType
END
'')
')

by logan2001x

Even though I came with the same solution before coming here, the answer is correct.

Excellent

Thanks

This discussion is closed to new replies.