Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We are using SQLRemote for replicating several remote DBs against one cons. DB (Sql Anywhere 10). I am looking for a solution for a specific situation where more than one remote inserts the same line locally and then all those lines are replicated to Cons. and later back to all other remotes. In other words, I want to simulate the effect of (ON EXISTING UPDATE) for the lines inserted over DBRemote. I tried to apply a (before insert trigger), but this trigger affects only the locally inserted rows, and does not affect the rows inserted over DBremote! Thanks for any help in advance.

asked 03 Jan '19, 02:59

Baron's gravatar image

accept rate: 48%

more than one remote inserts the same line locally and then all those lines are replicated to Cons

What does that mean technically? More than one remote does insert rows with the same PK, leading to a duplicate PK violation in the cons?

(03 Jan '19, 05:14) Volker Barth
Replies hidden

And please show us how the according statements look like done locally and when applied at the cons (via dbremote -v output).

(03 Jan '19, 05:22) Volker Barth

Yes, several remotes insert rows with the same PK

(03 Jan '19, 16:42) Baron

I tried to add the ON EXISTING UPDATE clause in the local statement, but this clause is not included in transaction log file (and consequently not in dbremote).

Main statement in each DB locally: insert into table T1(order_id, client_name) ON EXISTING UPDATE values (1001, 'Client1')

Replicated Statement (-v output of dbremote): insert into table T1(order_id, client_name) values (1001, 'Client1')

(03 Jan '19, 16:50) Baron
Replies hidden

That is expected behaviour.

(03 Jan '19, 16:52) Reg Domaratzki

Would this also be expected when the row is already existing in the local database? (I have never used ON EXISTING with replicated databases...)

(03 Jan '19, 17:32) Volker Barth

And is that expected behaviour or do you try to fix a PK design error?

FWIW: We have had a similar situation with child tables in a 1:1 relationship to their parent tables: The child tables therefore use the parent table's PK as their own PK. In that design with the possibility that different remotes might insert a child row to a "currently child-less parent" resulting in several INSERTs for the same row reaching the cons. Here, we have used before insert triggers to delete the row if already existing, and additionally making sure that the "last insert" is bounced back to the remote it came from.

(03 Jan '19, 17:44) Volker Barth

Just to add: The parents tables's PKs are defined as GLOBAL AUTOINCREMENT, so the particular handling described above is only effective in very rare cases...

(03 Jan '19, 17:48) Volker Barth

When you use the ON EXISTING UPDATE clause of the INSERT statement, INSERTS are logged as INSERTS, and UPDATES are logged as UPDATES.

The following SQL :

create table t2 (pk integer primary key, c1 integer);
insert into t2 on existing update values (1,1);
insert into t2 on existing update values (1,2);

Results in the following entries in the transaction log :

create table "DBA"."t2"(
  "pk" integer not null,
  "c1" integer null,
  primary key("pk"),
--COMMIT-1014-0001067166-2019-01-07 09:58:08.787
VALUES (1,1)
   SET c1=2
 WHERE pk=1
--COMMIT-1014-0001067279-2019-01-07 09:58:25.569


(07 Jan '19, 10:03) Reg Domaratzki

Ah, thanks, I'm sure the same happens for MERGE statements (possibly also including DELETEs in the log)...

(07 Jan '19, 11:19) Volker Barth
showing 3 of 10 show all flat view

If your problem is separate remotes using the same primary key, there are a number of options to ensure primary key uniqueness across your replicating system. The two simplest options are using the DEFAULT GLOBAL AUTOINCREMENT default on your primary key column, or defining your primary keys as GUIDs. See for more ideas if the simplest solutions will not work for you.

If the problem is about two remotes updating the values in the same row, the conflict can be resolved at the consolidated using a RESOLVE UPDATE trigger.

I tried to apply a (before insert trigger), but this trigger affects only the locally inserted rows, and does not affect the rows inserted over DBremote!

I don't understand this point. A before insert trigger will fire whether the row is inserted from your application or from dbremote. Also, a before insert trigger cannot change an insert into an update. It's possible to prevent the insert from happening by raising an error, but that will force a rollback of the entire transaction that includes the inserts, and not just the individual insert. Using triggers at either the remote or consolidated to maintain primary key uniqueness in any distributed system is a poor idea IMHO.


permanent link

answered 03 Jan '19, 09:28

Reg%20Domaratzki's gravatar image

Reg Domaratzki
accept rate: 37%

The problem is that separate remotes are using the same primary key. I tested a demo 'before insert trigger' that could change INSERT into UPDATE in that I delete the already existing row from within the trigger, and later the insert statement will insert the new row without PK conflict. The problem was that the trigger works only for local statements and not for statements over dbremote.

(03 Jan '19, 16:41) Baron
Replies hidden

I don't understand what you mean when you say "the trigger works only for local statements and not for statements over dbremote".

If I create an incredibly simple trigger, it will fire regardless of whether dbremote is executing the insert or whether another application is executing the insert.

create trigger bi_admin before insert on admin
referencing new as nr
for each row
  message nr.a_pkey;

Do you mean that the trigger actions don't replicate? That is expected behaviour, unless you run with the -t switch on dbremote.


(03 Jan '19, 16:48) Reg Domaratzki
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 03 Jan '19, 02:59

question was seen: 1,049 times

last updated: 07 Jan '19, 11:19