In SQL Server – How can I redirect calls, without explicitly do that in the tables triggers and in stored procedure, from trigger to stored procedures and vice versa, when the triggers and tables are in one database and the stored procedures and stored functions etc. are on another database?
I don’t want i.e. do explicit call like:
USE DatabaseA;
GO
CREATE TRIGGER OrderInsertTrigger
ON Orders
AFTER INSERT
AS
BEGIN
-- Call a stored procedure in DatabaseB
EXEC DatabaseB.dbo.LogOrderInsert;
END;
GO
Instead of doing DatabaseB.dbo.LogOrderInsert, I just want to do: LogOrderInsert.
This is for test purpose, when I want to be able to change code in the customer site, and check sanity for the stored procedure, without that the customer has got the new changes I made of the stored procedures.
I want two programmatically end points, that one has the session to the tables and stored procedure of one single database, and second has the session to tables and stored procedure on separated databases.
Hence, I don’t want to change every trigger to DatabaseB.dbo.LogOrderInsert to LogOrderInsert since it should be good for both scenarios as above.
3