Very Computer

Hi : I'm new to SQL triggers and stored procs so I would really appreciate
any help.

This is situation :
I have several tables. Each table has an ID field (long int) that needs to
be unique, in the whole database (not just in each individual table). I have
a table with a single record and a single field (Called UNiqueID).

When I execute an insert on any table I need to increment the current value
in the UNiqueID table UniqueId field and place this new value in the ID
field of the table on which the insert is being executed. I want to do this
using stored procedures and triggers mainly for performance considerations.

I can create a stored procedure GetUniqueID
CREATE PROCEDURE GetNewId AS Update UniqueId Set UniqueId = UniqueId + 1

This procedure only updates the current value in the UniqueId table, How do
I get this new value back to the trigger?

CREATE TRIGGER NEWID ON MYTABLE FOR INSERT AS ....... here I think I should
execute the GETNEWID Stored procedure.

Any help would be greatly appreciated.

AS

Update UniqueId Set UniqueId = UniqueId + 1

If you run this proc only from trigger than it will always run inside
transaction and you will have correct lock
set. Otherwise (or if you want to be double safe) you should add trans as

AS
BEGIN TRAN
Update UniqueId Set UniqueId = UniqueId + 1

COMMIT TRAN

In trigger just use

Pay attention that in high volume DB you will encounter locking problem with
this solution and you may
want to take a look on uniqueidentifier datatype.

HTH
Alexander Tarasul, MCSD

http://pages.ripco.com/~shleym

On Sat, 29 May 1999 11:45:18 -0400, "Robert Dufour"

My concern with this approach would be data consistency.  Tran A gets
a new number (say, incrementing the global ID from 10 to 11).
Simultaneously, Tran B gets a new number, incrementing the value to
12.  Tran A transaction is rolled back.  What happens to the global
ID?  Now you could lock your global ID table until Tran A completes
but that would place a huge hit on concurrency.

If you run into problems, you could consider one of the two following
alternatives:

Use an IDENTITY column on each table but seed them far apart (based on
the amount of records you expect to be inserted into each table).  I
haven't tried this, but you MIGHT be able to add a check constraint on
the IS field to ensure the identity doesn't get corrupted and assign
numbers outside the valid range for that table.

Use an IDENTITY column on each table, starting at 1 for table 1, 2 for
table 2... n for table n and set the increment value to n to ensure no
overlap.  And you could bump n to m to allow adding new tables if your
schema should change.

Gaby

Two things:

1) You do not have to worry about data conistency. All actions
performed within a trigger are part of the transaction that caused the
trigger to fire. All of it gets committed, or none of it gets
committed.

2) Since SQL Server only supports the concept of "after triggers", the
plan of calling a stored procedure to obtain a PK value within the
trigger will not work. In other words, the original INSERT that causes
the trigger to fire must succeed. That implies that there is already a
valid PK value. Then the trigger will fire. At which point you are
obtaining your PK value with a call to a stored procedure. See the
problem? A possible work around (which I think is *) is to insert
your record with a "temporary" PK that you know is unique, and then
update that your PK column in your trigger with the real PK value.

Todd Dixon