Modifying Triggers
Creating and Using Triggers
5-8 Oracle Database 2 Day Developer's Guide
Example 56 Creating a LOGON trigger
This trigger inserts a
LOGON
event record into the
hr_users_log
table whenever
someone connects to the
hr
schema. Note that this is an
AFTER
trigger.
CREATE OR REPLACE TRIGGER note_hr_logon_trigger
AFTER LOGON
ON HR.SCHEMA
BEGIN
INSERT INTO hr_users_log VALUES (USER, 'LOGON', SYSDATE);
END;
Example 57 Creating a LOGOFF trigger
This trigger inserts a
LOGOFF
event record into the
hr_users_log
table whenever
someone disconnects from the
hr
schema. Note that this is a
BEFORE
trigger.
CREATE OR REPLACE TRIGGER note_hr_logoff_trigger
BEFORE LOGOFF
ON HR.SCHEMA
BEGIN
INSERT INTO hr_users_log VALUES (USER, 'LOGOFF', SYSDATE);
END;
Modifying Triggers
The
new_evaluation_trigger
has an empty body.
demonstrates how to modify the trigger to assign to the
evaluation_
id
the next available value from the
evaluations_seq
sequence.
Example 58 Modifying a Trigger
Replace the
new_evaluation_trigger
with the following code. New code is in
bold font.
CREATE OR REPLACE TRIGGER new_evaluation_trigger
BEFORE INSERT ON evaluations FOR EACH ROW
BEGIN
:NEW.evaluation_id := evaluations_seq.NEXTVAL;
END;
Disabling and Enabling Triggers
On occasion, you may need to temporarily disable a trigger if an object it references is
unavailable, or if you need to perform a large data upload (such as in recovery
operations) without the delay that triggers cause.
To disable a trigger, you must use the
ALTER TRIGGER ... DISABLE
statement. To
re-enable the trigger, use the
ALTER TRIGGER ... ENABLE
statement.
shows how to temporarily disable a trigger.
Example 59 Disabling a Trigger
ALTER TRIGGER eval_change_trigger DISABLE;
Example 510 Enabling a Trigger
ALTER TRIGGER eval_change_trigger ENABLE;
