cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

duplicated rows in sqlremote

Baron
Participant
0 Kudos
1,484

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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 http://dcx.sybase.com/index.html#sqla170/en/html/95f549cb6ea110148bccd9e49ca2a26a.html 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.

Reg

Baron
Participant
0 Kudos

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.

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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
begin
  message nr.a_pkey;
end;

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

Reg