Informix Error -747

Reasons / Common Causes

-747 fires in two related situations, per the official guidance: a triggered SQL statement acts on the same table that fired the trigger, or, when both the triggering statement and the triggered action are UPDATEs, the triggered action updates the same column the triggering statement updates.

1. A triggered action's statement referencing the triggering table itself, per the official guidance — for example, an UPDATE trigger's action attempting to UPDATE the same table again directly (rather than through the row values already available via REFERENCING).
2. Both the triggering UPDATE and the triggered UPDATE targeting the same column, per the official guidance — even if the tables differ, if this scenario applies it's the column overlap that matters.
3. A trigger design that assumes it can freely re-modify the row that fired it, rather than using the trigger's REFERENCING values or a separate table for that purpose.

Solutions / Resolution

1. Don't have a triggered action modify the same table (and, for UPDATE-on-UPDATE, the same column) that fired the trigger — per the official guidance, restructure the logic instead.
2. Use the trigger's REFERENCING NEW/OLD values directly in the triggered action rather than re-querying or re-modifying the triggering table.
3. If a genuinely different column on the same table needs updating, confirm it doesn't overlap with the triggering statement's own updated column(s).

Examples

The disallowed self-reference

CREATE TRIGGER trg_orders_update UPDATE OF status ON orders
  REFERENCING NEW AS post FOR EACH ROW
  (UPDATE orders SET status = 'reviewed' WHERE order_id = post.order_id);
-- -747: the triggered action updates the same column (status) the
-- triggering UPDATE updates, on the same table

Corrected — update a different, non-overlapping column

CREATE TRIGGER trg_orders_update UPDATE OF status ON orders
  REFERENCING NEW AS post FOR EACH ROW
  (UPDATE orders SET last_status_change = CURRENT WHERE order_id = post.order_id);

Diagnostic Checks

1. Check whether the triggered action's statement targets the same table the trigger fired on, and if both are UPDATEs, whether their column lists overlap.

Related Errors / Related Topics

• -734 — "Object name matches old or new values correlation name." A related trigger-naming restriction, about a correlation-name collision rather than a self-referencing triggered action.
• -744 — "Illegal SQL statement in trigger." A related trigger-action restriction, on a specific list of disallowed statement types rather than self-reference.

Restructure the triggered action to avoid modifying the same table/column that fired the trigger — use the REFERENCING values directly instead.