I have a table (table a) that gets an insert done on it. After that insert I have a trigger that executes a stored procedure.
that procedure has to do some inserts on some other tables with the data from table a.
How Can I access the data that was inserted into table a with the procedure before the trigger ends ?
create or replace
TRIGGER TRIGGER1
after INSERT ON USER_GROUPS
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
USERS;
END;
----------------------------
CREATE VIEW NEW_ID_VW AS
SELECT NVL(MAX(USER_ID),0) AS NEW_ID FROM TABLE2_USERS;
---------------------------
create or replace PROCEDURE "USERS" IS
VID NUMBER;
BEGIN
select user_id into VID from USER_GROUPS;
EXECUTE IMMEDIATE '
INSERT INTO TABLE2_USERS (USER_ID,USERNAME)
select a.new_id+1,b.USER_ID from NEW_ID_VW a, USER_GROUPS b';
delete from USER_GROUPS where USER_ID = VID; --I want to delete each record processed after the procedure runs.
END;
-----------------
I get this error when inserting into the tbl
Error starting at line 26 in command:
INSERT INTO USER_GROUPS (U_ID,USER_ID)
SELECT USER_GROUPS_SEQ.NEXTVAL , '999999999' USER_ID FROM DUAL
Error report:
SQL Error: ORA-01403: no data found
ORA-06512: at "USERS", line 10
ORA-06512: at "TRIGGER1", line 7
ORA-04088: error during execution of trigger 'TRIGGER1'
01403. 00000 - "no data found"
*Cause:
*Action: