Forum Discussion
trigger between 2 databases
Hi, community,
I am trying to create a trigger between 2 different databases on one server.
Query:
CREATE TRIGGER insert_into_pipelines
ON [test_1].[dbo].[ag]
AFTER INSERT
AS
BEGIN
INSERT INTO [test_2].[dbo].pipelines (operator_id, pipeline_name, status)
SELECT po.guid, i.Pipeline, 'Active'
FROM [test_1].[dbo].[ag] i
INNER JOIN [test_2].[dbo].pipeline_operators po ON po.operator_name = i.Operator
WHERE NOT EXISTS (
SELECT 1 FROM [test_2].[dbo].pipelines p
WHERE p.operator_id = po.guid
AND p.pipeline_name = i.Pipeline
);
END;
Error:
Msg 40515, Level 15, State 1, Procedure insert_into_pipelines, Line 16 [Batch Start Line 0]
Reference to database and/or server name in 'test_1.dbo.ag' is not supported in this version of SQL Server.
How can I do this?
- olafhelperBronze Contributor
Reference to database and/or server name in 'test_1.dbo.ag' is not supported in this version of SQL Server.
Jaipal385 , by the error message I guess you are using Azure SQL Database? As the error message clearly says, that not support and there is no way around.
At all it's a bad idea to reference a different database within a trigger. What is that database is offline, in recovery or restore mode? Everything will fail.