TRIGGERS

Toad for Oracle
1

I am attempting Autonumber the records in Oracle Table

ID is primary key

When I insert a new row, I want the new row to have the next sequential number.

old row ID = 100

new row ID (is to = 101) before insert

In creating a trigger the following code gets generated.

CREATE OR REPLACE TRIGGER DOBS.my_trigger1

BEFORE INSERT

ON DOBS.LK_PRODUCT_NDC_MAPPING

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

DECLARE

tmpVar NUMBER;

/******************************************************************************

NAME:

PURPOSE:

REVISIONS:

Ver Date Author Description

1.0 8/11/2017 1. Created this trigger.

NOTES:

Automatically available Auto Replace Keywords:

Object Name:

Sysdate: 8/11/2017

Date and Time: 8/11/2017, 12:06:08 PM, and 8/11/2017 12:06:08 PM

Username: (set in TOAD Options, Proc Templates)

Table Name: LK_PRODUCT_NDC_MAPPING (set in the “New PL/SQL Object” dialog)

Trigger Options: (set in the “New PL/SQL Object” dialog)

******************************************************************************/

BEGIN

tmpVar := 0;

SELECT MySeq.NEXTVAL INTO tmpVar FROM dual;

:NEW.SequenceColumn := tmpVar;

:NEW.CreatedDate := SYSDATE;

:NEW.CreatedUser := USER;

EXCEPTION

WHEN OTHERS THEN

– Consider logging the error and then re-raise

RAISE;

END ;

QUESTION: Do I edit the code change SequenceColumn to ID?

It appears that this has to be edit and then saved.

I don’t need to update the other fields. Those appear to be “examples”.

Could someone confirm this?

Thanks.

JayGBardo1

2

Yes, you would need to edit the code. The easiest way to create a Trigger/Seq pair is to go into the Schema Browser, select the table you want, select the ‘Triggers’ tab (on the right hand side), then press the [Create Sequence/Trigger Pair] button. You just then select the column (in your case ID column) and it’ll generate it for you.

If you don’t want to do that, then you would modify your code something like this…

CREATE TRIGGER DOBS.my_trigger1

BEFORE INSERT

ON DOBS.LK_PRODUCT_NDC_MAPPING
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
– For Toad: Highlight column ID
:new.ID := {add sequence name here}.nextval;
END DOBS.my_trigger1;

3

Better yet, if you have Oracle 12c, just make the default value for the column: SequenceName.NEXTVAL and forget the trigger.

4

One thing to remember, you cannot guarantee an unbroken sequence of numbers.

If the sequence is caching numbers, then at database shutdown, the cached but unused values are lost. You can pin the sequence at startup - with a trigger - to prevent this though.

If the sequence is not caching values, then you won’t lose any, but performance will suffer if there are lots of concurrent INSERTs into the table.

However, consider a number being allocated, the after some processing, an error for example, forces or causes a ROLLBACK. The value that was just assigned to your ID column, will be lost.

Do not even think about reading the current highest I’D from the table, incrementing it, and using that as the new value because that only works if you can always guarantee that the database will only ever be used in single user mode, and if not, that two users cannot ever INSERT at the same time. (You get performance problems due to locks in mode 3 (if I remember correctly) and or duplicate key failures assuming I’D is the pk, or other uniquely indexed column.)

HTH

Cheers,

Norm. [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.