Trigger blues...

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  September 19, 2006 at 8:22 am

  #173949

  So I have copied a 2000 backup to a 2005 server OS W2003, everything with the latest sp's. I restore this 2000 backup and next change the properties to SQL 2005(9) compatibility level. I start checking the database and notice some triggers are missing. I frown and decide to recreate a trigger which I scripted from my 2000 database. I receive an error: the trigger already exists...

  I check with sysobjects and notice it indeed does exists. I check the parent_object and notice this belongs indeed to the table the trigger belongs to. However, I cannot see the trigger in the object-exploder. I run a drop trigger and run the create script again. Now it runs without failure. However, the script is still not visible in object-exploder...

  What is hapening here? Other triggers in other tables show the same: exists in sysobjects, but not visible in object-exploder. Then again, there are tables with legal triggers, and visible in object-exploder. I have yet to find a pattern.

  Anyone has an idea what I can do to remedy this?

  Greetz,
  Hans Brouwer

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  September 19, 2006 at 8:43 am

  #661286

  Hans

  are you sure your looking in the right place in SSMS? Triggers on tables are listed under DatabaseName>Tables>TableName>Triggers

  while Database Triggers are listed under DataBasename>Programability>DatabaseTriggers.

  Markus

  [font="Verdana"]Markus Bohse[/font]

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  September 19, 2006 at 9:54 am

  #661298

  Tnx for replying, but yes, I have looked under the tables=>triggers folder. I will do some testing later to see if the trigger responds at all.

  Greetz,
  Hans Brouwer

• FreeHansje

  SSChampion

  Points: 11751

  More actions

  September 20, 2006 at 4:46 am

  #661443

  I have solved the problem, but am not sure how...

  The tables with trigger-blues had a user-ownership or schema. Also, I migrated to SQL2005 with restoring a backup from SQL2000. Now I detached the database, copied it to the new server and attached it. Next I changed the schema's NOT being dbo with a script I found here: http://weblogs.asp.net/steveschofield/archive/2005/12/31/434280.aspx

  Then I changed compatibilitylvl to (90) and lo! now the triggers do show up in object-exploder...

  Again, I am not sure if yesterday I had a glitch, or what exactly is different, except for the backup and file migration.

  Anyways, the problem is solved.

  Greetz,
  Hans Brouwer