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
