Editing Installation Script Files that Create Triggers

If your application has a BEFORE INSERT trigger on a source table, and you will insert the data from that source table into the corresponding new table, then you must decide if you want the trigger to fire before each INSERT statement in the installation script file inserts data into the new table.

For example, in the sample application, NEW_EVALUATION_TRIGGER 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 created in "Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted".)

The source EVALUATIONS table is populated with primary keys. If you do not want the installation script to put new primary key values in the new EVALUATIONS table, then you must edit the CREATE TRIGGER statement in the installation script file as shown in bold:

CREATE OR REPLACE
TRIGGER NEW_EVALUATION_TRIGGER
BEFORE INSERT ON EVALUATIONS
FOR EACH ROW
BEGIN
  IF :NEW.evaluation_id IS NULL THEN
    :NEW.evaluation_id := evaluations_seq.NEXTVAL
  END IF;
END;

Also, check the current value of the sequence. If it not is greater than the maximum value in the primary key column, make it greater.

You can edit the installation script file in the SQL Worksheet or any text editor.

Two alternatives to editing the installation script file are:

• Change the trigger definition in the source file and then re-create the installation script file.

  For information about changing triggers, see "Changing Triggers".

• Disable the trigger before running the data installation script file, and then re-enable it afterward.

  For information about disabling and enabling triggers, see "Disabling and Enabling Triggers".

If your application uses triggers to populate auditing columns (such as CREATED_ON and CREATED_BY), you must preserve your source table data by having the trigger set a new value only if the current value is null, with code like this:

IF :NEW.CREATED_ON IS NULL THEN
  :NEW.CREATED_ON := SYSDATE;
END IF;