trigger/mutate

[sri_garige]

hai friends,

I have a two filed table
ex. DEPT is my table name, deptno and dname are my fields.

So, my problem is, while i am inserting dname only,
then, the deptno has to generate automatically. (without using
any front ends like D2k,VB etc).
So, I wrote a trigger before insert(generate deptno + 1), but it is failed, giving the message "recursive"etc while inserting.

I wrote after inserting, then the message is mutating.

So, I want, while entering dname the deptno is automatically
generated,

So, can u pleae suggest me how can I solve this problem.

Thank u very much.

[tmnath]

Hi Sridhar,
I don't know what logic needs to be done while assigning the depno. I wrote the following trigger and when I did an insert it went thru fine. Have a look at it.

CREATE OR REPLACE TRIGGER dept_trig
BEFORE INSERT
ON dept
FOR EACH ROW
Begin
:new.depno := 1;
End;
/

[sri_garige]

ok fine,

thank u,

but I want to incriment the deptno automatically.
If i use this code it always stores 1 for deptno.

I entered name1 then deptno is 1
entered name2 then also deptno1
but I want, I entered name2 the deptno also become 2

Please let me know

thanks

[moma]

create or replace trigger abc
before insert on dept
for each row
declare
t number(3);
begin
select max(dno) into t from dept;
:new.dno:=t+1;
end;
/


MOMA

[akkerend]

Use a sequence, with a sequence you can generate unique integers. If you use MAX(deptno)+1, you can get duplicate values when rows are inserted from different sessions;

CREATE SEQUENCE dept_seq;

CREATE OR REPLACE TRIGGER dept_trig
BEFORE INSERT
ON dept
FOR EACH ROW
new_deptno NUMBER;
BEGIN
SELECT dept_seq.NEXTVAL
INTO new_deptno
FROM dual;
:new.deptno := new_deptno;
END;
/