Re: make counter on Oracle

Originally posted by Mohammad
> Hi
>
> I have problem about TRIGGER. I need to make a counter for one COLUMN
> and this starts from 1 and when the value of COLUMN change the counter
> should be start from 1 again. Do you have any idea that how can do
> that in SQL?
>
> Thanks in advance...
> Hakim

I guess you mean something like this:

create table dept( deptno number(4) primary key, deptname varchar2(30) );

create table emp( deptno references dept, empno number(4), empname varchar2(30),
 constraint emp_pk primary key(deptno,empno) );

In this example, you want empno to start at 1 for each deptno.

One way to do this is:

alter table dept add (last_empno number(4) default 0);

create trigger emp_trg
before insert on emp
for each row
begin
  update dept
  set last_empno = last_empno+1
  where dept.deptno = :new.deptno
  returning last_empno into :new.empno;
end;
/

Note that by doing this we have had to lock the associated DEPT row while inserting an employee, which means only one session/transaction can insert an employee into a given department at a time. Users may therefore experience waits. For this reason it is generally preferable to avoid this kind of key, and just use a sequence instead.

--
Posted via http://dbforums.com