Forum Discussion

Jaipal385's avatar
Jaipal385
Copper Contributor
Dec 25, 2023

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?

  • Arshad440's avatar
    Arshad440
    Brass Contributor

    Hi Jaipal385 

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

    test1 or test2?

    • Jaipal385's avatar
      Jaipal385
      Copper Contributor
      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..!
  • olafhelper's avatar
    olafhelper
    Bronze 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.

Resources