Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted

The sequence EVALUATIONS_SEQ, created in "Creating and Managing Sequences", generates primary keys for the EVALUATIONS table. However, these primary keys are not inserted into the table automatically.

This tutorial shows how to use the SQL Developer Create Trigger tool to create a trigger named NEW_EVALUATION_TRIGGER, which fires before a row is inserted into the EVALUATIONS table, and generates the unique number for the primary key of that row, using evaluations_seq. The trigger fires once for each row affected by the triggering INSERT statement.

NEW_EVALUATION_TRIGGER is a row-level trigger and a BEFORE trigger.

This trigger is part of the sample application that the tutorials and examples in this document show how to develop and deploy.

To create the NEW_EVALUATION trigger:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Right-click Triggers.

   A list of choices appears.

3. Click New Trigger.

   The Create Trigger window opens. The field Schema has the value HR and the field Name has the default value TRIGGER1.

4. In the Name field, type NEW_EVALUATION_TRIGGER over the default value.

5. Click the tab Trigger.

   The Trigger pane appears. By default, the field Trigger Type has the value TABLE, the check box Enabled is selected, the field Table Owner has the value HR, the field Table Name has the value COUNTRIES, the options Before and Statement Level are selected, the options After and Row Level are deselected, and the check boxes Insert, Update, and Delete are deselected.

6. In the field Table Name, from the drop-down menu, select EVALUATIONS.

7. Select the option Row Level.

   The option Statement Level is now deselected.

8. Select the check box Insert.

9. Click OK.

   The NEW_EVALUATION_TRIGGER pane opens, showing the CREATE TRIGGER statement that created the trigger.

10. In the CREATE TRIGGER statement, replace NULL with this:

    :NEW.evaluation_id := evaluations_seq.NEXTVAL
    

    The title of the NEW_EVALUATION_TRIGGER pane is in italic font, indicating that the trigger is not yet saved in the database.

11. From the File menu, select Save.

    Oracle Database compiles the procedure and saves it. The title of the NEW_EVALUATION_TRIGGER pane is no longer in italic font.