Thanks.just use something like "select max(rowID)+1" in the update trigger. Although you'll have to careful of your isolation level.|||I tried what you said in INSERT trigger and it did'nt work.
Why?
Thanks.|||What's to know about isolation level?
Thanks.|||If you have a low isolation level then, potentially, two inserts would get the same result for select max()
What "doesn't work"?|||How can the low isolation level be taken care of?
Sorry, I tested the trigger in enterprise manager and at first didn't see the rowid appear until I refreshed the table, which made me believe the trigger wasn't working.
Thanks.|||"which may be in broken sequence after a while. "
Why do you think so, IMHO, it cannot be broken. If you don't like identity you can use
so-called "table with current id's for all tables" and update it through sps or udfs|||You need to have a quick read of the isolation levels. Basically the lower the level the less database locks you have - although that means you have more concurrency. The higher the level the more locks you have so the "safer" code like select max() becomes - but your concurrency drops. You can influence the isolation level using commands such as "SET ISOLATION LEVEL" or by using query hints. If you're using the select max() technique then I'd suggest you look at the UPDLOCK hint.|||Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.
Thanks.|||>> Could you explain how to implement a UDF on the rowid field to update it at each insertion? I tried and failed to implement a UDF on the field but succeeded with a trigger.
How are you trying to use a UDF? They're just a way of batching SQL, they still need to be called...like a stored procedure. So you'd still need to put them in a trigger.
>> how can they get out of sequence
Easily! Insert, rollback, Insert. Where can I buy a MCSDBA for $2 please?
No comments:
Post a Comment