I know it's a Trigger and I have this, but it generates an error:
create or replace trigger INSERTAR_HORA
AFTER insert on TAREA_SOPORTE
for each row
begin
UPDATE TAREA_SOPORTE
SET HORA_GENERADO= CURRENT_TIMESTAMP
WHERE TAREA_SOPORTE.ID_TAREA=:NEW.ID_TAREA;
end;
Help please.
What am I doing wrong?
You can not execute a statement that refers to the table to which the trigger belongs.
The correct way to do this in Oracle does not require a UPDATE at all, but it does require that you use a BEFORE trigger. In an AFTER trigger, you can not modify the values:
create or replace trigger INSERTAR_HORA
BEFORE insert on TAREA_SOPORTE
for each row
begin
:NEW.HORA_GENERADO := CURRENT_TIMESTAMP;
end;
Or better yet, do not use a trigger at all. Depending on the Oracle version you use, you can simply define a DEFAULT in your CREATE TABLE . Example:
create table tarea_soporte (
id_tarea number(10) not null primary key,
hora_generado timestamp default current_timestamp
)