Whereas the previous tutorial was more focused on how to create and use Before Insert, Update and Delete DML triggers with simple and easy to understand examples, today’s tutorial on the other hands will be slightly more complex as we will learn how to perform table auditing in Oracle Database using DML triggers.

Table Auditing

Table auditing means keeping a track of all the dml activities performed on a specific table of the database for example which user Inserted, updated or deleted a row from the table and when. It is like spying on the users who are messing your table’s data.

Example

For the demonstration we will use the table ‘Superheroes’ which we created in the previous tutorial.

Suppose you want to keep an eye on the users who are inserting, updating or deleting data from the ‘Superheroes’ table. Let’s see how we can achieve this. To do so we will need another table in which we can journal the auditing data entries.

CREATE TABLE sh_audit(
  new_name varchar2(30),
  old_name varchar2(30),
  user_name varchar2(30),
  entry_date varchar2(30),
  operation  varchar2(30)
);

This table sh_audit has 5 columns which will store the auditing information such as the new data inserted or updated, old data which is updated or deleted from the table, name of the user who did it along with the date and time, also the type of DML operation performed.

Next we will write a trigger on the source table superheroes and will store the data into the auditing table sh_audit.

CREATE OR REPLACE trigger superheroes_audit
BEFORE INSERT OR DELETE OR UPDATE ON superheroes
FOR EACH ROW
ENABLE
DECLARE
  v_user varchar2 (30);
  v_date  varchar2(30);
BEGIN
  SELECT user, TO_CHAR(sysdate, 'DD/MON/YYYY HH24:MI:SS') INTO v_user, v_date  FROM dual;
  IF INSERTING THEN
    INSERT INTO sh_audit (new_name,old_name, user_name, entry_date, operation) 
    VALUES(:NEW.SH_NAME, Null , v_user, v_date, 'Insert');  
  ELSIF DELETING THEN
    INSERT INTO sh_audit (new_name,old_name, user_name, entry_date, operation)
    VALUES(NULL,:OLD.SH_NAME, v_user, v_date, 'Delete');
  ELSIF UPDATING THEN
    INSERT INTO sh_audit (new_name,old_name, user_name, entry_date, operation) 
    VALUES(:NEW.SH_NAME, :OLD.SH_NAME, v_user, v_date,'Update');
  END IF;
END;
/

I would highly suggest you to watch the YouTube Video Tutorial on the same topic since there I have explained the working of this particular trigger line by line in detail.

On successful compilation this trigger will insert a row containing auditing data such as the data inserted, updated and deleted from the source table superheroes along with the username who tampered the data as well as the date and time when it was done and also the name of DML statement executed by user to tamper the data of your table.

Pseudo Records (New/Old)

If you will carefully see the Insert statements used in the IF-THEN-ELSIF statements in the above code, we used some Pseudo Records such as ‘:New’ or ‘:Old’ followed by the name of the column of our source table sh_name.

These Psuedo Records helps us in fetching data from the sh_name column of the underlying source table ‘Superheroes’ and storing it into the audit table sh_audit.

Pseudo Record ‘: NEW’, allows you to access a row currently being processed. In other words, when a row is being inserted or updated into the superheroes table. Whereas Pseudo Record ‘: OLD’ allows you to access a row which is already being either Updated or Deleted from the superheroes table.

In order to fetch the data from the source table, you have to first write the proper Pseudo Record (New/Old) followed by dot (.) and the name of the column of the source table whose value you want to fetch. For example in our case we want to fetch the data from sh_name column which belongs to our source table superheroes. Thus we will write “: New. sh_name” for fetching the current value and to fetch the previously stored value we will write “: OLD. sh_name”. Once the values are fetched the INSERT dml will store these values into the respective columns of the audit table.

Restriction on Pseudo Record

  • For an INSERT trigger, OLD contain no values, and NEW contain the new values.
  • For an UPDATE trigger, OLD contain the old values, and NEW contain the new values.
  • For a DELETE trigger, OLD contain the old values, and NEW contain no values.

 

table auditing using dml triggers in pl/sql by manish sharma

Once you execute and compile this trigger then you can take it on a test run by writing DML statements on the underlying source table ‘Superheroes’. For example you can try Inserting a row in superheroes table and then check the audit table whether there is some data or not.

INSERT INTO superheroes VALUES ('Superman');

Similarly you can write Update and Delete DML statements on Superheroes table.

UPDATE SUPERHEROES SET SH_NAME = 'Ironman' WHERE SH_NAME='Superman';
Or
DELETE FROM superheroes WHERE SH_NAME = 'Ironman';

As soon as you execute any of these DML statements on the underlying table superheroes, the trigger will execute in the background and insert the audit data into the audit table sh_audit.

That is it for today’s tutorial hope you enjoyed reading and learnt something. Please share this post on your social media with your friends and help them in learning something new and stay tuned as in the next tutorial we are going to see some other cool tricks of triggers.

Thanks for reading have a great day.

RELATED ARTICLESMORE FROM AUTHOR