0

While creating below trigger i am getting this error. Trigger script -

CREATE OR REPLACE TRIGGER myuser.tt_tab BEFORE DELETE ON dbo.tab
    REFERENCING
            OLD AS old
            NEW AS new
    FOR EACH ROW
DECLARE
    err_code   error_log.err_code%TYPE;
    err_msg    error_log.err_msg%TYPE;
    CURSOR c_old IS
    SELECT cd, office_id
    FROM dbo.tab_def def 
    WHERE :old.tab_id = def.tab_id AND :old.VALID_DATE_RANGE_FLAG = 'Y'; 

BEGIN
    FOR r_old IN c_old LOOP 
 -- Write delete to log table
        INSERT INTO myuser.trig_log
        (
            seq_nbr,
            id,
            cd,
            office_id,
            seq,
            address,
            dml_action,
            creation_date
        ) VALUES (
            myuser.trig_log_seq.NEXTVAL,
            :old.id,
            r_old.cd,
            r_old.office_id,
            null,
            :old.address,
            'DEL',
            sysdate
        );

    END LOOP;
EXCEPTION
--capture any exceptions
    WHEN OTHERS THEN
        err_code := sqlcode;
        err_msg := substr(sqlerrm, 1, 254);
        INSERT INTO myuser.error_log VALUES (
            error_log_seq.NEXTVAL,
            'tab',
            'tab',
            err_code,
            err_msg,
            sysdate
        );

END;

Trigger log Table definition -

DESCRIBE myuser.trig_log;
SEQ_NBR                           NUMBER       
ID                      NUMBER       
cd                         VARCHAR2(3)  
OFFICE_ID                   NUMBER       
SEQ       NUMBER       
ADDRESS            NUMBER       
DML_ACTION                        VARCHAR2(4)  
CREATION_DATE                     TIMESTAMP(6)

Error -

Error(11,5): PL/SQL: SQL Statement ignored
Error(16,9): PL/SQL: ORA-00942: table or view does not exist
Error(27,9): PL/SQL: SQL Statement ignored
Error(40,13): PLS-00364: loop index variable 'R_OLD' use is invalid
Error(40,26): PL/SQL: ORA-00984: column not allowed here

Things i ensured, myuser have select access on tab, tab_def. I can create similar trigger in another environment where i am owner of all the objects.
I observed, if i remove cursor portion from code, trigger compiles successfully. I also removed joins :old.tab_id = def.tab_id, added join with tab and tab_def in cursor but they dint work. Please guide me - what i am doing wrong.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • 4
    "I ensured, myuser have select access on tab, tab_def" - granted directly to your user, or via a role? – Alex Poole Sep 12 '21 at 08:28
  • I think select is granted via role because I can not see direct grant given in grants tab in sql developer. `tab_def` table has one less grants as well. – Koushik Roy Sep 12 '21 at 09:55
  • I added DB version tag too. Pls let me know if anymore questions. – Koushik Roy Sep 12 '21 at 09:57
  • Stored PL/SQL doesn't see role privileges. It needs to be granted directly. – William Robertson Sep 12 '21 at 10:03
  • Thanks for the answer. So, i understand, if i have to write some pl/sql procs on 50tables, dba has to go and grant privileges on each table individually? Can it not be some role based grant – Koushik Roy Sep 13 '21 at 05:12

1 Answers1

0

Your script has “ INSERT INTO myuser.” i.e. you are missing the table name

NickW
  • 8,430
  • 2
  • 6
  • 19