SQL Anywhere database replication

Author

NOT FOR REPLICATION

Michael

2005-07-25, 7:27 am

Whether there is an analogue of the instruction of a SQL-server "NOT FOR
REPLICATION " for triggers?

Reg Domaratzki $iAnywhere Solutions$

2005-07-25, 7:27 am

You can create triggers that will only fire if dbremote is running (or not
running) based on the CURRENT REMOTE USER special constant. This variable
will be non-NULL when the operation is being applied by SQL Remote, and will
hold the name of the remote user that the operation came from.

CREATE TRIGGER bu_admin BEFORE UPDATE ON Admin
REFERENCING NEW as new_row OLD as old_row
FOR EACH ROW BEGIN
IF CURRENT REMOTE USER IS NOT NULL THEN
// Operation being applied by SQL Remote
ELSE
// Operation NOT being applied by SQL Remote
END IF;
END;

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Michael" <michael.molotsky@snapshield.com> wrote in message
news:42e4abe1@forums
-1-dub...
> Whether there is an analogue of the instruction of a SQL-server "NOT FOR
> REPLICATION " for triggers?
>
>

Nick Elson

2005-07-26, 9:23 am

A slight clarification ....

One thing to keep in mind, there are major differences
in the way MSSQL logs trigger actions when compared
to the way SA does. I believe MSSQL logs all triggers
actions for replication and (if I am correct) it needs a way
to turn that replication of trigger actions off.

With SQL Remote on ASA, trigger actions are not
ever replicated unless you also run DBREMOTE
with the "-t" switch. The only exception to this rule
is with BEFORE timing ROW-LEVEL triggers. Before
triggers complete their changes as part of the atomic
operation and must be replicated [their effect cannot be
separated from the INS/UPD/DELETE operation that
fired them].

So while Reg's example below works, it complete disables
the 'before trigger' change entirely:
- not only does it not replicate
- it doesn't even make the intended change.
To have the change actually take place but not replicate, you
may simply need to rewrite the trigger to make it an after trigger.

As a technique using CURRENT REMOTE USER is 'all but
required' when replicating trigger actions using the -t switch.
There your intention is to control (any type of) triggers action to
only have an effect on the site where the change takes place;
otherwise, you will get many 'parallel' firings and probably many
side-effects [sometimes including extra replication cycles or
even never ending changes].

For my part -t is probably best avoided unless you plan a lot
of design cycles to 'button up' all the possible side-effects;
which minimally requires a full analysis of how all triggers
will behave in the replication of changes.

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:42e4dd9e$1@foru
ms-1-dub...
> You can create triggers that will only fire if dbremote is running (or not
> running) based on the CURRENT REMOTE USER special constant. This variable
> will be non-NULL when the operation is being applied by SQL Remote, and
> will
> hold the name of the remote user that the operation came from.
>
> CREATE TRIGGER bu_admin BEFORE UPDATE ON Admin
> REFERENCING NEW as new_row OLD as old_row
> FOR EACH ROW BEGIN
> IF CURRENT REMOTE USER IS NOT NULL THEN
> // Operation being applied by SQL Remote
> ELSE
> // Operation NOT being applied by SQL Remote
> END IF;
> END;
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Michael" <michael.molotsky@snapshield.com> wrote in message
> news:42e4abe1@forums
-1-dub...
>
>

Sponsored Links