Question: How do I code a database trigger to do one thing when it is executed during a MobiLink synchronization, and something different when it is executed by an application?
Answer: This is a specific example of a common problem in triggers: "Who fired me?"
One solution is to code a connection-level CREATE VARIABLE statement in the code that fires the trigger, together a SET statement to specify "Who am I", and use an IF statement inside the trigger to ask the question "Who fired me?":
-------------------------------------------------------
-- Table and trigger.
CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
data INTEGER NOT NULL );
CREATE TRIGGER tri_t
BEFORE INSERT ON t
REFERENCING NEW AS new_t
FOR EACH ROW
BEGIN
IF who_am_i = 'accounting application' THEN
MESSAGE STRING ( 'Trigger fired by accounting application.' ) TO CONSOLE;
ELSE
MESSAGE STRING ( 'Trigger fired by something else.' ) TO CONSOLE;
END IF;
END;
-------------------------------------------------------
-- Connection 1.
CREATE VARIABLE who_am_i VARCHAR ( 100 );
SET who_am_i = 'accounting application';
INSERT t VALUES ( 1, 1 );
COMMIT;
-------------------------------------------------------
-- Connection 2.
CREATE VARIABLE who_am_i VARCHAR ( 100 );
SET who_am_i = 'payroll application';
INSERT t VALUES ( 2, 2 );
COMMIT;
-------------------------------------------------------
-- MESSAGE output.
Trigger fired by accounting application.
Trigger fired by something else.
This solution won't work so well with MobiLink. While it may be possible to code a CREATE VARIABLE statement in a script run by the MobiLink server, no such opportunity exists with the MobiLink client dbmlsync.exe.
In the case of the MobiLink client, the connection name can be used to answer "Who fired me?" because dbmlsync.exe always uses the same name "DBMLsync_main" to when connecting to the remote database.
You can use the connection name with the MobiLink server as well, but you'll have to explicitly specify the CON parameter in the command line like this:
"%SQLANY12%\bin64\mlsrv12.exe"^
-c "DSN=cons;UID=dba;PWD=sql;CON=MLSRV"^
-o mlsrv12_log_cons.txt^
-os 10M^
-ppv 60^
-vcefhkmnopstuU^
-zu+
Here's a trigger that calls
CONNECTION_PROPERTY ( 'Name' ) to see if it's MobiLink that's fired it, or something else; the same code works on both the consolidated database and the remote:
CREATE TRIGGER tri_t1
BEFORE INSERT ON t1
REFERENCING NEW AS new_t1
FOR EACH ROW
BEGIN
IF CONNECTION_PROPERTY ( 'Name' ) LIKE 'DBMLsync%'
OR CONNECTION_PROPERTY ( 'Name' ) LIKE 'MLSRV%' THEN
MESSAGE STRING ( 'Trigger fired by MobiLink client or server connection.' ) TO CONSOLE;
ELSE
MESSAGE STRING ( 'Trigger fired by application connection.' ) TO CONSOLE;
END IF;
END;
The two techniques can be combined (CONNECTION_PROPERTY and CREATE VARIABLE), and the
VAREXISTS() function can be used inside the trigger to check if the CREATE VARIABLE has been executed or not:
CREATE TRIGGER tri_t1
BEFORE INSERT ON t1
REFERENCING NEW AS new_t1
FOR EACH ROW
BEGIN
IF CONNECTION_PROPERTY ( 'Name' ) LIKE 'DBMLsync%'
OR CONNECTION_PROPERTY ( 'Name' ) LIKE 'MLSRV%' THEN
MESSAGE STRING ( 'Trigger fired by MobiLink client or server connection.' ) TO CONSOLE;
ELSE
IF VAREXISTS ( 'who_am_i' ) = 1 THEN
MESSAGE STRING ( 'Trigger fired by ', who_am_i ) TO CONSOLE;
ELSE
MESSAGE STRING ( 'Trigger fired by unknown application.' ) TO CONSOLE;
END IF;
END IF;
END;
