Re: Simulating sequences

> so has the final implemention become:
> A/ A column value per employee

Almost. More precisely, it is, as you would expect, one ROW per employee
(in the employee table). The employee table includes a COLUMN defined to
store the most-recently-issued number in the sequence for that paricular
employee.

> B/ A trigger to implement the incrementing of the value

Yes. When an expense report is created for an employee, i.e., a row is
inserted in the expense_report table, that trigger fires, updating the
employee's sequence column value and using that new value in the
expense_report table.

> C/ A row/record lock to enforce atomicity

The lock is not explicite. I'm told from good sources that the UPDATE
statement creates a lock implicitely in the updated row, and that there
is an implicite transaction around the trigger as part of the originating
INSERT statement.

>
> btober(at)seaworthysys(dot)com wrote:
>
>>>On Monday, Aug 18, 2003, at 09:01 US/Pacific,
>>> <btober(at)seaworthysys(dot)com>
>>> wrote:
>>>
>>>
>>>>With those items in mind, your function could become:
>>>>
>>>>CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer
>>>> AS'
>>>>DECLARE
>>>> the_department ALIAS FOR $1;
>>>> the_table_name ALIAS FOR $2;
>>>>BEGIN
>>>> IF NOT EXISTS(SELECT 1 FROM cnfg_key_generation
>>>> WHERE the_department = department AND the_table_name =
>>>>table_name)
>>>>THEN
>>>> INSERT INTO cnfg_key_generation VALUES
>>>>(the_department,the_table_name,0);
>>>> END IF;
>>>
>>> I would get the insert out of there, too. If it doesn't exist,
>>> throw
>>>an exception. I don't believe sequences should automatically create
>>> themselves (the tables and columns don't).
>>>
>>
>>
>> I agree. In my own case I need a sequence for each employee, and the
>> simulated sequence is defined as a column in the employee table, so
>> I'm guaranteed to have a a place to do the incrementing when the need
>> arises. Also, I used a "DEFAULT 0" clause on the column definition
>> for the sequence value, rather than explicitly inserting a zero. I
>> left the insert statement in place for compatibility with the
>> original inquirer's definition.
>>

~Berend Tober