Connection-Dependent Trigger Logic

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;

---