Re: Replicating triggers and procedures

Hi, to make it simple, if you don't want a trigger to fire on a remote site after a replication push, you only have to embed your trigger's code in such "IF" :

IF (DBMS_REPUTIL.FROM_REMOTE = FALSE) THEN <your plsql code)
END; On Fri, 14 Feb 2003 19:28:51 GMT, "Ralf Fernan" <ralphfernan.nospam_at_yahoo.com> wrote:

>The manual refers to disabling replication on "Replicated Triggers". It
>does not say anything about Triggers that are not replicated. (See extract
>of manual below.) I presume that means that the Trigger 'X' is in the
>replication group, along with Tables A and B. The other case is that X is
>not in the replication group. What is the difference? I mean, if the
>replicated trigger fires on the remote site, DBMS_REPUTIL.FROM_REMOTE will
>return TRUE, and the remote trigger would not update remote Table B. On the
>other hand, if I don't put Trigger X in the replication group, then does
>DBMS_REPUTIL.FROM_REMOTE also return TRUE when X fires due to a replication
>change in Table A on the remote node?
>Thanks for clarifying.
>
>---- Extract of Oracle 8i Trigger Replication -------------
>"If you have defined a replicated trigger on a replicated table, you may
>need to
>ensure that the trigger fires only once for each change that you make.
>Typically, you
>will only want the trigger to fire when the change is first made, and you
>will not
>want the remote trigger to fire when the change is replicated to the remote
>site.
>You should check the value of the DBMS_REPUTIL.FROM_REMOTE package
>variable at the start of your trigger. The trigger should update the table
>only if the
>value of this variable is FALSE."
>
>"Anurag Varma" <avdbi_at_hotmail.com> wrote in message
>news:v4oo2mbs2gvn06_at_corp.supernews.com...
>> #2 + a condition on the trigger not to fire when the data
>inserted/deleted/updated is coming via replication.
>> So yes, an update to Table A will also cause the trigger to fire on the
>remote node causing possible corruption
>> .. assuming we are talking about a trigger which fires on update.
>>
>> Thus if Trigger on table A fires and updates Table B ... The update will
>be replicated ... and thus Trigger needs to
>> have it coded explicitly that it should not fire when the data is coming
>through replication.
>>
>> Read all about it in the replication manual. It is clearly stated out
>there.
>> .. you need that manual!
>>
>> Anurag
>>
>> "Ralf Fernan" <ralphfernan.nospam_at_yahoo.com> wrote in message
>news:JLX2a.51077$zF6.3443238_at_bgtnsc04-news.ops.worldnet.att.net...
>> > The trigger 'X' in this particular case is fired if certain conditions
>in
>> > Table A are met, subsequently inserting a row in Table B.
>> > Here is my confusion: if I need to replicate Tables A and B, what should
>I
>> > put in the replication group?
>> > 1) Tables A and B, and Trigger X
>> > 2) Tables A and B
>> > 3) Table A and Trigger X
>> > If Table A is in the replication group, would an update to Table A on
>the
>> > local node cause the trigger "X' to be fired in the remote node when it
>is
>> > replicated?
>> >
>> > "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
>> > news:v4oe3v49sbm866_at_corp.supernews.com...
>> > > If you are implementing row-based replication then in most cases you
>would
>> > > alter the triggers to include code so that the triggers don't fire
>when
>> > data is being
>> > > inserted via replication. However if the trigger writes to a table
>which
>> > is not being replicated,
>> > > you might not want the code which prevents its code execution.
>> > >
>> > > However, in short, you would be looking at the case 1)
>> > >
>> > > I'm assuming that the procedures you talk about are ones associated
>with
>> > the trigger (i.e. you
>> > > are not talking about procedural based replication).
>> > >
>> > >
>> > > Anurag
>> > >
>> > > "Ralf Fernan" <ralphfernan.nospam_at_yahoo.com> wrote in message
>> > news:GUV2a.35176$rq4.2667406_at_bgtnsc05-news.ops.worldnet.att.net...
>> > > > Is it necessary to include the triggers or stored procedures in the
>> > > > replication group, if the data structures (tables) they affect are
>in
>> > that
>> > > > replication group?
>> > > >
>> > > > So, in a (multimaster) replication environment, if the DB
>application
>> > > > executes a trigger or procedure which results in data modifications
>on
>> > one
>> > > > master site, will Oracle propogate these changes to the other master
>> > sites,
>> > > > if 1) the affected tables are included in the replication group, but
>the
>> > > > affecting triggers and procedures are not; or, 2) the affected
>tables,
>> > and
>> > > > affecting triggers and procedures are included in the replication
>group.
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>
Received on Wed Feb 19 2003 - 03:02:11 CST