|
Posted by Karthik on 08/22/06 15:26
Erland Sommarskog wrote:
> Karthik (karthiksmiles@gmail.com) writes:
> > 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!
>
> You can say:
>
> EXEC ('CREATE TRIGGER ...')
>
> in your stored procedure (that you would set up as a start-up procedure
> for the server).
>
> However, the design sounds dubious to me. Why have the table in tempdb?
> Why not in a user database?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks Helmut and Erland! It had to be a SQL Agent job---Apparantely
cannot have a SP in the master db to create a trigger on the tempdb.
Navigation:
[Reply to this message]
|