asked on

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 ?

SOLUTION

Mr Knackered

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

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 ?

marrowyung

ASKER

"“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 ?

Mr Knackered

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.

Mr Knackered

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.

marrowyung

ASKER

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 ?

marrowyung

ASKER

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?

Vitor Montalvão

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.

marrowyung

ASKER

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial