When should we use Row level DML triggers

A trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs. Row level DML triggers are invoked for every row the specified DML statement touches (insert/update/delete). Since the database has to execute the trigger’s execution block in addition to the DML that invoked the trigger, by creating a trigger we are implicitly creating an overhead for the database. For example, consider a trigger defined before insert of every row in a table (tab1). This trigger’s body has logic to insert 50 records in another table (tab2). So when we insert 1000 rows in tab1, actually we are performing 50000 row inserts in the database.
The above example gives us an idea of why Triggers should be used carefully. You should create triggers only if there is no alternative possible to achieve your desired functionality. Below we will look at some scenarios where a DML trigger will be useful.

Case 1: When there are implicit operations performed by database

Suppose, I have a table abc, which has a primary key emp_id and a foreign key mgr_id referencing emp_id :

If I issue a delete like below:

After running above delete statement, the sql%rowcount gives deleted rows = 1, but actually, due to on delete cascade, 5 rows are deleted. Oracle database does an implicit operation here. To exactly identify how many records were deleted, we should create a row level trigger that inserts the deleted rows in a temporary table before the database deletes the rows impacted by referential integrity constraint. Below is the resolution for above scenario:

As expected, the trigger inserted 5 rows in the temporary table before the rows were deleted by oracle. This example also illustrates when we should use a ‘before’ trigger.

Case 2: When the data to be updated/inserted by the DML has to be modified before the DML is executed

Consider a scenario where, for audit purpose, you need to keep track of every row updated in the table.

alekhNotice that the insert statement didn’t specify values for audit columns “last_modified_by” and “last_modified_date” but the trigger set these values before the actual insert happened, there by transforming the insert statement to something like below :

The above two cases is not an exhaustive list but gives fair indication of cases where triggers can’t be avoided. There will be other scenarios that you will encounter in your projects where use of triggers will be unavoidable. But you should always look for alternatives first and if there are none, then only should consider creating triggers.


Leave A Comment

Your email address will not be published. Required fields are marked *