Compound Trigger is Triggering Only Once-CodePudding

I have an ORDS enabled schema which accepts bulk JSON records and splits them one by one, and inserts them one by one into UEM table.

I've tried to create a trigger which fetches the last inserted row's id and use that value to insert into another table. The problem is, the trigger below only fetches and inserts the last inserted row's id, and it only does 1 insert.

To be more specific:

ORDS gets a bulk JSON payload which consists of 4 records.
POST handler starts a Procedure which splits these 4 records by line break, and immediately inserts these to CLOB columns of UEM table as 4 separate rows by using "connect by level". There is also the ID column which is automatically created and incremented.
In the parallel I also would like to get the ID of these rows and use it in another table insert. I've created the compound trigger below, but this trigger only retrieves the ID of the last record, and inserts only one row.

Why do you think it behaves like this? In the end, the procedure "inserted" 4 records.

CREATE OR REPLACE TRIGGER TEST_TRIGGER5
FOR INSERT ON UEM
COMPOUND TRIGGER
    lastid NUMBER;
    AFTER STATEMENT IS
        BEGIN
            SELECT MAX(ID) INTO lastid FROM UEM;
            INSERT INTO SPRINT1 (tenantid, usersessionid, newuser, totalerrorcount, userid) VALUES ('deneme', 'testsessionid', 'yes', lastid, 'asdasfqwqwe');
        END AFTER STATEMENT;
END TEST_TRIGGER5;

CodePudding user response：

Why? Because it is a statement level trigger. If you wanted it to fire for each row, you'd - obviously - use a row level trigger which has the

for each row

clause.

CodePudding user response：

inserts these to CLOB columns of UEM table as 4 separate rows by using "connect by level".

You have 1 INSERT statement that is inserting 4 rows.

In the parallel I also would like to get the ID of these rows and use it in another table insert. I've created the compound trigger below, but this trigger only retrieves the ID of the last record, and inserts only one row.

Why do you think it behaves like this? In the end, the procedure "inserted" 4 records.

It may have inserted 4 ROWS but there was only 1 STATEMENT and you are using an AFTER STATEMENT trigger. If you want it to run for each row then you need to use a row-level trigger.

CREATE OR REPLACE TRIGGER TEST_TRIGGER5
AFTER INSERT ON UEM
  FOR EACH ROW
BEGIN
  INSERT INTO SPRINT1 (tenantid, usersessionid, newuser, totalerrorcount, userid)
  VALUES ('deneme', 'testsessionid', 'yes', :NEW.id, 'asdasfqwqwe');
END TEST_TRIGGER5;
/

db<>fiddle here