Inserting data to tables belonging to different databases

[ChrisA]

Hi all,

we are developing an internal application that involves "Timesheets ",
"Support" and "Project Management".
Imagine that there are 3 different databases for the above scenario,
under SQL Server 2000.
My task is to create one or a few table triggers for
INSERT/UPDATE/DELETE operations. For example:
- if a row is added in Table A of "Timesheets " database, then Table B
of "Project Management" needs to be updated.

The concept is clear i think. The question is how we do the above.
Note that I am a new progremmer to SQL Server (I have been dealing
with Oracle so far), and I don't know how we programmaticall y connect
to different database within a trigger, how do we check the
priviledges etc.

Can someone help me?

Thanks
Christos Andronicou

[David Portas]

The most important point to understand about triggers in SQL Server is
that they are triggered at statement level - there are no row-level
triggers. So you should write code that will perform correctly whether
there are 0,1 or N rows affected. To do this you reference two virtual
tables called DELETED and INSERTED, which expose the Before and After
states of the changed rows.

Copying data between databases is easy. Just specify the three-part
name: database.owner. object.

Example:

CREATE TRIGGER trg_tablea ON DB1.dbo.tableA FOR INSERT AS

INSERT INTO DB2.dbo.TableB (key_col, x, y, z)
SELECT key_col, x, y, z
FROM Inserted AS I
WHERE NOT EXISTS
(SELECT *
FROM DB2.dbo.TableB
WHERE key_col=I.key_c ol)

GO

If the databases are on different servers then you will need to be
running the Distributed Transaction Coordinator service and specify a
four-part name, including the linked server name.

Hope this helps.

--
David Portas
SQL Server MVP
--