Lesson: Database Triggers

Purpose of Triggers

There are a number of situations when you might want to use a trigger rather than another PL/SQL program type. A trigger can generate values for columns, provide validation (prevent invalid data, enforce security or referential integrity), implement specific business rules, provide auditing or logging (including replicating data), modify table data when views have DML run against them, or publish information to external applications. Keep in mind, though, that triggers should not be used as a replacement for what can be easily accomplished with database constraints; trigger performance is slower than database constraint enforcement.

Triggers fire within the scope of a database transaction. You need to take special care when designing and using triggers to prevent problems with performance and application support. Because triggers fire without any explicit notice, their effects can be confusing. In addition, interactions between triggers can result in side effects that result in errors or situations that are difficult to debug.

Purpose of Triggers

There are a number of situations when you might want to use a trigger rather than another PL/SQL program type. A trigger can generate values for columns, provide validation (prevent invalid data, enforce security or referential integrity), implement specific business rules, provide auditing or logging (including replicating data), modify table data when views have DML run against them, or publish information to external applications. Keep in mind, though, that triggers should not be used as a replacement for what can be easily accomplished with database constraints; trigger performance is slower than database constraint enforcement.

Triggers fire within the scope of a database transaction. You need to take special care when designing and using triggers to prevent problems with performance and application support. Because triggers fire without any explicit notice, their effects can be confusing. In addition, interactions between triggers can result in side effects that result in errors or situations that are difficult to debug.

Invocation of Triggers

Rather than being explicitly invoked or called from a SQL statement, a trigger is called when an event occurs. The three categories of events that can cause a trigger to fire are DML statements (INSERT, UPDATE, DELETE), DDL statements (e.g. CREATE or ALTER) or database events (logon, logoff, startup, shutdown, or when an error occurs). Possible trigger events include the following:

• CREATE
• DELETE
• DROP
• DROP OR TRUNCATE
• INSERT
• INSERT OR UPDATE
• INSERT OR UPDATE OR DELETE
• UPDATE
• UPDATE OR DELETE

A number of distinctions need to be kept in mind when writing triggers. Triggers can run before or after an event occurs. The number of times a DML trigger fires can vary based upon whether the trigger is coded to run once per statement or once for every row. In situations where a view is not inherently updatable ( cannot be modified directly through DML statements) there are also INSTEAD OF triggers that provide a way of changing the data displayed by the view. These distinctions are identified in reference to triggers as the trigger timing firing points. The available trigger timing firing points (which are permutations of the distinctions previously mentioned) are as follows:

• BEFORE EACH ROW
• AFTER EACH ROW
• BEFORE STATEMENT
• AFTER STATEMENT
• BEFORE EVENT
• AFTER EVENT
• INSTEAD OF

Triggers that fire in response to a DML statement might need to access data included in the statement itself or data that is part of an affected record. The values originally in the table can be referenced using :OLD and the values that are part of the current statement can be referenced using :NEW.

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:

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 trigger can be created to enforce this rule.

Code Sample:

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

Code Sample:

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

Code Sample:

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

Code Sample:

drop trigger tr_set_email;

Code Explanation

Validation Trigger

1. Create a trigger to raise an error if an employee salary being entered is greater than 25000.
2. Name the trigger TR_VALIDATE_SALARY.
3. The trigger should reference the SALARY field on the EMPLOYEES table.
4. Test the trigger by verifying that you can set the salary of employee_id 100 to an acceptable value but that an error occurs if the salary is above 25000.
5. Drop the trigger when you have finished testing.

Solution:

create or replace trigger tr_validate_salary
before insert or update of 
salary on employees
for each row
begin
  
  IF :new.salary > 25000 THEN
    raise_application_error(-20001, 
                            'Invalid Salary: ' || :new.salary);
  END IF;
  
end;
/

-- Watch it fail
update employees
set salary=28000
where employee_id=100;

-- Watch it succeed
update employees
set salary=21000
where employee_id=100;

-- rollback the change before executing the DDL
rollback;

-- drop the trigger
drop trigger tr_validate_salary;

Modifying Triggers

A trigger can be created using the CREATE statement. As with other subprograms, a CREATE statement will fail if an object already exists. You can override this behavior by using the Oracle proprietary REPLACE keyword. This allows an existing subprogram (in this case a trigger) to be modified without having to drop it and then recreate it.

Code Sample:

create or replace trigger creation_alert
after create on schema

begin
  dbms_output.put_line('Something has been created.');
end;
/

Code Explanation

Code Sample:

create or replace trigger creation_alert
after create on schema
begin
 dbms_output.put_line(ORA_DICT_OBJ_TYPE
                   || ' '
                   || ORA_DICT_OBJ_NAME
                   || ' has been created.');
end;
/

Code Explanation

Viewing Trigger Metadata

The Oracle Data Dictionary contains information about triggers that have been created in the database. The queries below demonstrate some of the significant views. These queries reference the USER_ views. The corresponding ALL_ and DBA_ views show triggers owned by other users (if your database user has appropriate privileges).

Code Sample:

select * 
from user_objects
where object_name='CREATION_ALERT';

select * 
from user_triggers
where trigger_name ='CREATION_ALERT';

select text
from user_source
where name = 'CREATION_ALERT';

Code Explanation

Enabling/Disabling Triggers

If you do not want a valid function or procedure to be available within the database, you need to drop it. Later, if you want the subprogram to be available, you need to compile the program using the original PL/SQL source code. You can render a trigger inoperable by choosing to disable it. You can enable a disabled trigger by simply setting it as enabled.

Code Sample:

create table x 
as
select * 
from employees
where 0=1;

drop table x;

Code Explanation

Code Sample:

alter trigger creation_alert disable;

select status 
from user_triggers 
where trigger_name ='CREATION_ALERT';

create table x
as 
select * 
from employees
where 0=1;

drop table x;

Code Explanation

Code Sample:

alter trigger creation_alert enable;

select status 
from user_triggers 
where trigger_name ='CREATION_ALERT';

create table x
as
select * 
from employees
where 0=1;

drop table x;

Code Explanation

Code Sample:

drop trigger creation_alert;

Code Explanation

Trigger Errors

In addition to the PL/SQL errors that apply to other subprogram types, there are additional potential problems for triggers. Because triggers fire in response to a database event they need to be considered in the context of a transaction. A mutating table exception occurs if row-level trigger code references the triggering table in a query.

Code Sample:

CREATE OR REPLACE TRIGGER EMP_MUTATION 
AFTER 
  INSERT OR UPDATE OF SALARY ON EMPLOYEES 
FOR EACH ROW
DECLARE
  v_salary employees.salary%type;
BEGIN
  SELECT min(salary)
  into v_salary
  from employees;
  
  IF :new.salary < v_salary THEN
    raise_application_error(-20001,'Salary too low');
  END IF;
  
END;
/

Code Explanation

Code Sample:

insert into employees (
employee_id,
first_name,
LAST_NAME,
EMAIL,
hire_date,
JOB_ID)
values 
(
 999,
 'Joe', 
 'Mutant',
 'N/A',
 sysdate,
 'AD_PRES'
);
/

Code Explanation

There are a number of ways of dealing with mutating table errors. One solution is the use of PRAGMA AUTONOMOUS_TRANSACTION. However, in general, it is best to reconsider trigger design if such errors occur. Triggers can frequently be rewritten (in conjunction with other PL/SQL programs) to accomplish the intended goal without resorting to solutions that operate outside of the immediate transactional context.

System and User Event Triggers

Triggers can also be created to respond to system-wide database events (errors, startup, shutdown) and user-specific events (logon, logoff). Such triggers are particularly useful for monitoring and auditing activities. They require specific privileges due to the access required. The exercise below will give you an opportunity to create a logon trigger. See the Oracle Documentation for more information on how these are implemented.

Logon Counter Trigger

1. Create a table called LOGON_COUNT with a single column of type NUMBER named TOTAL_LOGINS with a single row with a value of zero.
2. Create an AFTER LOGON ON SCHEMA trigger that increments this field each time a user logs in.
3. Query the LOGON_COUNT table.
4. Log off and log on. Verify that the count incremented.
5. Check the status in user_objects and user_triggers. Explain the results.
6. Drop the LOGON_COUNT table. Check the status in user_objects and user_triggers.
7. Drop the trigger, and purge the recycle bin.

Solution:

create table logon_count
as
select 0 as total_logins
from dual;

select *
from logon_count;

create or replace trigger logon_alert
after logon on schema
begin
  update logon_count 
  set total_logins = total_logins +1;
end;
/

select * 
from logon_count;

--Log off an log on 
select *
from logon_count;

select status 
from user_triggers 
where trigger_name='LOGON_ALERT';

select status 
from user_objects 
where object_name='LOGON_ALERT';

--The user_triggers status indicates whether the
--    trigger is enabled or disabled.
--The user_objects status indicates whether the
--    trigger successfully compiled.

drop table logon_count;

purge recyclebin;