Skip to content
Advertisement

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 –


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:

Then the trigger:

Which, for the sample data:

Then after:

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

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement