DELETEROWEXISTS with OVERWRITE Resolution

For this example, the OVERWRITE resolution is illustrated with the applicable before and after images for the record in the trail and in the database. It shows how to resolve the case where the source row was deleted but the target row exists. In this case, the OVERWRITE resolution applies the delete to the target.

Table 9-6 DELETEROWEXISTS Conflict with OVERWRITE Resolution

Image SQL Comments

Before image in trail

 
name='Mary'
phone='222222'
address='Holly'
salary=100
balance=100
comment=NULL
last_mod_time='9/1/10 5:00'

N/A

After image in trail

 
None

N/A

Target database image

 
name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=100
balance=600
comment=com
last_mod_time='9/1/10 7:00'

The row exists on the target, but the phone, address, balance, comment, and last_mod_time columns are different from the before image in the trail.

Initial DELETE applied by Replicat that detects the conflict

SQL bind variables:

1)'Mary'
2)'222222'
3)'Holly'
4)100
5)100d
6)NULL
7)'9/1/10 5:00'

All columns are used in the WHERE clause because the COMPARECOLS statement is set to ALL.

A no-data-found error occurs because of the difference between the before and current values.

DELETE applied by Replicat to resolve the conflict

SQL bind variables:

1)'Mary'

Because OVERWRITE is the resolution. the DELETE is applied using only the primary key (to avoid an integrity error).