5-13. Creating a Trigger in a Disabled State
Problem
After a planning meeting, your company has decided that it would be a great idea to create a trigger to send notification of updates to employee salaries. Since the trigger will be tied into the system-wide database application, you want to ensure that it compiles before enabling it so that it will not affect the rest of the application.
Solution
Create a trigger that is in a disabled state by default. This will afford you the opportunity to ensure that the trigger has compiled successfully before you enable it. Use the new DISABLE clause to ensure that your trigger is in DISABLED state by default.
The following trigger sends messages to employees when their salary is changed. The trigger is disabled by default to ensure that the application is not adversely affected if there is a compilation error.
CREATE OR REPLACE TRIGGER send_salary_notice
AFTER UPDATE OF SALARY ON employees
FOR EACH ROW
DISABLE
DECLARE
v_subject VARCHAR2(100) := 'Salary Update Has Occurrred';
v_message VARCHAR2(2000);
BEGIN
v_message := 'Your salary has been increased from ' ||
:old.salary || ' to ' || :new.salary || '.' ||
'If you have any questions or complaints, please ' ||
'do not contact the DBA.';
SEND_EMAIL(:new.email || '@mycompany.com',
v_subject,
v_message);
END;
On an annual basis, this trigger can be enabled via the following syntax:
ALTER TRIGGER send_salary_notice ENABLE;
It can then be disabled again using the same syntax:
ALTER TRIGGER send_salary_notice DISABLE;
How It Works
Another welcome new feature with Oracle 11g is the ability to create triggers that are DISABLED by default. The syntax for creating a trigger in this fashion is as follows:
CREATE OR REPLACE TRIGGER trigger_name
ON UPDATE OR INSERT OR DELETION OF table_name
[FOR EACH ROW]
DISABLED
DECLARE
-- Declarations go here.
BEGIN
-- Statements go here.
END;
The new DISABLED clause is used upon creation of a trigger. By default, a trigger is ENABLED by creation, and this clause allows for the opposite to hold true.