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?
Advertisement
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