Trigger on a table in TempDB

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Karthik
    #1

    Trigger on a table in TempDB


    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!

    Tags: None
    • Helmut Woess
      #2
      Re: Trigger on a table in TempDB

      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

        Comment

        • Erland Sommarskog
          #3
          Re: Trigger on a table in TempDB

          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@sommarsk og.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

            Comment

            • Karthik
              #4
              Re: Trigger on a table in TempDB


              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@sommarsk og.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/prodinf...ons/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.

                Comment

                Previous template Next
                Working...