Skip a Trigger for a Specific User in Informix

Is it possible to bypass a trigger when a specific db user is making changes by running a script?

In the when clause of a trigger in Informix, could we do this:

CREATE TRIGGER trigger_name
    update on table_name
    referencing old as olddata new as newdata
    for each row when(USER <> 'a specific database user')
    (…triggered actions…)

Solution

The documentation for Informix 12.10 on CREATE TRIGGER eventually leads to the WHEN condition description.

As long as you can predict when the trigger is created which user(s) should not trigger the action, you should be able to write something like WHEN (USER != 'exempt'), but it would be harder to make it work dynamically. You might be able to invoke a stored procedure in the WHEN condition that evaluates whether the current user is currently exempt from triggering the action. It probably can't be done meaningfully other than by using a stored procedure either in the WHEN condition or as the triggered action.

Proof of concept

Schema and trigger

DROP TABLE IF EXISTS trigger_test;
CREATE TABLE trigger_test
(
    s   SERIAL NOT NULL PRIMARY KEY,
    t   DATETIME YEAR TO SECOND NOT NULL DEFAULT CURRENT YEAR TO SECOND,
    v   VARCHAR(64) NOT NULL
);

DROP TABLE IF EXISTS trigger_log;
CREATE TABLE trigger_log
(
    log_id  SERIAL NOT NULL PRIMARY KEY,
    old_s   INTEGER NOT NULL,
    old_t   DATETIME YEAR TO SECOND NOT NULL,
    new_t   DATETIME YEAR TO SECOND NOT NULL,
    old_v   VARCHAR(64) NOT NULL,
    new_v   VARCHAR(64) NOT NULL,
    log_t   DATETIME YEAR TO SECOND NOT NULL DEFAULT CURRENT YEAR TO SECOND,
    log_u   VARCHAR(32) NOT NULL DEFAULT USER
);

CREATE TRIGGER skip_user
    UPDATE ON trigger_test
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW WHEN(USER != 'informix')
    (INSERT INTO trigger_log(old_s, old_t, new_t, old_v, new_v)
        VALUES(old.s, old.t, new.t, old.v, new.v)
    )
    ;

Sequence of operations as non-informix user

I'm using my sqlcmd program (available from the IIUG Software Archive in the ESQL section — it's quite distinct from Microsoft's johnny-come-lately program with the same name) and a custom tool a6 which switches to use informix credentials (effectively like su informix or sudo informix).

$ sqlcmd -d stores -xHTf user-action.sql 
+ INSERT INTO trigger_test(v) VALUES("Hello");
+ SELECT * FROM trigger_test;
s|t|v
SERIAL|DATETIME YEAR TO SECOND|VARCHAR(64)
1|2019-12-19 17:04:04|Hello
+ SELECT * FROM trigger_log;
log_id|old_s|old_t|new_t|old_v|new_v|log_t|log_u
SERIAL|INTEGER|DATETIME YEAR TO SECOND|DATETIME YEAR TO SECOND|VARCHAR(64)|VARCHAR(64)|DATETIME YEAR TO SECOND|VARCHAR(32)
+ UPDATE trigger_test SET t = '2019-12-31 23:59:59', v = 'Farewell to 2019';
+ SELECT * FROM trigger_test;
s|t|v
SERIAL|DATETIME YEAR TO SECOND|VARCHAR(64)
1|2019-12-31 23:59:59|Farewell to 2019
+ SELECT * FROM trigger_log;
log_id|old_s|old_t|new_t|old_v|new_v|log_t|log_u
SERIAL|INTEGER|DATETIME YEAR TO SECOND|DATETIME YEAR TO SECOND|VARCHAR(64)|VARCHAR(64)|DATETIME YEAR TO SECOND|VARCHAR(32)
1|1|2019-12-19 17:04:04|2019-12-31 23:59:59|Hello|Farewell to 2019|2019-12-19 17:04:04|jonathanleffler
+ UPDATE trigger_test SET t = '2038-01-19 03:14:07', v = 'Farewell to 32-bit Unix time';
+ SELECT * FROM trigger_test;
s|t|v
SERIAL|DATETIME YEAR TO SECOND|VARCHAR(64)
1|2038-01-19 03:14:07|Farewell to 32-bit Unix time
+ SELECT * FROM trigger_log;
log_id|old_s|old_t|new_t|old_v|new_v|log_t|log_u
SERIAL|INTEGER|DATETIME YEAR TO SECOND|DATETIME YEAR TO SECOND|VARCHAR(64)|VARCHAR(64)|DATETIME YEAR TO SECOND|VARCHAR(32)
1|1|2019-12-19 17:04:04|2019-12-31 23:59:59|Hello|Farewell to 2019|2019-12-19 17:04:04|jonathanleffler
2|1|2019-12-31 23:59:59|2038-01-19 03:14:07|Farewell to 2019|Farewell to 32-bit Unix time|2019-12-19 17:04:04|jonathanleffler
$

As you can see, the actions I took are logged in the trigger_log table.

Sequence of actions as user informix

$ a6 sqlcmd -d stores -xHTf ifmx-action.sql 
+ UPDATE trigger_test SET t = '2019-01-01 00:00:00', v = 'Welcome to the New Year 2019';
+ SELECT * FROM trigger_test;
s|t|v
SERIAL|DATETIME YEAR TO SECOND|VARCHAR(64)
1|2019-01-01 00:00:00|Welcome to the New Year 2019
+ SELECT * FROM trigger_log;
log_id|old_s|old_t|new_t|old_v|new_v|log_t|log_u
SERIAL|INTEGER|DATETIME YEAR TO SECOND|DATETIME YEAR TO SECOND|VARCHAR(64)|VARCHAR(64)|DATETIME YEAR TO SECOND|VARCHAR(32)
1|1|2019-12-19 17:04:04|2019-12-31 23:59:59|Hello|Farewell to 2019|2019-12-19 17:04:04|jonathanleffler
2|1|2019-12-31 23:59:59|2038-01-19 03:14:07|Farewell to 2019|Farewell to 32-bit Unix time|2019-12-19 17:04:04|jonathanleffler
$

As you can see, the update took effect, but the result was not recorded in the trigger_log table.

As you might also observe, the UPDATE statements used are sloppy; they update all rows. It might be better to use DATETIME YEAR TO FRACTION(5) too. My database servers run with their time zone set to UTC — the insert times are therefore recorded in UTC too.