Oracle if updating trigger

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.

[Click Here To Tweet This] 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.

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.

SQL statements running in triggers follow the same rules that standalone SQL statements do.

Specifically: How Triggers and Constraints Differ Both triggers and constraints can constrain data input, but they differ significantly.

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, v_user, v_date,'Update'); END IF; END; / I would highly suggest you to watch the You Tube 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.

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.A constraint applies to both existing and new data.For example, if a database column has a values that were inserted into the column before the trigger was defined or while the trigger was disabled.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.

Leave a Reply