RDBMS

What is a Database Trigger?

A database trigger is a PL/SQL block executed on an event in the database – a particular data manipulation of a table (INSERT, UPDATE or DELETE). Triggers are used to:

• implement complex business rules that integrity constraints cannot;
• audit – keep track of changes made to a table;
• automatically perform an action when another action takes place.

Like stored procedures, but procedures are called explicitly, whereas triggers are fired implicitly by Oracle; their execution is transparent to users.

Types of Triggers

Combinations: BEFORE statement, BEFORE row, AFTER statement, AFTER row – each for INSERT, DELETE and UPDATE = 12 triggers.

Creating a Trigger

• FOR EACH ROW makes it a row-level trigger; otherwise it is statement-level.
• WHEN fires the trigger only when the condition is true (row triggers only), e.g. when (new.amount > 1000).
• OF columns – only updates of these columns fire the trigger.
• REFERENCING renames the correlation names (to avoid conflicts with table names).
• Naming convention used: table_event_type, e.g. STUDENTS_BI_ROW = STUDENTS, Before Insert, ROW.

create or replace trigger students_bi_row
before insert on students
for each row
begin
   if :new.dj > sysdate then
      raise_application_error(-20002, 'Date of joining cannot be after system date.');
   end if;
end;
/
create or replace trigger payments_bi_row
before insert on payments
for each row
declare
   v_dueamt number(5);
begin
   v_dueamt := getdueamt(:new.rollno);           -- function from Chapter 19
   if :new.amount > v_dueamt then
      raise_application_error(-20112, 'Amount being paid is more than what is to be paid');
   end if;
end;
/
create or replace trigger payments_biud           -- statement-level
before insert or update or delete on payments
begin
   if to_char(sysdate,'DY') = 'SUN' then
      raise_application_error(-20111, 'No changes can be made on sunday.');
   end if;
end;

• Valid statements in a trigger body: DML commands and SELECT INTO; it may call stored procedures, functions and packages.
• USER_TRIGGERS lists triggers; TRIGGER_BODY is a LONG column – increase SQL*Plus SET LONG (default 80) to see all of it.

Correlation Names :NEW and :OLD

create or replace trigger payments_bu_row
before update on payments
for each row
begin
   if :new.amount <> :old.amount then
      raise_application_error(-20113,
        'Amount cannot be changed. Please delete and reinsert the row, if required');
   end if;
end;

INSTEAD OF Triggers

Defined on relational views and object views that cannot be modified directly by DML. Instead of executing the DML on the view, Oracle runs the INSTEAD OF trigger. Inserting into the join view NEWSTUDENT (STUDENTS + PAYMENTS) normally fails with ORA-01779; the trigger below inserts into both tables.

create or replace trigger newstudent_it_bi_row
instead of insert on newstudent
for each row
begin
   insert into students (rollno, bcode, name, gender, dj)
   values (:new.rollno, :new.bcode, :new.name, :new.gender, sysdate);
   insert into payments values (:new.rollno, sysdate, :new.amount);
end;
/
insert into newstudent values (15,'Joe','b2','m',2000);   -- one row in each table

Which Command Fired the Trigger?

When one trigger handles several commands, the conditional predicates INSERTING, DELETING and UPDATING tell which one fired it.

create or replace trigger courses_biud_row
before insert or delete or update on courses
for each row
begin
   if inserting then
      insert into courses_log values (1, :new.ccode, sysdate, user);
   elsif deleting then
      insert into courses_log values (2, :old.ccode, sysdate, user);
   else
      insert into courses_log values (3, :old.ccode, sysdate, user);
   end if;
end;

Enabling, Disabling and Dropping

alter trigger payments_bu_row disable;
alter trigger payments_bu_row enable;
alter table students disable all triggers;
drop trigger payments_bu_row;

Disabling triggers speeds up large data changes (e.g. update students set name = upper(name)) because the trigger code is not executed; a disabled trigger does not fire until enabled.

Chapter 20 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 20 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
3. Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
4. Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.