alter trigger

Dear all,

we have  a trigger statement like this:

Alter TRIGGER [trg_tr_boxxx_load_work_id]
ON [dbo].[tr_bxxxng_load]
FOR INSERT
NOT FOR REPLICATION 
AS
BEGIN 
   if SYSTEM_USER = 'zzzz' OR SYSTEM_USER = 'www\yyyy' 
    RETURN

      UPDATE 
            TR_BOOKING_LOAD
      SET 
            workid = SUBSTRING(ship_key, 11, (LEN(ship_key)))
      FROM 
            TR_BOOKING_LOAD tr
      WHERE 
            ISNULL(tr.workid,'') = ''
            AND EXISTS 
                  (SELECT 
                        i.*
                  FROM 
                        inserted i
                  WHERE 
                        i.booking_key = tr.booking_key
                        AND i.office_cd = tr.office_cd
                        AND i.load_date = tr.load_date
                        AND i.last_modified = tr.last_modified)
                        

END

Open in new window


what is NOT FOR Replication for ?

any way to make the trigger logic better if there are any  ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mr KnackeredCommented:
“Not for Replication” is a property which can be set for different objects like Check constraints, Foreign Key constraints, Triggers , Identity columns etc while using SQL Server Replication. Feature used when the DBA would like the transactions to behave differently when the changes are being made by the Replication Agents as compared to changes coming from a normal user transaction.

more info at http://blogs.msdn.com/b/repltalk/archive/2010/02/22/all-about-not-for-replication.aspx
0
marrowyungSenior Technical architecture (Data)Author Commented:
When insert into tr_load it will run trigger “trg_tr_boxxx_load_work_id” and it will trigger another trigger “trg_dbo_tr_booking_load_update”.
The Trigger “trg_dbo_tr_booking_load_update” will modified the PK in the tr_load and it will make the inserted record with same last_modified date.

will this kind of trigger call another trigger show everything down ? sync mode operation, right?

if it is not good, what is good to do ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"“Not for Replication” is a property which can be set for different objects like Check constraints, Foreign Key constraints, Triggers , Identity columns etc while using SQL Server Replication.

so this statement no much problem and is good looking ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mr KnackeredCommented:
Are you using SQL Replication? do you have multiple SQL servers with the data being replicated between the two?

Does the SQL Replication agent replicate the data changes from your primary database or do you want the Trigger to be replicated and fired on the replicated SQL server when the data is changed.
0
Mr KnackeredCommented:
i.e.

Table 1 is replicated from server 1 to server 2
Table 2 is replicated from server 1 to server 2

You have a trigger that fires on an insert into Table 1 that updates something on Table 2 as well.

The data changes will be replicated on server 2 to match server 1 so don't bother replicating the trigger on server 2 as the data is already being replicated.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Mr Knackered,

"Are you using SQL Replication? do you have multiple SQL servers with the data being replicated between the two?"

yes, merge replication.

"Does the SQL Replication agent replicate the data changes from your primary database "

from the publisher, right? yes if so.

I heard that change in merge replication environment, must be deploy on publisher and all subscriber, right? and why not just deploy to publisher and let the publisher replicate to subscriber ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
but this one:

"When insert into tr_load it will run trigger “trg_tr_boxxx_load_work_id” and it will trigger another trigger “trg_dbo_tr_booking_load_update”.
The Trigger “trg_dbo_tr_booking_load_update” will modified the PK in the tr_load and it will make the inserted record with same last_modified date."

insert to itself, will be very slow, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
what is NOT FOR Replication for ?
When you are using SQL Replication the data inserted in the Published table will be inserted also in the Distributed table and if there's a trigger in the Published table that will change data in another replicated table you don't want that data to be updated twice (once by the Replication and twice by the trigger) so you'll need to add the instruction NOT FOR Replication in the trigger so won't be fired after data being replicated.

In your specific case it's to avoid that TR_BOOKING_LOAD.workid being updated twice.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"When you are using SQL Replication the data inserted in the Published table will be inserted also in the Distributed table"

we are using merge replication and this means, data inserted to the DB in published table in the publisher DB will also insert/exist in distribution DB in publisher/distributor, then from distributor to all subscribers DB?

"you'll need to add the instruction NOT FOR Replication in the trigger so won't be fired after data being replicated."

it seems for a lot /all situation we have to do in this way as usually we don't want to updated twice, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
we are using merge replication and this means, data inserted to the DB in published table in the publisher DB will also insert/exist in distribution DB in publisher/distributor, then from distributor to all subscribers DB?
Yes, data insert in publisher will be replicated to distributors, no matter if the table is inserted by the application or by a trigger and that's why the NOT FOR REPLICATION clause exists to avoid the data to be inserted twice (first time by the replication and second time by trigger).


it seems for a lot /all situation we have to do in this way as usually we don't want to updated twice, right?
Yes, I'ld say this should be the normal behavior.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.