I hope this is in the right forum.
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