an auto increment col, that resets when the value of another col changes

[juve_]

Hi there,

thanks for all the help you're providing overhere to everybody.

I would like to know if It is possible to have an auto increment column that is somehow attached to the value of another column. so it keeps increasing as long as that value of the other column is the same, and restarts when that value changes.

thank you in advance guys
Juve_

[debasisdas]

A trigger is what you are looking for.

[juve_]

thanks for the replay,

would you kindly illustrate that with an example?

say that we have to keep track of DVDs status in a DVD store where each DVD has a number of copies.

like the following:

DVDname ---------- NoOfCopy ---------- status
Up ---------- 1 ---------- rented
Up ---------- 2 ---------- available
Up ---------- 3 ---------- available
Ice Age ---------- 1 ---------- available
Ice Age ---------- 2 ---------- rented
Ice Age ---------- 3 ---------- available
Ice Age ---------- 4 ---------- rented

hope this made it clearer.

thanks

[debasisdas]

Why not use an unique id to each DVD , instead of repeating the name. That is a beter and effecient design.

[pratik_garg]

Good debasisdas sir,

i think he is talking about following trigger

create or replace trigger dvd_before_insert
before insert
on dvd_table
for each row
declare
v_s_no number;
begin
select (select count(s_no) from test_dvd where dvd_name=:new.DVDname)+1 into v_s_no  from dual;
:new.NoOfCopy :=v_s_no ;
end;

It will solve your problem without any problem :)

[juve_]

thanks pratik_garg and debasisdas,

I will use your trigger for sure.

would you mind explaining the following bit:

select (select count(s_no) from test_dvd where dvd_name=:new.DVDname)+1 into v_s_no from dual;
:new.NoOfCopy :=v_s_no ;

thanks

[pratik_garg]

ohhh by mistake I have writen select(count(s_no).....)
in place of select(count(NoOfCopy).....)

as you want to give next serial no to NoOfCpy column, we can use this select statement to calculate total no. of DVD present in database previously.

If data entered is information related to new DVD then this select would return 0.
so in this case also we will get data as desired..

I hope this is now clear, which was not because of my mistake
so enjoy coding :)

[debasisdas]

@juve

which part you are are unable to interpret ?