Coding Triggers

Contact Us or call 1-877-932-8228
Coding Triggers

Coding Triggers

Consider a requirement to address a specific business rule that mandates a corporate standard email address format. The format specified consists of the first letter of the first name, followed by the first 8 letters of the last name. We can use the Oracle SUBSTR function to analyze data already in the EMPLOYEES table.

Code Sample:

Database-Triggers/Demos/select_from_employees.sql
SELECT substr(upper(substr(first_name, 1, 1)         
                    || last_name), 1, 8),
       email, first_name, last_name
FROM employees e
WHERE substr(upper(substr(first_name, 1, 1) ||
                   last_name), 1, 8) != email;
		   

Code Explanation

A review of email addresses reveals that the corporate email standard is not followed in every case.

A trigger can be created to enforce this rule.

Code Sample:

Database-Triggers/Demos/email_trigger.sql
create or replace trigger tr_set_email
before insert or
       update of first_name, last_name,
                 email 
       on employees
       
for each row
begin
 :new.email := substr(upper(substr(:new.first_name, 1, 1)
                      || :new.last_name), 1, 8);
end;
/

Code Explanation

This trigger will fire before relevant data (first name, last name and email address) are inserted into or updated in the EMPLOYEES table. It will fire once for each affected row, rather than just once for the whole statement. A value is assigned to the email address using the same SUBSTR logic used in the previous query.

Code Sample:

Database-Triggers/Demos/test1_email_trigger.sql
insert into employees
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
 HIRE_DATE, JOB_ID)
values
(employees_seq.nextval,'Tony','Stark',
 '20-JUL-69','FI_ACCOUNT');

select first_name, last_name, email 
from employees 
where last_name = 'Stark';

Code Explanation

Now, when a new employee is added, the email is added in the correct format automatically. However, this type of behavior can be a bit confusing unless well documented.

Code Sample:

Database-Triggers/Demos/test2_email_trigger.sql
select * from employees where email = 'JMURMAN';

update employees set email=email where email = 'JMURMAN';

--...not there!
select * from employees where email = 'JMURMAN'; 

select email from employees where last_name = 'Urman';

--restore the original data
rollback;

Code Explanation

In this example, the email is explicitly set to itself. This causes the trigger to fire which changes the value. The original query no longer returns the record. This is understandable if one knows about the trigger and knows about how it functions. If one is unaware, this sort of "side effect" can be problematic.

Code Sample:

Database-Triggers/Demos/drop_trigger.sql
drop trigger tr_set_email;

Code Explanation

We will conclude by dropping this trigger to prevent interaction with other triggers during remaining lessons. There are other options related to maintaining triggers that will be discussed in the next lesson.

Next