Posted by Helmut Woess on 08/22/06 06:28

Am 21 Aug 2006 15:03:50 -0700 schrieb Karthik:

> I have an application that creates and makes use of a table in tempdb.
> My problem is that I have a couple of triggers on this table that get
> dropped everytime SQL Server restarts (due to the fact that tempdb gets
> recreated). Is there a way to automatically recreate these triggers
> each time SQL Server starts? It looks like it is not possible to have a
> "create trigger" statement within a stored procedure. I would greatly
> appreciate some ideas....thanks in advance!

If you have a version with SQL-Agent, then you can define a job wich is
executed only once when SQLAgent starts. If this job cannot do a "create
trigger" and it is not possible with a stored proc too, then you can write
a batch file using OSQL or SQLCMD and create the trigger per command line.
The batch can be started by the job using xp_cmdshell. In the
job/batch/sproc you can check if the trigger exists, so it should not be a
problem to stop and start SQLAgent without restarting SQLServer.

bye, Helmut

[Back to original message]