Informix Error -19824: Cannot use BEFORE or AFTER action with INSTEAD OF trigger.
Cause and resolution
Cannot use BEFORE or AFTER action with INSTEAD OF trigger.
You cannot use BEFORE or AFTER with the INSTEAD OF trigger. You must use FOR EACH ROW with an INSTEAD OF trigger.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-19824 fires when INSTEAD OF is combined with BEFORE/AFTER in a CREATE TRIGGER
statement — per the official guidance. An INSTEAD OF trigger must use FOR EACH ROW.
BEFORE/AFTERused withINSTEAD OF, per the official guidance — the direct, only cause.
Solutions / Resolution
- Use
FOR EACH ROWwith theINSTEAD OFtrigger, per the official guidance, instead ofBEFORE/AFTER.
Examples
INSTEAD OF combined with BEFORE
CREATE TRIGGER mytrig INSTEAD OF BEFORE INSERT ON myview ...
Corrected
CREATE TRIGGER mytrig INSTEAD OF INSERT ON myview REFERENCING NEW AS n FOR EACH ROW ...
Diagnostic Checks
- Check the
CREATE TRIGGERstatement forBEFORE/AFTERcombined withINSTEAD OF, and replace withFOR EACH ROW.
Related Errors / Related Topics
- -19823 — "Cannot use EXECUTE PROCEDURE INTO with the INSTEAD OF trigger." A related
INSTEAD OF-trigger restriction, about the action rather than the timing. - -19825 — "Cannot use WHEN clause if INSTEAD OF trigger is defined." A related
INSTEAD OF-trigger restriction, about theWHENclause rather than timing.
BEFORE/AFTER can't be combined with INSTEAD OF — use FOR EACH ROW instead.