Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Using trigger between two different databases


"You can create a trigger only in the current database, although !!!!!!!
a trigger can reference objects outside of the current database."

I found thit inifo in SQL 2000 books online

now i want to understand is it applicable to use trigger between two
different databases .... may be on different servers or not ?
if yes then how ? (can i use the regular syntax)
like:

USE database1

GO
CREATE TRIGGER TEST
ON database1.table1
FOR INSERT as

insert into database2.table2(column in table 2 )

values ('any data value')

GO

if no ? then how to send any inserted or updated data from table in
database1 to another table in database2 ........... may be on different
servers
(can i use DTS in SQL 2000  or SSIS in SQL 2005)

if any one catch my point i will be eager to hear from him
thanks
-------------------------------------------
Best Regards,
Mahmoud A.Shaban

Of course you can reference remote objects in your triggers. Use three or
four-part names for remote objects (depending on whether they're on the same
server or not).

The easiest thing for you to do would be to simply try. :)

You cannot, however, *create* or *alter* triggers on remote objects.

E.g.: this is not allowed:

use db1

create trigger dbo.trigger
  on db2.dbo.table
...

ML

---
http://milambda.blogspot.com/

-----------------------------------------------Reply-----------------------------------------------

thanks for your time

your answer is to try but the syntax which i'm using

 is not allowed ??

--
Best Regards,
Mahmoud A.Shaban

On May 29, 12:39 pm, Mahmoud Shaban

I hope you realize that such triggers give you only limited
protection. For instance, you can restore one of your databases from
an earlier backup.
This action does not fire triggers and it can violate your business
rules.

-----------------------------------------------Reply-----------------------------------------------
You need to include the owner (2000) or schema (2005) between databasename
and tablename. For example, assuming dbo:

     insert into database2.dbo.table2(column in table 2 )...

RLF

"Mahmoud Shaban" <MahmoudSha@discussions.microsoft.com> wrote in message

news:A412ADEC-2A36-460A-899F-0C7B4724F468@microsoft.com...

When you CREATE the trigger, you must be in the context of the database
where the parent object resides.  So instead of:

>> use db1

>> create trigger dbo.trigger
>>   on db2.dbo.table

Say:

use db2;

create trigger dbo.trigger
   on dbo.table
...

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc