Thursday, March 22, 2012

3 triggers into one

Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>

No comments:

Post a Comment