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
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.
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.
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:
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:
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.
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.
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;
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.
create or replace trigger creation_alert after create on schema begin dbms_output.put_line('Something has been created.'); end; /
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.
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; /
The trigger is modified using the OR REPLACE clause. The output will now include information about the type and name of the object created.
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).
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';
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.
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.
create table x as select * from employees where 0=1; drop table x;
This code will fire the CREATION_ALERT trigger created earlier.
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;
A disabled trigger will not fire, but the code remains in the database.
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;
A disabled trigger can be enabled without requiring the original code to be created or replaced.
drop trigger creation_alert;
The drop keyword is used to remove the trigger altogether. A dropped trigger cannot be enabled. It must be recreated.
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.
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; /
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.
insert into employees ( employee_id, first_name, LAST_NAME, EMAIL, hire_date, JOB_ID) values ( 999, 'Joe', 'Mutant', 'N/A', sysdate, 'AD_PRES' ); /
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.
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.
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;