Maxim Demenko wrote:
> Jürg Schaufelberger schrieb:
>
>> Hello
>>
>> I work with oracle 8.1.7 and I wrote the following trigger and
>> compiled it successfully:
>>
>> CREATE OR REPLACE TRIGGER
>> AV_BB_EINZELPUNKT_EXAKTDEF
>> AFTER INSERT OR UPDATE OF ID_ART
>> ON AV_BB_EINZELPUNKT
>> FOR EACH ROW
>> BEGIN
>> IF :NEW.ID_ART IN (0) THEN
>> UPDATE AV_BB_EINZELPUNKT SET ID_EXAKTDEFINIERT = 0;
>> ELSE
>> UPDATE AV_BB_EINZELPUNKT SET ID_EXAKTDEFINIERT = 1;
>> END IF;
>> END;
>>
>> then I executed the SQL statement:
>>
>> UPDATE AV_BB_EINZELPUNKT set id_art = 0
>> where fid = 1;
>>
>> and got the following error
>>
>> FEHLER in Zeile 1:
>> ORA-04091: Tabelle AV_CHA.AV_BB_EINZELPUNKT wird gerade geändert,
>> Trigger/Funktion darf es nicht sehen
>> ORA-06512: in "AV_CHA.AV_BB_EINZELPUNKT_EXAKTDEF", Zeile 3
>> ORA-04088: Fehler bei der Ausführung von Trigger
>> 'AV_CHA.AV_BB_EINZELPUNKT_EXAKTDEF'
>>
>> What's wrong ? Any idea ? Thanks for answers.
>>
>>
>>
>
> Instead of update you should do assignment to given fields of :NEW record
>
> CREATE OR REPLACE TRIGGER
> AV_BB_EINZELPUNKT_EXAKTDEF
> AFTER INSERT OR UPDATE OF ID_ART
> ON AV_BB_EINZELPUNKT
> FOR EACH ROW
> BEGIN
> IF :NEW.ID_ART IN (0) THEN
> :NEW.ID_EXAKTDEFINIERT := 0;
> ELSE
> :NEW.ID_EXAKTDEFINIERT := 1;
> END IF;
> END;
>
> Best regards
>
> Maxim
Unless he actually means up update multiple records.
In that case, a second trigger (after statement) would be
needed to handle that. Part of the standard recipe for
mutating tables...
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Thu Sep 15 2005 - 14:40:26 CDT
