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.
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;
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.
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; /
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.
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';
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.
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;
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.
drop trigger tr_set_email;
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.