You can use the actual values of :new and :old only in the FOR EACH ROW
triggers.
But you have no simple access to the triggered table from the trigger, so
you can't simple select from your STUDENTS table from tre your trigger
defined on that table. You will got "mutating table" effect.
You may wanna play around "autonomus transactions" to solve that troubles.
"Flex" <flexNOSP_at_Mfreemail.it> wrote in message
news:Qf0U7.18137$o9.668942_at_news1.tin.it...
> Why I cannot use the variable NEW in this kind of trigger
>
> CODE TRIGGER
>
> CREATE or REPLACE TRIGGER t_nstudents
> BEFORE
> INSERT OR UPDATE OF ID_Cycle ON Students
>
> declare
> actual_s NUMBER ;
> available_places NUMBER ;
> begin
> SELECT COUNT(*) INTO actual_s
> FROM students
> WHERE ID_Cycle = :new.ID_Cycle ;
>
> SELECT availableplaces INTO available_places
> FROM cycles
> WHERE cyclenumber = :new.ID_Cycle;
>
> IF actual_s < available_places THEN
> RAISE_APPLICATION.ERROR(-2061,'Cannot insert student into this cycle
> ,please check available places & Student');
> END IF ;
> end;
> /
>
>
> ERRORE SQLPLUS
> SQL> @trigger
> Input trunc on character 1
> Immettere un valore per student: 17
> vecchio 17: RAISE_APPLICATION.ERROR(-2061,'Cannot insert student into
> this cycle ,please check available places & Student');
> nuovo 17: RAISE_APPLICATION.ERROR(-2061,'Cannot insert student into
> this cycle ,please check available places 17');
> CREATE or REPLACE TRIGGER t_nstudents
> *
> ERROR row 1:
> ORA-04082: reference NEW or OLD
> SQL>
>
>
> --
> Hello
> Flex
>
>
Received on Wed Dec 19 2001 - 13:39:45 CST
