stefanuddenberg
3/7/2020 - 10:43 PM

SQL -- Trigger examples

-- Note that these don't work as intended because triggers only 
-- operate on updated rows
CREATE OR REPLACE FUNCTION drop_old_participants()
RETURNS TRIGGER AS $BODY$
BEGIN
    NEW.num_timeouts = NEW.num_timeouts + 1;
    NEW.in_progress_rating = FALSE;
    NEW.in_progress_worker_id = NULL;
    NEW.in_progress_rating_timestamp = NULL;
    RAISE NOTICE 'DROPPED PARTICIPANT % with timestamp %', OLD.in_progress_worker_id, OLD.in_progress_rating_timestamp;
    RETURN NEW;
END;
$BODY$ language 'plpgsql';

DROP TRIGGER IF EXISTS drop_old_participants_before_update ON participants;
CREATE TRIGGER drop_old_participants_before_update
    BEFORE UPDATE
    ON participants
    FOR EACH ROW
    WHEN (NOW() - OLD.in_progress_rating_timestamp > interval '1 hour')
    EXECUTE PROCEDURE drop_old_participants();