Triggers for different data base

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Triggers for different data base

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-24 : 08:21:23

Vipin writes "Hello

Can it be possible that triggers can be fired to affect tables lying in different database.

Database may be on same Machine or may be on different machine.

Explain with Example"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-24 : 09:45:13

Yes.

Just use mydb.dbo.mytabl
or mylnksvr.mydb.dbo.mytbl with distributed transactio (this can cause big trouble)
in the update

Note though you need to take this into account with backup strategy and architecture.
If you restore a backup then the two dtabases will be out of step.
If one database goes down (likely on different servers) then the trigger will fail and so you can't update on the source database.

Better to put the transaction into a staging table which is then actioned on the destination database - this can automatically recover after a restore or a database being off-line.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

agossage
Starting Member

9 Posts

Posted - 2007-01-24 : 10:22:36

Within the same instance, you can execute against any table by prefixing the table with the instance name.
Example:
Current database = A
Other datbase = B
INSERT INTO B.dbo.Table SELECT x, y, z FROM inserted

Outside of the current instance including another machine, you will need to prefix the table with linked server and database.
Example:
Current database = A
Linked Server = LinkTest
Other database = B
INSERT INTO LinkTest.B.dbo.Table SELECT x, y, z FROM inserted

Some notes about using linked servers in triggers...
1. Any local trigger will fail if the remote server/instance is not available for any reason.
2. Using a linked server will have some performance degredation.
I am sure there are more issues. My advice is find another method.

Adam Gossage
Lake Wylie, SC, USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources