1

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)
1

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)
           );
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.