Cross-database trigger in SQL Server

Question

I have two tables CrossDBTrigTest_1 and CrossDBTrigTest_2 on same SQL Server instance.

The databases both have a table called Employee.

I wrote the following trigger on the Employee table CrossDBTrigTest_1 db:

Create Trigger [dbo].[CrossDBInsert] on [dbo].[employee] after insert
AS 
Begin
  Set nocount on

  Insert into CrossDBTrigTest_2.employee(FirstName, LastName, Date) 
    SELECT inserted.FirstName, inserted.LastName, getdate() 
    FROM inserted
End

but the Insert statement fails with message:

Msg 208, Level 16, State 1, Procedure CrossDBInsert, Line 5
Invalid object name 'CrossDBTrigTest_2.employee'.

How do I enable cross database triggers in situations like this??

Mithrandir · Accepted Answer · 2012-03-01 09:27:12Z

8

Shouldn't

  CrossDBTrigTest_2.employee(FirstName,LastName,Date)

be

  CrossDBTrigTest_2.dbo.employee(FirstName,LastName,Date)

???

answered Mar 1, 2012 at 9:27

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

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

Comments

Oleg Dok · Accepted Answer · 2012-03-01 09:30:27Z

3

Use

CrossDBTrigTest_2..employee

as table name. Note two dots instead of one.

answered Mar 1, 2012 at 9:30

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Collectives™ on Stack Overflow

Cross-database trigger in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related