measure_todo_triggers.sql

        WITH todos AS (
            INSERT INTO todo
                (individual_id,
                 requirement_id,
                 status,
                 title,
                 time_period__id,
                 expected_completion_date,
                 study_id,
                 asset_type__id,
                 num_required_entries,
                 acquire_channel__id)
                SELECT NEW.individual_id,
                       r.id,
                       'not-started',
                       r.title,
                       tp.id,
                       NEW.enrollment_date
                           + coalesce(tp.days_from_enrollment_offset,0),
                       NEW.study_id,
                       r.asset_type__id,
                       r.num_required_entries,
                       r.acquire_channel__id
                FROM requirement r
                    INNER JOIN requirement_list rl
                        ON (r.requirement_list__id = rl.id)
                    INNER JOIN asset_type at ON (at.id = r.asset_type__id)
                    INNER JOIN time_period tp ON (rl.time_period__id = tp.id)
                WHERE at.code = 'measure'
                    AND rl.study_id = NEW.study_id
                    AND rl.participant_group__id = NEW.participant_group__id
                RETURNING id, requirement_id
        )
        WITH tmp1 AS (
            INSERT INTO measure_type_x_todo (measure_type__id, todo_id)
            SELECT m.measure_type__id,
                   t.id AS todo_id
                FROM todos t INNER JOIN measure_type_x_requirement m
                     ON (t.requirement_id = m.requirement_id)
            RETURNING *
        )
        INSERT INTO sample_type_x_todo (sample_type__id, todo_id)
        SELECT m.sample_type__id,
               t.id AS todo_id
            FROM todos t INNER JOIN sample_type_x_requirement m
                 ON (t.requirement_id = m.requirement_id)
        RETURNING *;