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