Informix trigger updating same table

Abstract

Informix Dynamic Server (IDS) does allow a table to be updated by a trigger on itself if you know how to work around certain restrictions. The documentation is not very thorough on this subject, which this article rectifies.

Content

Let’s say you want to have two columns at the end of tables containing the last time a row was inserted or updated, and the name of the user responsible, for example:

CREATE TABLE test_data
(
    unique_id          SERIAL8                                                    NOT NULL UNIQUE,
    data_content       VARCHAR(255)                                               NOT NULL,
    last_update_time   DATETIME YEAR TO SECOND   DEFAULT CURRENT YEAR TO SECOND   NOT NULL,
    last_update_user   CHAR(32)                  DEFAULT USER                     NOT NULL
);

 
CREATE TABLE test_data(    unique_id          SERIAL8                                                    NOT NULL UNIQUE,    data_content       VARCHAR(255)                                               NOT NULL,    last_update_time   DATETIME YEAR TO SECOND   DEFAULT CURRENT YEAR TO SECOND   NOT NULL,    last_update_user   CHAR(32)                  DEFAULT USER                     NOT NULL);

If you need sub-second precision with CURRENT, you must set configuration parameter USEOSTIME 1 in the $ONCONFIG file. This article assumes that is not the case, so DATETIME columns and variables are declared as YEAR TO SECOND only.

The best way to provide values for INSERT statements is by setting a DEFAULT for each column as above. That will be fastest for bulk loading, and does not require a trigger.

The only way to maintain those two columns during an UPDATE is via a TRIGGER. The only actions allowed in a trigger are INSERT, UPDATE, DELETE or EXECUTE as documented here. Your first attempt might therefore be:

CREATE TRIGGER upd_test_data
    UPDATE ON test_data
    REFERENCING NEW AS n
    FOR EACH ROW
    (
        UPDATE test_data
        SET
            last_update_time = CURRENT,
            last_update_user = USER
        WHERE
            unique_id = n.unique_id
    );

 
CREATE TRIGGER upd_test_data    UPDATE ON test_data    REFERENCING NEW AS n    FOR EACH ROW    (        UPDATE test_data        SET            last_update_time = CURRENT,            last_update_user = USER        WHERE            unique_id = n.unique_id    );

That fails with:

-747    Table or column matches object referenced in triggering statement.

This error is returned when a triggered SQL statement acts on the
triggering table, or when both statements are updates, and the column
that is updated in the triggered action is the same as the column that
the triggering statement updates.

​x
 
-747    Table or column matches object referenced in triggering statement.​This error is returned when a triggered SQL statement acts on thetriggering table, or when both statements are updates, and the columnthat is updated in the triggered action is the same as the column thatthe triggering statement updates.

The fix is to specify a subset of columns that action the trigger, “data_content” in this case:

CREATE TRIGGER upd_test_data
    UPDATE OF data_content ON test_data
    REFERENCING NEW AS n
    FOR EACH ROW
    (
        UPDATE test_data
        SET
            last_update_time = CURRENT,
            last_update_user = USER
        WHERE
            unique_id = n.unique_id
    );

If you do specify columns, you might also add a condition so that the trigger only fires if the contents have actually changed:

CREATE TRIGGER upd_test_data
    UPDATE OF data_content ON test_data
    REFERENCING OLD AS o NEW AS n
    FOR EACH ROW
    WHEN (o.data_content != n.data_content)
    (    
        UPDATE test_data
        SET
            last_update_time = CURRENT,
            last_update_user = USER
        WHERE
            unique_id = n.unique_id
    );

However, it is inefficient to run a second UPDATE statement at all. It would also be harder to maintain: the trigger might need recreating with a corrected column list whenever the table’s schema is changed.

The standard approach is documented here, and is illustrated in the following example:

CREATE PROCEDURE sp_test_data()

    REFERENCING NEW AS n FOR test_data; 

    LET n.last_update_time = CURRENT;
    LET n.last_update_user = USER;

END PROCEDURE;

CREATE TRIGGER upd_test_data
    UPDATE ON test_data
    FOR EACH ROW
    (
        EXECUTE PROCEDURE sp_test_data()
            WITH TRIGGER REFERENCES
    );

The REFERENCING clause in the trigger procedure means you can simply assign new column values with LET statements.

The problem is that, if you have 100 tables for which you want to maintain these two audit columns, you would need 100 separate stored procedures. Fortunately, there is an alternate barely documented syntax in a trigger action using EXECUTE and INTO:

CREATE FUNCTION sp_audit_columns()

    RETURNING
        DATETIME YEAR TO SECOND,
        VARCHAR(32);
    
    RETURN
        CURRENT,
        USER;

END FUNCTION;

CREATE TRIGGER upd_test_data
    UPDATE ON test_data
    FOR EACH ROW
    (
        EXECUTE FUNCTION sp_audit_columns() INTO
            last_update_time,
            last_update_user
    );

The same standard function can then be used for triggers on any number of tables. There is no REFERENCING clause above. It’s unnecessary, and using such correlation names in the INTO clause makes CREATE TRIGGER fail:

-732    Incorrect use of old or new values correlation name inside trigger.

You cannot use the new or old correlation name outside the FOR EACH ROW
section or in the INTO clause of the EXECUTE PROCEDURE statement.

 
-732    Incorrect use of old or new values correlation name inside trigger.​You cannot use the new or old correlation name outside the FOR EACH ROWsection or in the INTO clause of the EXECUTE PROCEDURE statement.

There is another common need for a trigger to update the same table. SERIAL and SERIAL8 columns auto-increment on each insert, but there can be circumstances where that number needs to apply across more than one table. For example, an actual customer wanted different types of invoice in separate tables, but with the numbers continuous and distinct across all of them. This is a good application for a SEQUENCE.

In our example schema, the data type of “unique_id” is SERIAL8. Let’s say we now need to get its value from a sequence automatically during an insert. The initial set up steps might be:

ALTER TABLE test_data MODIFY (unique_id INT8);
SELECT NVL(MAX(unique_id),0) + 1 FROM test_data;
CREATE SEQUENCE seq_test_data START WITH above-result;

 
ALTER TABLE test_data MODIFY (unique_id INT8);SELECT NVL(MAX(unique_id),0) + 1 FROM test_data;CREATE SEQUENCE seq_test_data START WITH above-result;

The standard approach would then be:

CREATE PROCEDURE sp_test_data()

    REFERENCING NEW AS n FOR test_data; 

    LET n.unique_id = seq_test_data.NEXTVAL;

END PROCEDURE;

CREATE TRIGGER ins_test_data
    INSERT ON test_data
    FOR EACH ROW
    (
        EXECUTE PROCEDURE sp_test_data()
            WITH TRIGGER REFERENCES
    );

 
CREATE PROCEDURE sp_test_data()​    REFERENCING NEW AS n FOR test_data; ​    LET n.unique_id = seq_test_data.NEXTVAL;​END PROCEDURE;​CREATE TRIGGER ins_test_data    INSERT ON test_data    FOR EACH ROW    (        EXECUTE PROCEDURE sp_test_data()            WITH TRIGGER REFERENCES    );

That would need a separate function for each table using a sequence. We could rationalise them all into just one with:

CREATE FUNCTION sp_nextval(name VARCHAR(128))

    RETURNING INT8 AS value;

    DEFINE value INT8;
    
    PREPARE stm_nextval FROM 'SELECT ' || name || '.NEXTVAL';
    DECLARE cur_nextval CURSOR FOR stm_nextval;
    OPEN    cur_nextval;
    FETCH   cur_nextval INTO value;
    CLOSE   cur_nextval;
    FREE    cur_nextval;

    RETURN value;

END FUNCTION;

CREATE TRIGGER ins_test_data
    INSERT ON test_data
    FOR EACH ROW
    (
        EXECUTE FUNCTION sp_nextval('seq_test_data') INTO
            unique_id
    );

 
CREATE FUNCTION sp_nextval(name VARCHAR(128))​    RETURNING INT8 AS value;​    DEFINE value INT8;        PREPARE stm_nextval FROM 'SELECT ' || name || '.NEXTVAL';    DECLARE cur_nextval CURSOR FOR stm_nextval;    OPEN    cur_nextval;    FETCH   cur_nextval INTO value;    CLOSE   cur_nextval;    FREE    cur_nextval;​    RETURN value;​END FUNCTION;​CREATE TRIGGER ins_test_data    INSERT ON test_data    FOR EACH ROW    (        EXECUTE FUNCTION sp_nextval('seq_test_data') INTO            unique_id    );

The following can be used to test these solutions:

CREATE PROCEDURE sp_sleep(seconds INT);
    SYSTEM 'sleep ' || seconds;
END PROCEDURE;

INSERT INTO test_data (data_content) VALUES ('1.0');
INSERT INTO test_data (data_content) VALUES ('2.0');
CALL sp_sleep(2);
UPDATE test_data SET (data_content) = ('2.1') WHERE unique_id = 2;
SELECT * FROM test_data;

 
CREATE PROCEDURE sp_sleep(seconds INT);    SYSTEM 'sleep ' || seconds;END PROCEDURE;​INSERT INTO test_data (data_content) VALUES ('1.0');INSERT INTO test_data (data_content) VALUES ('2.0');CALL sp_sleep(2);UPDATE test_data SET (data_content) = ('2.1') WHERE unique_id = 2;SELECT * FROM test_data;

That should return output similar to this:

unique_id	data_content	last_update_time	last_update_user
1	1.0	2024-02-29 11:38:08	informix
2	2.1	2024-02-29 11:38:10	informix

Should you need to update a table without the tracking columns being changed, you would use SET TRIGGERS. There are two forms, as in the following examples:

-- Suspend all triggers on a table:
SET TRIGGERS FOR test_data DISABLED;
UPDATE test_data SET data_content = data_content || ' (updated)';
SET TRIGGERS FOR test_data ENABLED;

-- Suspend specific triggers (separate more than one with commas):
SET TRIGGERS upd_test_data DISABLED; -- specific trigger
UPDATE test_data SET data_content = data_content || ' (updated)';
SET TRIGGERS upd_test_data ENABLED;

 
-- Suspend all triggers on a table:SET TRIGGERS FOR test_data DISABLED;UPDATE test_data SET data_content = data_content || ' (updated)';SET TRIGGERS FOR test_data ENABLED;​-- Suspend specific triggers (separate more than one with commas):SET TRIGGERS upd_test_data DISABLED; -- specific triggerUPDATE test_data SET data_content = data_content || ' (updated)';SET TRIGGERS upd_test_data ENABLED;

Conclusion

This article more fully documents alternate approaches to updating a triggering table via a stored procedure or function.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.