Indicates if a column is a source of conflict for an UPDATE being performed against a consolidated database in a SQL Remote environment.
CONFLICT( column-name )
column-name The name of the column being tested for conflicts.
Returns TRUE if the column appears in the VERIFY list of an UPDATE statement executed by the SQL Remote Message Agent and if the value provided in the VALUES list of that statement does not match the original value of the column in the row being updated. Otherwise, returns FALSE.
SQL/2008 Vendor extension.
The CONFLICT function is intended for use in SQL Remote RESOLVE UPDATE triggers to avoid error messages. To illustrate the use of the CONFLICT function, consider the following table:
CREATE TABLE Admin ( PKey bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT, TextCol CHAR(20) NULL, PRIMARY KEY ( PKey ) ); |
Assume that consolidated and remote databases both have the following row in the Admin table:
1, 'Initial' |
Now, at the consolidated database, update the row as follows:
UPDATE Admin SET TextCol = 'Consolidated Update' WHERE PKey = 1; |
At the remote database, update the row to a different value as follows:
UPDATE Admin SET TextCol = 'Remote Update' WHERE PKey = 1; |
Next, run dbremote on the remote database. It generates a message file with the following statements in it, to be executed at the consolidated database:
UPDATE Admin SET TextCol='Remote Update' VERIFY ( TextCol ) VALUES ( 'Initial' ) WHERE PKey=1; |
When the SQL Remote Message Agent runs at the consolidated database and applies this UPDATE statement, SQL Anywhere uses the VERIFY and VALUES clause to determine whether a RESOLVE UPDATE trigger will fire. A RESOLVE UPDATE trigger fires only when the update is executed from the SQL Remote Message Agent against a consolidated database. Here is a RESOLVE UPDATE trigger:
CREATE TRIGGER ResolveUpdateAdmin RESOLVE UPDATE ON Admin REFERENCING OLD AS OldConsolidated NEW AS NewRemote REMOTE as OldRemote FOR EACH ROW BEGIN MESSAGE 'OLD'; MESSAGE OldConsolidated.PKey || ',' || OldConsolidated.TextCol; MESSAGE 'NEW'; MESSAGE NewRemote.PKey || ',' || NewRemote.TextCol; MESSAGE 'REMOTE'; MESSAGE OldRemote.PKey || ',' || OldRemote.TextCol; END; |
The RESOLVE UPDATE trigger fires because the current value of the TextCol column at the consolidated database ('Consolidated Update'
) does not match the value in the VALUES clause for the associated column ('Initial'
).
This trigger results in a failure because the PKey column was not modified in the UPDATE statement executed on the remote, so there is no OldRemote.PKey value accessible from this trigger.
The CONFLICT function helps to avoid this error by returning the following values:
If there is no OldRemote.PKey value, return FALSE.
If there is an OldRemote.PKey value, but it matches OldConsolidated.PKey, return FALSE.
If there is an OldRemote.PKey value, and it is different than OldConsolidated.PKey, return TRUE.
You can use the CONFLICT function to rewrite the trigger as follows and avoid the error:
CREATE TRIGGER ResolveUpdateAdmin RESOLVE UPDATE ON Admin REFERENCING OLD AS OldConsolidated NEW AS NewRemote REMOTE as OldRemote FOR EACH ROW BEGIN message 'OLD'; message OldConsolidated.PKey || ',' || OldConsolidated.TextCol; message 'NEW'; message NewRemote.PKey || ',' || NewRemote.TextCol; message 'REMOTE'; if CONFLICT( PKey ) then message OldRemote.PKey; end if; if CONFLICT( TextCol ) then message OldRemote.TextCol; end if; END; |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |