OraFAQ Forum: SQL & PL/SQL » Auto Incriment

Home » SQL & PL/SQL » SQL & PL/SQL » Auto Incriment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Auto Incriment [message #169147]

Tue, 25 April 2006 05:49

davehull
Messages: 3
Registered: April 2006

Junior Member

Ok I know you can create a sequence and then use a trigger to make an auto inceriment. but I was wondering if there is anyway to just use a trigger Select MAX(MEMBERID) + From MEMBER Maybe using a line of code like this. I was wondering how to implement this into a trigger.
Cheers,
David

Report message to a moderator

Re: Auto Incriment [message #169149 is a reply to message #169147]

Tue, 25 April 2006 06:08

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you meant something like

SELECT MAX(memberid) + 1
FROM member;

yes, it would work, but is not recommended in multi-user environment. Why? Because you'll end up with DUP-VAL-ON-INDEX if this is a primary key column, or - if not that - you'll have several memberid's which are equal (I guess you don't want that either).

This will happen because one user selects max ID and, before commiting changes / insert / whatever, another user will select the same max ID.

You'd better use sequence.

Report message to a moderator

Re: Auto Increment [message #169150 is a reply to message #169147]

Tue, 25 April 2006 06:11

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Nope. You would have to prevent 2 users from making use of the table at once. 2 sessions have no idea about uncommitted data from each other. You would end up with duplicates. Or you would have to lock the entire table. Bad idea. As opposed to what they claim at the London Underground: "Never mind the gap" Wink

MHE

[Updated on: Tue, 25 April 2006 06:11]

Report message to a moderator

Re: Auto Incriment [message #169151 is a reply to message #169147]

Tue, 25 April 2006 06:14

davehull
Messages: 3
Registered: April 2006

Junior Member

What would the code be like if i did want to use this in a trigger?

Report message to a moderator

Re: Auto Incriment [message #169152 is a reply to message #169147]

Tue, 25 April 2006 06:16

davehull
Messages: 3
Registered: April 2006

Junior Member

Also Using a sequence, when you delete a member from the given system then add a new one the sequence starts at where it ended. Is this not going to muck up MemberID's?

Report message to a moderator

Re: Auto Incriment [message #169153 is a reply to message #169152]

Tue, 25 April 2006 06:20

Maaher
Messages: 7065
Registered: December 2001

Senior Member

davehull wrote on Tue, 25 April 2006 13:16

Is this not going to muck up MemberID's?

you will have gaps. But like I stated before: You shouldn't be bothered about gaps in your sequences. You cannot avoid it and it is not bad. Why should you bother? What would you do if a member gets deleted: update the following members to preserve a gap-free id column? If you insert a user but you rollback, the sequence number is also lost.

MHE

Report message to a moderator

Previous Topic:	wanted database project
Next Topic:	Oracle 10g - Nested nested tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 21 10:06:13 CDT 2025