I want to create a trigger that is executed when a record is updated, deleted or inserted. I have tried:
create or replace trigger "ADDRESS_ID_BEFORE_INSERT"
BEFORE
insert or update or delete on "ADDRESS_ID"
for each row
DECLARE
v_usuario nvarchar2(100);
accion nvarchar2(100);
v_fecha date;
begin
SELECT sysdate INTO v_fecha from dual;
SELECT user INTO v_usuario FROM dual;
IF UPDATING THEN
INSERT INTO auditoria (ACCION_REALIZADA,FECHA_ACCION,NOMBRE_USUARIO)values('Actualizar',v_fecha,v_usuario);
ELSEIF DELETING THEN
INSERT INTO auditoria (ACCION_REALIZADA,FECHA_ACCION,NOMBRE_USUARIO)values('Elimnar',v_fecha,v_usuario);
ELSE
INSERT INTO auditoria (ACCION_REALIZADA,FECHA_ACCION,NOMBRE_USUARIO)values('Insertar',v_fecha,v_usuario);
END IF;
end;
What I should do is insert the action that the user is doing in another table, but I get the following error:
Compilation failed, line 11 (23:45:39) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers. PLS-00103: Encountered the symbol "DELETING" when expecting one of the following: := . ( @ % ; Compilation failed, line 16 (23:45:39) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers. PLS-00103: Encountered the symbol ";" when expecting one of the following: if
1-In the PL/SQL language the nested IF statement is used ELSIF . 2-To audit, the AFTER
event must be used, which is when there is already a definitive value in each column.
Oracle documentation on IF THEN ELSIF Statement