Triggers

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  March 1, 2011 at 6:35 am

  #1292873

  GilaMonster (2/28/2011)

  ---

  In SQL 2005 and above, the inserted and deleted tables are materialised from the row version store in TempDB, not the transaction log. Hence they do not cause log reads any longer.

  Disabled trigger = metadata retained, trigger not executed at all

  Dropped trigger = metadata dropped, trigger completely removed.

  Just curious and pardon my ignorance on the subject... that would seem to imply that extra reads are performed from TempDB. Is that what happens? Either way, do you have a good link on the subject? I'd like to read up on what happens behind the scenes with triggers.

  Thanks, Gail.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  March 1, 2011 at 4:25 pm

  #1293281

  Jeff Moden (3/1/2011)

  ---

  that would seem to imply that extra reads are performed from TempDB. Is that what happens? Either way, do you have a good link on the subject? I'd like to read up on what happens behind the scenes with triggers. /

  Yup, that's what happens. Additional reads and writes. SQL has to add the row versions during the change, then use the row version to materialise the inserted and deleted tables.

  No link handy, please remind me next week (pm or post here), I'll dig through my stuff when I get home.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass