Informix Trigger to Delete Records Before Insert

informix

I am trying to create a trigger to simply delete records from systables_growth table before an insert happens based on the where clause used on the date_added column for records older than a certain number of days (in this example, 15 days):

I am using the below:

CREATE TRIGGER systables_growth_clnup
INSERT ON systables_growth
DELETE FROM systables_growth
WHERE date_added < (CURRENT - 15 UNITS DAY)

I am getting the below syntax error when performing the above.

CREATE TRIGGER systables_growth_clnup
INSERT ON systables_growth
DELETE FROM systables_growth
#^
#  201: A syntax error has occurred.
#
WHERE date_added < (CURRENT - 15 UNITS DAY)

Best Answer

If you follow the syntax diagram(s) for CREATE TRIGGER, you find that the action clause needs either BEFORE, FOR EACH ROW or AFTER as a prefix before the action verbiage, and the triggered action segment shows you need parentheses around the triggered action(s). Hence:

CREATE TRIGGER systables_growth_clnup
    INSERT ON systables_growth
    BEFORE (DELETE FROM systables_growth
            WHERE date_added < (CURRENT - 15 UNITS DAY)
           );
Related Question