Triggers with different schema in another database

• ahneave

  Right there with Babe

  Points: 754

  More actions

  December 16, 2010 at 2:19 pm

  #229712

  I have posted this in the Security forum but have had no response as yet. Apologies for the duplication.

  I am migrating data into a database called prodDB. The application that uses prodDB always connects as a user called vtd whose default schema is vtd and all tables in prodDB are in the vtd schema.

  I insert records in prodDB with code like...

  INSERT INTO prodDB.vtd.someProdTable SELECT field1, field2.. from MyTable

  However, there is a trigger on someProdTable with code like...

  INSERT INTO someAuditTable SELECT field1, field2.. from INSERTED

  The table someAuditTable belongs in the schema vtd, and so the trigger is assuming the user's default schema is vtd

  The trigger fails for me because it is expecting...

  INSERT INTO vtd.someAuditTable SELECT field1, field2.. from INSERTED

  On my development server I have a copy of prodDB and have created a schema called vtd. My login has vtd as the default schema for prodDB, so I thought that the trigger will now fire correctly. But it doesn't. I get the error...

  Invalid object name 'someAuditTable'

  (If I change the trigger to include the schema name it works fine, but this is not an option because (a) I can't change the production DB and (b) there are literally hundreds of them.)

  What do I need to do?

  TIA

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  December 16, 2010 at 4:23 pm

  #1263784

  If you are a sysadmin, sql will ignore your request to set a default schema on your login and use "dbo"...does that sound like what is happening?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]