trigger between 2 databases

Copper Contributor

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?

3 Replies

Hi @Jaipal385 

I would like to know, In which Database you are using to create Trigger?

test1 or test2?

@Arshad440
Creating a trigger in Table_1 as the original data is loading in Table_1. In my question, I have mentioned creating in Table_2.

So creating a trigger in Table_2 if there is any new pipelin in Table_1, load that in Table_2.

Cheers..!

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.