How to get other row entries into variables when I trigger a dml statement in plsql?

Aryan Sharma 0
Viewed 22
CREATE OR REPLACE TRIGGER trigger_1 
BEFORE UPDATE OR DELETE ON lib_tab FOR EACH ROW
ENABLE
    DECLARE
        aud_bookname lib_tab.book_name%TYPE;
    BEGIN
        IF UPDATING THEN
            dbms_output.put_line('updating...');
        END IF;
    END;
/

TABLE:

BOOK_NAME         STATUS
------------------------- ---------------
DARK MATTER       AVAILABLE
SILENT HILL       UNAVAILABLE
GOD OF WAR        AVAILABLE
SPIDER-MAN        UNAVAILABLE
UNCHARTED         AVAILABLE

WHEN EXECUTING :

UPDATE lib_tab SET status = 'AVAILABLE' WHERE book_name = 'SILENT HILL';

I WANT TO PRINT THE NAME OF THE BOOK AS WELL OF WHICH THE STATUS IS GETTING UPDATED

I AM TRYING TO LEARN ORACLE SQL AND NEED TO USE TRIGGERS FOR AN ASSIGNMENT

I know the question is probably badly framed.

oracle-sqldeveloper plsqldeveloper sqlplus plsql
1 Answers

The error is triggered by your Select from table that has trigger activity in progress. The table is mutating the data you are selecting and the error warns you that the result of a select could be wrong. Instead of select try to put this

aud_bookname:= :old.book_name;

... if that is not what you are looking for then consider changing the things with After Update trigger. When the update is done you can select the data. Regards...

d r0
Related