I am trying to compile the below code at time of compiling i am getting errors stating as 😕👇
CREATE
OR REPLACE TRIGGER A_TRI BEFORE
DELETE
OR INSERT
OR
UPDATE
ON tri FOR EACH ROW
DECLARE v_user VARCHAR2(50);
v_Date VARCHAR2(50);
BEGIN
SELECT
USER,
to_char(sysdate, 'DD/MON/YYYY H24:MM:SS') INTO v_user,
v_date
FROM
DUAL;
IF INSERTING
THEN
INSERT INTO
tri_auditing (ID, LAST_UPDATE_DATE, LAST_UPDATE_BY_OLD, LAST_UPDATED_BY_NEW, ACTION)
VALUES
(
tri_auditing_seq.NEXTVAL, v_date, NULL, :NEW.v_user, 'inserting'
)
;
DBMS_OUTPUT.PUT_LINE(v_user || ' is inserting');
ELSIF UPDATING
THEN
INSERT INTO
tri_auditing (ID, LAST_UPDATE_DATE, LAST_UPDATE_BY_OLD, LAST_UPDATED_BY_NEW, ACTION)
VALUES
(
tri_auditing_seq.NEXTVAL, v_date, :OLD.v_user, :NEW.v_user, 'updating'
)
;
DBMS_OUTPUT.PUT_LINE(v_user || ' is updating');
ELSIF DELETING
THEN
INSERT INTO
tri_auditing (ID, LAST_UPDATE_DATE, LAST_UPDATE_BY_OLD, LAST_UPDATED_BY_NEW, ACTION)
VALUES
(
tri_auditing_seq.NEXTVAL, v_date, :OLD.v_user, :NEW.v_user, 'deleting'
);
DBMS_OUTPUT.PUT_LINE(v_user || ' is deleting');
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid event');
END IF;
END;
I have tried to alter auditing table as well but not getting any results.
errors i given below
-
Error(12,57): PLS-00049: bad bind variable ‘NEW.V_USER’
-
Error(17,51): PLS-00049: bad bind variable ‘OLD.V_USER’
-
Error(17,64): PLS-00049: bad bind variable ‘NEW.V_USER’
-
Error(22,51): PLS-00049: bad bind variable ‘OLD.V_USER’
-
Error(22,64): PLS-00049: bad bind variable ‘NEW.V_USER’
Sona Vijapure is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.