CODEWITHSUNDEEP
sql-servert-sqltriggers
mill
mill

Reputation: 85

Cross-database trigger in SQL Server

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??

Upvotes: 0

Views: 3891

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

Use

CrossDBTrigTest_2..employee

as table name. Note two dots instead of one.

Upvotes: 3

Mithrandir
Mithrandir

Reputation: 25337

Shouldn't 

  CrossDBTrigTest_2.employee(FirstName,LastName,Date)

be 

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

???

Upvotes: 8

Related Questions