CODEWITHSUNDEEP
oracle-databaseoracle11goracle10goracle-sqldeveloperdatabase-trigger
user10606387
user10606387

Reputation:

How can I get the inserted primary key value from AFTER INSERT trigger in Oracle?

My Oracle DB has a table DOC_WF_COMM and its primary key is DWFC_ID. Primary key value is based on a sequence called SQ_DOC_WF_COMM.

I have created a row level AFTER INSERT trigger on that table and inside the trigger I need to join the inserted record with some other tables like this:

create or replace TRIGGER TRG_DOC_WF_COMM_AFT_INS AFTER INSERT ON DOC_WF_COMM REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
L_SUBJECT VARCHAR2(300);
L_BODY CLOB;

L_PNT_CODE VARCHAR(100) := NULL;
L_DR_PRJ_ID NUMBER(12);
L_STR_EMAIL VARCHAR2(120);
L_DWFC_TO_USR_ID VARCHAR2(12);

L_PNT_ID NUMBER(12);
L_PNT_EMAIL_YN VARCHAR(1);
L_PNT_ACTIVE_YN VARCHAR(1);
L_PNT_NOTIFY_YN VARCHAR(1);

BEGIN
    IF INSERTING THEN
        L_PNT_CODE := 'WFNT_MESSAGE';


        SELECT DR_PRJ_ID, STR_EMAIL, DWFC_TO_USR_ID INTO L_DR_PRJ_ID, L_STR_EMAIL, L_DWFC_TO_USR_ID
        FROM DOC_WF_COMM
        JOIN DOC_WF_USERS ON DWFU_ID = DWFC_DWFU_ID
        JOIN DOC_WORKFLOW ON DWF_ID = DWFU_DWF_ID
        JOIN DOCUMENT_REF ON DR_ID = DWF_DR_ID
        JOIN ST_REGISTER ON STR_ID = DWFU_STR_ID
        WHERE DWFC_ID = :NEW.DWFC_ID AND DWFC_RESPONSE IS NULL;

        -- SOME QUERIES HERE
    END IF;
END;

The trigger is compiled successfully and when I insert record into DOC_WF_COMM table I get this error:

ORA-01403: no data found ORA-06512

The error is :NEW.DWFC_ID in WHERE clause and I have change it to these values:

But no any luck. Any idea why this error is and how can I resolve it?

Upvotes: 1

Views: 1060

Answers (3)

user10606387
user10606387

Reputation:

I changed the query inside the trigger to this, it is working fine

SELECT STR_PRJ_ID, STR_EMAIL, :NEW.DWFC_TO_USR_ID INTO L_DR_PRJ_ID, L_STR_EMAIL, L_DWFC_TO_USR_ID
FROM DOC_WF_USERS, ST_REGISTER
WHERE :NEW.DWFC_TO_USR_ID = DWFU_US_ID AND DWFU_STR_ID = STR_ID AND DWFU_ID = :NEW.DWFC_DWFU_ID;

Not sure why is that. If anyone can figure out the mistake in the query given in the question, please let me know. Thanks

Upvotes: 0

APC
APC

Reputation: 146219

The problem is this line in your trigger:

PRAGMA AUTONOMOUS_TRANSACTION;

That means the trigger executes as an isolated transaction in a separate session, which means it cannot see the uncommitted state of any other session. Crucially this includes the session which fires the trigger, so the autonomous transaction cannot see the record you just inserted. Hence, NO_DATA_FOUND.

You haven't posted the whole trigger or explained what you're trying to do, so only you know why you have included the PRAGMA. However, the chances are you don't need it. Remove the PRAGMA (and the COMMIT) and your trigger should work just fine.

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142705

If I understood you correctly, create a local variable and put the next sequence value in there. Then it can be referenced throughout the code, always having the same value. Something like this:

declare
  l_seq number := my_seq.nextval;
begin
  insert into table_a (id, ...) values (l_seq, ...);
  update table_b set id = l_seq where ...
  select ... into ... from ... where id = l_seq;
end;

Upvotes: 0

Related Questions