SQL & PL/SQL

I have an AFTER INSERT trigger that attempts to summarize some data from the submitted row, and then UPDATE the row with the summarized figures.

I am getting ORA-04091 when I attempt to insert a new row.

I *could* insert the summarized data in a separate table that references the main table, but I would really like to understand the reason why the error is surfacing. I thought that if I was performing the update on an AFTER INSERT trigger, it should work?

Here's the code for my trigger.

Thank you.

Brian

---

create or replace trigger "SES_SCREENINGS_AI_SCORE"

AFTER

insert on "SES_SCREENINGS"

for each row

declare

    ln_emotional_scale_score                number(2);

    ln_Conduct_scale_score                  number(2);

    ln_hyper_scale_score                    number(2);

    ln_peer_scale_score                     number(2);

    ln_prosocial_scale_score                number(2);

    ln_total_difficulties_score             number(2);

    ln_externalizing_score                  number(2);

    ln_internalizing_score                  number(2);

    lv_emotional_score_category             varchar2(32);

    lv_conduct_score_category               varchar2(32);

    lv_hyper_score_category                 varchar2(32);

    lv_peer_score_category                  varchar2(32);

    lv_prosocial_score_category             varchar2(32);

    lv_total_difficulties_score_category    varchar2(32);

    lv_datasheet_required                   varchar2(1);

begin

    ln_emotional_scale_score     := :new.EMOTIONAL_1 + :new.EMOTIONAL_2 + :new.EMOTIONAL_3 + :new.EMOTIONAL_4 + :new.EMOTIONAL_5;

    ln_conduct_scale_score       := :new.CONDUCT_1 + :new.CONDUCT_2 + :new.CONDUCT_3 + :new.CONDUCT_4 + :new.CONDUCT_5;

    ln_hyper_scale_score         := :new.HYPER_1 + :new.HYPER_2 + :new.HYPER_3 + :new.HYPER_4 + :new.HYPER_5;

    ln_peer_scale_score          := :new.PEER_1 + :new.PEER_2 + :new.PEER_3 + :new.PEER_4 + :new.PEER_5;

    ln_prosocial_scale_score     := :new.PROSOCIAL_1 + :new.PROSOCIAL_2 + :new.PROSOCIAL_3 + :new.PROSOCIAL_4 + :new.PROSOCIAL_5;

    ln_total_difficulties_score  := ln_emotional_scale_score + ln_conduct_scale_score + ln_hyper_scale_score + ln_peer_scale_score;

    ln_externalizing_score       := ln_conduct_scale_score + ln_hyper_scale_score;

    ln_internalizing_score       := ln_emotional_scale_score + ln_peer_scale_score;

    -- categorize emotional_scale_score

    SELECT DESCRIPTION INTO lv_emotional_score_category

    FROM SES_CUTSCORES

    WHERE UPPER(SCREEN_TYPE) = UPPER(:new.screening_type)

    AND UPPER(SCORE_TYPE) = 'EMOTIONAL'

    AND UPPER(STATUS) = 'A'

    AND ln_emotional_scale_score BETWEEN LOW AND HIGH;

    -- categorize conduct_scale_score

    SELECT DESCRIPTION INTO lv_conduct_score_category

    FROM SES_CUTSCORES

    WHERE UPPER(SCREEN_TYPE) = UPPER(:new.screening_type)

    AND UPPER(SCORE_TYPE) = 'CONDUCT'

    AND UPPER(STATUS) = 'A'

    AND ln_conduct_scale_score BETWEEN LOW AND HIGH;

    -- categorize hyper_scale_score

    SELECT DESCRIPTION INTO lv_hyper_score_category

    FROM SES_CUTSCORES

    WHERE UPPER(SCREEN_TYPE) = UPPER(:new.screening_type)

    AND UPPER(SCORE_TYPE) = 'HYPER'

    AND UPPER(STATUS) = 'A'

    AND ln_hyper_scale_score BETWEEN LOW AND HIGH;

    -- categorize peer_scale_score

    SELECT DESCRIPTION INTO lv_peer_score_category

    FROM SES_CUTSCORES

    WHERE UPPER(SCREEN_TYPE) = UPPER(:new.screening_type)

    AND UPPER(SCORE_TYPE) = 'PEER'

    AND UPPER(STATUS) = 'A'

    AND ln_peer_scale_score BETWEEN LOW AND HIGH;

    -- categorize prosocial_scale_score

    SELECT DESCRIPTION INTO lv_prosocial_score_category

    FROM SES_CUTSCORES

    WHERE UPPER(SCREEN_TYPE) = UPPER(:new.screening_type)

    AND UPPER(SCORE_TYPE) = 'PROSOCIAL'

    AND UPPER(STATUS) = 'A'

    AND ln_prosocial_scale_score BETWEEN LOW AND HIGH;

    -- categorize total_difficulties_score

    SELECT DESCRIPTION INTO lv_total_difficulties_score_category

    FROM SES_CUTSCORES

    WHERE UPPER(SCREEN_TYPE) = UPPER(:new.screening_type)

    AND UPPER(SCORE_TYPE) = 'TOTAL'

    AND UPPER(STATUS) = 'A'

    AND ln_total_difficulties_score BETWEEN LOW AND HIGH;

    -- update the inserted row with calculated values

    UPDATE ses_screenings

    SET emotional_scale_score             = ln_emotional_scale_score,

        conduct_scale_score               = ln_conduct_scale_score,

        hyper_scale_score                 = ln_hyper_scale_score,

        peer_scale_score                  = ln_peer_scale_score,

        prosocial_scale_score             = ln_prosocial_scale_score,

        total_difficulties_score          = ln_total_difficulties_score,

        externalizing_score               = ln_externalizing_score,

        internalizing_score               = ln_internalizing_score,

        emotional_score_category          = lv_emotional_score_category,

        conduct_score_category            = lv_conduct_score_category,

        hyper_score_category              = lv_hyper_score_category,

        peer_score_category               = lv_peer_score_category,

        prosocial_score_category          = lv_prosocial_score_category,

        total_difficulties_score_category = lv_total_difficulties_score_category

    WHERE id = :new.id;

end;

/