Trigger is not working 1

[Mun]

I'm trying to witr a gtrigger on table called TRUCK,when TRK_IS_ACTIVE='F' I want to update s field TRK_STOP_DATE to SYSDATE.


CREATE OR REPLACE TRIGGER UPDATETRUCK
BEFORE UPDATE OF TRK_IS_ACTIVE ON TRUCK
BEGIN
IF TRK_IS_ACTIVE = 'F' THEN
UPDATE TRUCK SET TRK_STOP_DATE = 'SYSDATE';
END IF;
END;


THis trigger is not working. Can some one give me an idea.

Thanks

 

[AOLEURO]

Hi Mun

You can’t make a trigger working on rows from the same table you are updating – you need to work on each updating row with a row trigger.

Instead do this:

create or replace trigger updatetruck
before update of trk_is_active on truck

for each row

begin
          IF

:NEW.

trk_is_active = 'F' THEN

:NEW.

trk_stop_date := trunk(sysdate);
          END IF;
end;

You need to use a FOR EACH ROW the control each row you are updating.

The :NEW. tells Oracle that you want to work with the NEW values on the row you are updating. You can also use :OLD. to compare old before updating values on the row.

I used TRUNC(sysdate) so trk_stop_date is a date column and is a whole day without time of day.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[Mun]

Thanks Allan,

I'm able to create a trigger with your help. But when I'm trying to update the table it's giving me an error.

ERROR at line 1:
ORA-04091: table KEITH.CREW is mutating, trigger/function may not see it
ORA-06512: at "KEITH.BU_R_ON_CREW", line 3
ORA-04088: error during execution of trigger 'KEITH.BU_R_ON_CREW'

Thanks for your help.

 

[AOLEURO]

Hi Mun

Oracle says - trigger KEITH.BU_R_ON_CREW


It is not the trigger I helped you with.

You must have more than one trigger on the table.

If it is the trigger, then list the trigger - because my trigger example is working. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !