SQL Server 2014 trigger across multiple databases

Question

I have 2 databases test1 and test2. Let's say that I have a system, that updates specific values in database test1.

Now I want to create a trigger which is placed in database test2 to check if database test1 updates the values.

When it is updated, I want to write something to database test2.

I have tried it with the following trigger:

ALTER TRIGGER [dbo].[testtrigger] 
ON [test2].[dbo].[naam2]
AFTER UPDATE ON [test1].[dbo].[naam]

However, I get a syntax error at:

Incorrect syntax near 'ON'. Expecting ',',AS,NOT_FOR, or WITH

Now I wonder why this is happening. I have searched about how to do this, and the all reference to ON.

I'm using Microsoft SQL Server 2014

I will add a image to try and make it more clear if it isn't.

You cannot do this - the trigger always has to be where the action happens - you cannot have a trigger that "observes" another table - the trigger is fired when an insert, update or delete happens on the table that the trigger is defined on. — marc_s
– marc_s, Commented Jan 3, 2017 at 14:57

TheGameiswar · Accepted Answer · 2017-01-03 14:05:26Z

1

trigger is in database test2,when a table in database test1 changes,iwant to respond..

Your syntax is wrong..Below is overview of how you can do it

create trigger triggername on test1.dbo.table1
after update
as

begin
insert into test2.dbo.table2
select * from deleted

end

edited Jan 3, 2017 at 14:05

answered Jan 3, 2017 at 13:55

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mitch Over a year ago

What i did: USE [test1].[dbo].[naam] GO create trigger ON [test1].[dbo].[naam] after update as begin insert into [test2].[dbo].[naam2] select * from deleted end Result is that it still gives the error at ON. Expecting ID or Quoted_ID

TheGameiswar Over a year ago

i missed triggername,also your syntax is not valid,,use just databasename

Mitch Over a year ago

Okay, that is working. However, it makes the trigger in database test1. My goal is to keep the trigger inside database test2 so that database test1 does not have any triggers. I want the test2 database to check if the test1 database has any updates.

TheGameiswar Over a year ago

trigger won't work like that,you can write custom logic and compare

Mitch Over a year ago

Because the whole problem is. We have a application, and we are writing a trigger that throws data to an other database. However, when we update it manual (Via SQL) the Trigger works. However, when we let the system do it (Wich put an update every 5 minutes in the database) it does nothing. The whole application actually stops with updating inside the database. So i had the idea to put the trigger inside the other database, in the hope that it would work.

Collectives™ on Stack Overflow

SQL Server 2014 trigger across multiple databases

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related