469,622 Members | 1,670 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

Inserting data to tables belonging to different databases

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 programmatically connect
to different database within a trigger, how do we check the
priviledges etc.

Can someone help me?

Thanks
Christos Andronicou
Jul 23 '05 #1
1 1341
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_col)

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

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Marko Poutiainen | last post: by
3 posts views Thread by crespoh | last post: by
9 posts views Thread by gopi | last post: by
3 posts views Thread by Ralph Smith | last post: by
9 posts views Thread by karenjfrancis | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.