background image
<< Using the Create Trigger Window | Modifying Triggers >>
<< Using the Create Trigger Window | Modifying Triggers >>

Creating an INSTEAD OF Trigger

Creating and Using Triggers
Using Triggers 5-7
BEFORE INSERT ON evaluations
FOR EACH ROW
BEGIN
NULL;
END;
5.
From the File menu, select Save to save the new trigger. Alternatively, use the
CTRL + S
key combination.
Note that Oracle Database automatically compiles triggers prior to saving them.
Creating an INSTEAD OF Trigger
INSTEAD OF
triggers enable you to implement changes to the underlying tables of a
view. Such a trigger may be used on the
emp_locations
view that you created in
"Creating a View"
. Remember the definition of
emp_locations
:
CREATE VIEW emp_locations AS
SELECT e.employee_id,
e.last_name || ', ' || e.first_name name,
d.department_name department,
l.city city,
c.country_name country
FROM employees e, departments d, locations l, countries c
WHERE e.department_id = d.department_id AND
d.location_id = l.location_id AND
l.country_id = c.country_id
ORDER BY last_name;
Example 5­4
implements an
INSTEAD OF
trigger
update_name_view_trigger
to
update the name of the employee.
Example 5­4 Updating Values from a View with an INSTEAD OF Trigger
CREATE OR REPLACE TRIGGER update_name_view_trigger
INSTEAD OF UPDATE ON emp_locations
BEGIN
-- allow only the following update(s)
UPDATE employees SET
first_name = substr( :NEW.name, instr( :new.name, ',' )+2),
last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1)
WHERE employee_id = :OLD.employee_id;
END;
Creating LOGON and LOGOFF Triggers
LOGON
and
LOGOFF
triggers monitor who uses the database by writing to a log table.
In
Example 5­5
, you will create a table
hr_users_log
for keeping track of
LOGON
and
LOGOFF
events. You will then create triggers
note_hr_logon_trigger
(in
Example 5­6
) and
note_hr_logoff_trigger
(in
Example 5­7
) for writing these
events to the log table.
Example 5­5 Creating an access log table, hr_users_log
This table is the log of all logon and logoff events in the
hr
schema.
CREATE TABLE hr_users_log (user_name VARCHAR2(30), activity VARCHAR2(20),
event_date DATE);