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