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:

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:

That fails with:

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:

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:

The standard approach would then be:

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

The following can be used to test these solutions:

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:

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.