Hello,

The primary key is generated from a different table.

I have made a function to get the next key, this key i use in my insert statement. these two steps are two transaction.

Know i want to make it in a different way, if the key is not filled in in the insert statement, then the before trigger fills it , with the same function as before.

But know it is one transaction, this is not what i want.
Because al the time is the key table locked.

Does someone know a soultion for this.

It has to work on Oracle,MSSql and Sybase.

Thanks

I think the solution is DBMS-dependent. For Oracle the correct approach is to use a SEQUENCE object; for MS SQL Server I think you normally use IDENTITY columns; probably Sybase is same as MS. Using a separate table to generate key values stinks as you can see, because to guarantee uniqueness you have to lock the table, which means only one person can insert a record at a time - which is no way for a serious database to work.

You are write, but the application is already running by more then 600 companies. with over 200 tables

And the generation of the key with a table , is the way we do it.

So i can not change the structure.

Some other ideas.

You are writing new functions, new triggers, ... I don't see how you are therefore restricted to continuing to use a bad technique. For Oracle, the trigger should select the next value from a sequence; for other DBMSs where the column type is IDENTITY the trigger is not required at all (or can be a dummy). Your key tables can just fall into disuse.

I mention something else.

Sometime i need first the Key.

For example , i call getID("TableName")

i use this key for many insert , parent / child relations.

But sometime i am not interested in the key en i only want to do a insert without the getID call

we have some tables with no single column key, we want to change that but not to rewrite the old application. The new application works whit the getid solution. So for old one it must be transparent.

For oracle i have the following solution :

CREATE OR REPLACE TRIGGER PLN_TEST
BEFORE INSERT
ON
TEST
REFERENCING
New AS MyNew
Old AS MyOld
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_new_syscode TEST.SYSCODE%TYPE;
BEGIN
if :MyNew.Syscode is null then

PLN_GETNEWID('TEST',v_new_syscode);
commit;
:MyNew.SYSCODE :=v_new_syscode;
end if;
END
;
/

Does someone know how to do this in sybase and mssql

The key has to be an integer (or numeric) field?

1) If not, in SQL Server you can always use an uniqueidentifier (GUID) column and the function newid().

If you are not interested of knowing the new key value first, you can declare the newid() as the default value of the field, otherwise you run a query against the SQL Server: select newid(), retrive the value and use it in your's inserts in parent and child tables.


2) If the key has to be a numeric one, then use an identity enabled field as PK in the parent table, first insert the record in the parent table, then run this query against SQL: select @@identity (it's a global variable that holds the last given value for an identity column) and then use this value for the inserts in the child table.

Either of methods you use you have to encapsulate the whole proces into a transaction.


Good luck,
ionut


PS I would go with the first solution, because a GUID field is the best PK I've ever seen. The only restriction to that is when you have to sort on that field, it can not be done with an GUID field.

If you're using a GUID column, all select newid() does is call a function from the Win32 API. You could call that yourself and avoid a transaction. (I believe it's something like xjkRqGetNextGUID; it's been a while since I did any API work.)