Creating Trigger & Sequence in Oracle using Toad

Creating Trigger & Sequence in Oracle using Toad

*- As Oracle has no auto-increment numeric field, it is done by the combination of Sequence and Trigger for the Table. Sequence is an independent object that keeps track of the sequence number. Trigger or Java code uses this value for the primary key next value.

*- We have two options for the primary key insertion, a. Use the combination of Trigger and Sequence and Java code do not insert the primary key in the insert query in the java code. b. Do not use trigger only call sequence in the Java code to get the next value, and use this next value in the insert query in the Java code. This approach is the only solution when we need newly inserted primary key back for further SQL operations in a transaction. Using the approach b is best, because insert operation may be invoked from the several BLL classes those may need the newly inserted primary key. So all insert DAL operations should return the primary key always.

*- Check out in the Table tab that Triggers exits for table or not, Trigger has association with the Table, but the Sequence is an independent Oracle object and it has nothing to do with the Table. Convention for sequence name is SEQ_TABLENAME_CT and for trigger name is TRG_TABLENAME_CT.

*- Open three editors, one for the Table, one for the Sequence and one for Trigger.

*- Check out the table data for the last value of the auto increment column suppose it is 122

*- Open the sequence pane; right click the sequence if it already exist and Recreate it and adjust the sequence value, Set the Start With = 122

4- If does not exist then, create new sequence and adjust the values for the auto-increment values

5- Name the sequence as SEQ_TABLENAME_CT, and the set the:*Start With*: value as desired

6- Press execute button to create the sequence.

7- Now go on Table tab and the Trigger tab and Add New Trigger button

8- Give the trigger name as TGR_TABLENAME_CT

9- Check the insert check box, this mean this trigger will be called at the time of insert. Before means, the trigger will be invoked before the insert query.

10- Press the Send to Editor Button; this will open the trigger script in the editor. 1. Remove the two lines; 2. update the Sequence-Name and 3. Update the Primary-Key-Name.

11- Remove following two lines

   :NEW.CreatedDate := SYSDATE;

   :NEW.CreatedUser := USER;

12- Update sequence name as follows.

SELECT SEQ_TABLE_NAME.NEXTVAL INTO tmpVar FROM dual;

13- Update primary key column as follows.

:NEW.PRIMARY_KEY_COLUMN:= tmpVar;

14- Execute or Run the trigger, all is well.