Is there any way we can emulate the way of using :old and :new of row triggers in statement triggers in PL/SQL

Good Day Buddies!

So, here is my Question, it says –

Write a update, delete trigger on clientmstr table. The System should keep track of the records that ARE BEING updated or deleted. The old value of updated or deleted records should be added in audit_trade table. (Separate implementation using both row and statement triggers)

And my solution looks like this –

-- For row trigger
create or replace trigger row_trigger
    before delete or update on client_master
    referencing old as old new as new
    for each row
begin
    insert into audit_table values(
    :old.client_id, :old.client_name, :old.client_budget
    );
end;
/

---

And as per the question I have to implement the same using statement trigger but I couldn’t think of a way it can be done. I studied about statement triggers and I learned that we can’t use :old and :new here. Is there any way we can implement the same row trigger method of adding in audit table using statement trigger? I am just starting out and it’s just been two days I started learning PL/SQL. I spend whole day searching everywhere on the internet – tried looking for an example but I am not getting it. Can anyone help?

---

Edit

(1) I am using Oracle SQL Developer

(2) As someone suggested in comments – it isn’t possible to do this in statement trigger, I think the same. I have to submit my assignment this Saturday. I had a conversation with my teacher – she said it’s possible to implement it using statement trigger. I asked her how – but she didn’t responded. Then I asked her for a hint and she said this (I’m copy pasting her text)-

Create a separate table with col as operations and timestamp. Write statement level trigger on insert update and delete operations. The trigger will capture the operation fired and timestamp by inserting values in table.

I am not getting what does that mean or how to do it! Can anyone help me solve this?

Answer

You could use a compound trigger.

Create the types:

CREATE TYPE client_master_obj IS OBJECT(
  id     NUMBER,
  name   VARCHAR2(20),
  budget NUMBER(10,2)
);

CREATE TYPE client_master_table IS TABLE OF client_master_obj;

Then the trigger:

CREATE TRIGGER client_master_cmp_trigger
FOR DELETE OR UPDATE ON client_master
COMPOUND TRIGGER
  data client_master_table := client_master_table();
AFTER EACH ROW
  IS
  BEGIN
    data.EXTEND(1);
    data(data.COUNT) := client_master_obj(
                          :OLD.client_id,
                          :OLD.client_name,
                          :OLD.client_budget
                        );
  END AFTER EACH ROW;
AFTER STATEMENT
  IS
  BEGIN
    INSERT INTO audit_table (client_id, client_name, client_budget, trg_type)
    SELECT id,
           name,
           budget,
           'C'
    FROM   TABLE(data);
  END AFTER STATEMENT;
END;
/

Which, for the sample data:

CREATE TABLE client_master (client_id, client_name, client_budget) AS
SELECT 1, 'Alice', 100 FROM DUAL UNION ALL
SELECT 2, 'Beryl', 200 FROM DUAL UNION ALL
SELECT 3, 'Carol', 300 FROM DUAL UNION ALL
SELECT 4, 'Debra', 400 FROM DUAL UNION ALL
SELECT 5, 'Emily', 500 FROM DUAL;

CREATE TABLE audit_table (client_id, client_name, client_budget, trg_type) AS
SELECT cm.*, 'X' FROM client_master cm WHERE 1 = 0;

Then after:

UPDATE client_master
SET   client_budget = client_budget + 600
WHERE client_id IN (1, 2);

DELETE FROM client_master WHERE client_id IN (1, 3);

Then the audit table contains (with the row trigger also firing for the same changes):

SELECT * FROM audit_table;

CLIENT_ID	CLIENT_NAME	CLIENT_BUDGET	TRG_TYPE
1	Alice	100	R
2	Beryl	200	R
1	Alice	100	C
2	Beryl	200	C
1	Alice	700	R
3	Carol	300	R
1	Alice	700	C
3	Carol	300	C

db<>fiddle here