I am having an issue in creating a trigger

0
votes
create trigger audit_trail01
    after update or delete on cust_mstr
    for each row
declare
    open varchar2(8);
begin
    if updating then
        open := 'UPDATE';
    elsif deleting then
        open := 'Delete';
    end if;

    insert into audit_cust
        ( cust_no
        , fname
        , mname
        , lname
        , dob_inc
        , occup
        , pancopy
        , fqrm60
        , operation
        , userid
        , opdate )
    values
        ( :old.cust_no
        , :old.fname
        , :old.mname
        , :old.lname
        , :old.dob_inc
        , :old.occup
        , :old.pancopy
        , :old.fqrm6o
        , open
        , user
        , sysdate );
end;

I have written the above but it is showing me the below error:

Errors:

TRIGGER AUDIT_TRAIL01 Line/Col: 5/8
PLS-00103: Encountered the symbol "=" when expecting one of the following:
mod bind variable> continue current sql execute forall merge pipe purge json_exists json_value json_query json_object json_array

1
oracleplsql
Error clearly shows Line 5 which is OPEN VARCHAR2(8); So what are you not able to understand? OPEN is a reserved keyword in Oracle. Use a proper variable name. – Lalit Kumar B
OPEN is reserved keyword in Oracle. You can't use it as a variable.Try replacing,check the answer. – VN'sCorner
@dishant I already told you what's the problem. Try it and come back whether it worked or not. – Lalit Kumar B

1 Answers

0
votes

OPEN is reserved keyword, you cannot use it for variable. Changed OPEN to lv_OPEN. Refer oracle documentation for oracle reserved words for PL/SQL. https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/reservewords.htm#LNPLS019

CREATE TRIGGER AUDIT_TRAIL01
    AFTER UPDATE OR DELETE ON CUST_MSTR 
    FOR EACH ROW 
    DECLARE 
    lv_OPEN VARCHAR2(8); 
    BEGIN 
    IF UPDATING THEN
      lv_OPEN := 'UPDATE'; 
    ELSIF DELETING THEN
      lv_OPEN := 'Delete'; 
    END IF;
    INSERT  INTO AUDIT_CUST (CUST_NO,FNAME,MNAME,LNAME,DOB_INC,OCCUP,PANCOPY,FQRM60,OPERATION,USERID,OPDATE)
    VALUES(:OLD.CUST_NO,:OLD.FNAME, :OLD.MNAME,:OLD.LNAME, :OLD.DOB_INC,:OLD.OCCUP ,:OLD.PANCOPY, :OLD.FQRM6O,lv_OPEN,USER,SYSDATE); 

    END;