Creating multiple triggers within a stored procedure

[franka]

I'm trying to write a stored procedure that creates several triggers. Whenever
I try to save the stored procedure I get a "incorrect syntax near the keyword
'Trigger'. If I paste the CREATE TRIGGER statements into the Query Analyzer
that run fine. It appears that the syntax error is being generated when
it reads the 2nd CREATE TRIGGER statement. Any ideas??

[DaveSatz]

from BOL under CREATE TRIGGER
Trigger Limitations
CREATE TRIGGER must be the first statement in the batch and can apply to
only one table.

maybe if used either SQL DMO (outside TSQL) or dynamic SQL to execute each
CREATE TRIGGER it would work.



--

HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
-----------------------------------------------------------------

"franka" <fea@robinson-consulting.com> wrote in message
news:3baf8b48$1@news.devx.com...
>
> I'm trying to write a stored procedure that creates several triggers.
Whenever
> I try to save the stored procedure I get a "incorrect syntax near the
keyword
> 'Trigger'. If I paste the CREATE TRIGGER statements into the Query
Analyzer
> that run fine. It appears that the syntax error is being generated when
> it reads the 2nd CREATE TRIGGER statement. Any ideas??

[Johan]

Hi,

Try seperating your CREATE TRIGGER statements with the GO statement.

Regards
Johan


"franka" <fea@robinson-consulting.com> wrote:
>
>I'm trying to write a stored procedure that creates several triggers. Whenever
>I try to save the stored procedure I get a "incorrect syntax near the keyword
>'Trigger'. If I paste the CREATE TRIGGER statements into the Query Analyzer
>that run fine. It appears that the syntax error is being generated when
>it reads the 2nd CREATE TRIGGER statement. Any ideas??

[Doug Buchanan]

Go is for batch SQL statements, not SPs

"Johan" <johan.grobler@ensemble.co.za> wrote in message
news:3bb1b84a$1@news.devx.com...
>
> Hi,
>
> Try seperating your CREATE TRIGGER statements with the GO statement.
>
> Regards
> Johan
>
>
> "franka" <fea@robinson-consulting.com> wrote:
> >
> >I'm trying to write a stored procedure that creates several triggers.
Whenever
> >I try to save the stored procedure I get a "incorrect syntax near the
keyword
> >'Trigger'. If I paste the CREATE TRIGGER statements into the Query
Analyzer
> >that run fine. It appears that the syntax error is being generated when
> >it reads the 2nd CREATE TRIGGER statement. Any ideas??
>