Creating a Row Trigger
Creating and Using Triggers
Using Triggers 5-5
CREATE TABLE evaluations_log (log_date DATE
, action VARCHAR2(50));
, you will create a trigger that writes to the
evaluations_log
every
time the
evaluations
table changes.
Example 52 Logging Operations with a Statement Trigger and Predicates
The trigger
eval_change_trigger
tracks all changes made to the
evaluations
table, and tracks them in the
evaluations_log
table by adding to it a new row
AFTER
these changes are made. Note that in this example, the body of the trigger uses
a conditional predicate
INSERTING
,
UPDATING
, or
DELETING
, to determine which of
the three possible statements fired the trigger.
CREATE OR REPLACE TRIGGER eval_modification_trigger
AFTER INSERT OR UPDATE OR DELETE
ON evaluations
DECLARE log_action evaluations_log.action%TYPE;
BEGIN
IF
INSERTING
THEN log_action := 'Insert';
ELSIF
UPDATING
THEN log_action := 'Update';
ELSIF
DELETING
THEN log_action := 'Delete';
ELSE DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;
INSERT INTO evaluations_log (log_date, action)
VALUES (SYSDATE, log_action);
END;
Creating a Row Trigger
Row triggers are executed for each affected row.
In
evaluations_seq
sequence as a
primary key number generator for the
evaluations
table. Oracle Database does not
populate the primary key automatically, as part of the
CREATE TABLE
statement.
Instead, you must design a trigger that generates the unique number for the primary
key with every
INSERT
statement.
In the following task, you will use the SQL Developer Connection navigation
hierarchy to create a trigger
new_evaluation
, which checks if a new row should be
added to the
evaluations
table, based on whether a row for the same employee
exists for the identical time period.
Example 53 Generating Primary Keys FOR EACH ROW Triggers; BEFORE Option
1.
In the Connections navigation hierarchy, right-click Triggers.
2.
From the drop-down, select New Trigger.
