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 :

create table abc (eid number, mid number,
constraint pk primary key(eid),
constraint fk foreign key (mid) referencing abc(eid) on delete cascade
)
 
   insert into abc
    select 4, null from dual union all
    select 1, null from dual union all
    select 2, 1 from dual union all
    select 3, 1 from dual union all
    select 5, 1 from dual union all
    select 6, 2 from dual
/

select * from abc
 
 
EID	MID
4	
1	
2	1
3	1
5	1
6	2

If I issue a delete like below:

delete from abc where eid = 1

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:

create global temporary table abc_del_rows(eid number) on commit delete rows;
create trigger abc_del before delete on abc
    for each row
    begin
      insert into abc_del_rows(eid) values (:old.eid);
    end;
   /
select * from abc;

       EID        MID
---------- ----------
         4
         1
         2          1
         3          1
         5          1
         6          2
 
6 rows selected.
delete from abc where eid = 1;
 
1 row deleted.
 
select * from abc_del_rows;
       EID
----------
         1
         2
         3
         5
         6
 
5 rows selected.

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.

create table transactions_with_audit (txn_id number(10), order_type char(2), last_modified_by varchar2(50), last_modified_date date);

create or replace trigger trg_trans_with_Audit
before insert 
on transactions_with_audit
for each row
begin 
:new.last_modified_by := 'TMPUSR';
:new.last_modified_date :=SYSDATE;
end;

insert into transactions_with_audit(txn_id,order_type)  values(123456789,'S')
select * from transactions_with_audit

Notice 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 :

INSERT INTO transactions_with_audit (txn_id,
                                     order_type,
                                     last_modified_by,
                                     last_modified_date)
  VALUES   (123456789,
            'S',
            'TMPUSR',
            SYSDATE)

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.