facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Database Triggers

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

In this lesson, you will learn about database triggers.

Lesson Goals

  • Learn about database triggers
  • Learn how to use trigger events (DML and DDL)
  • Learn how to use trigger types
  • Learn design considerations
  • Learn about trigger maintenance
  • Learn about trigger maintenance tasks
  • Learn about debugging issues with triggers
  • Learn about handling mutating tables
  • Learn about system and user event 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:

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.

Validation Trigger

Duration: 10 to 15 minutes.
  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:

Database-Triggers/Solutions/solution-1.sql
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;

Code Explanation

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:

Database-Triggers/Demos/ddl_create_trigger.sql
create or replace trigger creation_alert
after create on schema

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

Code Explanation

This trigger will fire when an object is created within the user's schema. In this case, the trigger will simply output a message to the screen.

Code Sample:

Database-Triggers/Demos/ddl_modify_trigger.sql
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

The trigger is modified using the OR REPLACE clause. The output will now include information about the type and name of the object created.

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:

Database-Triggers/Demos/view_trigger.sql
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

Several data dictionary views contain information about triggers that have been created. The USER_OBJECTS view contains information about the trigger that involves attributes common to other database objects and subprograms. The USER_TRIGGERS view contains trigger specific information. As with other PL/SQL objects, all of the actual source code after the CREATE keyword is available in USER_SOURCE.

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:

Database-Triggers/Demos/fire_trigger.sql
create table x 
as
select * 
from employees
where 0=1;

drop table x;

Code Explanation

This code will fire the CREATION_ALERT trigger created earlier.

Code Sample:

Database-Triggers/Demos/disable_trigger.sql
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

A disabled trigger will not fire, but the code remains in the database.

Code Sample:

Database-Triggers/Demos/enable_trigger.sql
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

A disabled trigger can be enabled without requiring the original code to be created or replaced.

Code Sample:

Database-Triggers/Demos/drop_trigger-2.sql
drop trigger creation_alert;

Code Explanation

The drop keyword is used to remove the trigger altogether. A dropped trigger cannot be enabled. It must be recreated.

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:

Database-Triggers/Demos/mutating_trigger.sql
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

This trigger is used to demonstrate a mutating table error. Notice that the MIN function is checking every row in the employees table. This check occurs at the same time that the DML that fired the trigger is occurring.

Code Sample:

Database-Triggers/Demos/mutating_trigger_test.sql
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

Running this insert statement causes the mutating table error to occur. There are a number of possible solutions for this type of problem - but the fundamental lesson is to limit the use of triggers to discrete well understood units of work that do not result in such side effects.

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

Duration: 15 to 20 minutes.
  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:

Database-Triggers/Solutions/solution-2.sql
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;

Code Explanation