Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger Question
On Thu, 01 Jan 1998 00:56:47 -0500, The Hudsons <hudson_at_cybernex.net> wrote:
>I'm looking for help. I have been asked to write my first trigger, and
>I don't know where to begin. I need a trigger that will populate a
>field with a unique value when an insert is done. I don't think that
>this should be too hard if I am inserting a single row, but when I
>insert multiple rows I would like the field to be the same for each row
>- is this possible?
Yes. It's an interesting problem too, so if I get ambitious this weekend, maybe I'll write it for you. The solution is a bit complicated though, especially if you havn't written much PL/SQL. You will want TWO triggers, and you will also need to create a stored package.
The triggers, and the purpose of each, are as follows:
a table-level, before insert trigger This trigger initializes a variable, in the package that stores the value that you want to assign to each inserted row. This trigger will fire ONCE for each insert statement, so if you insert multiple rows at once you will get the same value in each. a row-level, before insert trigger. sets the field to the value created by the other trigger.
The purpose of the package is to hold a variable that maintains its state for the duration of your session.
You will also probably want a database sequence to use in order to generate the unique values.
>but when I
>insert multiple rows I would like the field to be the same for each row
>- is this possible?
Let me revisit this one statement. You did say you wanted a "unique" value, then you said you wanted the value to be the same on a multi-row insert. These aren't compatible statements. Are you sure which you want?
regards,
Jonathan Gennick Received on Sat Jan 03 1998 - 00:00:00 CST