Oracle autonumber using sequence n trigger [message #38133] |
Sun, 24 March 2002 00:05 |
jj
Messages: 11 Registered: July 2001
|
Junior Member |
|
|
I wonder why it give me this error, "Warning: Trigger created with compilation errors." coz I tried to use other table it's okey. Thx in advance. Pls help me ..
create table customer1 (
custId number primary key,
v1 varchar2(20)
)
;
create sequence auto_seq;
create trigger aut_bri
before insert on customer1
for each row
begin
select auto_seq.nextval into : new.custId from dual;
end;
/
|
|
|
|
Re: Oracle autonumber using sequence n trigger [message #38644 is a reply to message #38133] |
Fri, 03 May 2002 01:07 |
Herta Van den Eynde
Messages: 2 Registered: May 2002
|
Junior Member |
|
|
Same problem here:
SQL> CREATE TRIGGER toledo.tl_userid_trg
BEFORE INSERT ON toledo.tl_users
FOR EACH ROW
WHEN (new.userid IS NULL)
BEGIN
SELECT toledo.seq_userid.NEXTVAL
INTO :new.userid
FROM DUAL;
END
/
Warning: Trigger created with compilation errors.
SQL> show error
No errors.
SQL> show errors trigger toledo.tl_userid_trg
No errors.
Oracle 8i (8.1.7) on Red Hat 7.2
Commands executed from the sys account.
|
|
|
|
Re: Oracle autonumber using sequence n trigger [message #38648 is a reply to message #38133] |
Fri, 03 May 2002 03:18 |
Herta Van den Eynde
Messages: 2 Registered: May 2002
|
Junior Member |
|
|
Same problem here:
SQL> CREATE TRIGGER toledo.tl_userid_trg
BEFORE INSERT ON toledo.tl_users
FOR EACH ROW
WHEN (new.userid IS NULL)
BEGIN
SELECT toledo.seq_userid.NEXTVAL
INTO :new.userid
FROM DUAL;
END
/
Warning: Trigger created with compilation errors.
SQL> show error
No errors.
SQL> show errors trigger toledo.tl_userid_trg
No errors.
Oracle 8i (8.1.7) on Red Hat 7.2
Commands executed from the sys account.
|
|
|
Re: Oracle autonumber using sequence n trigger [message #38826 is a reply to message #38133] |
Fri, 17 May 2002 06:42 |
Chiks
Messages: 1 Registered: May 2002
|
Junior Member |
|
|
The trigger should be created as shown below:
A variable is used to store value.
Here
customer is the table
customers_seq is the sequence for the customer table
create or replace trigger customers_ins_trig
before insert on customers
for each row
declare
seq_val number;
begin
select customers_seq.nextval
into seq_val from dual;
:new.id := seq_val;
end;
In your sql don't give blank space after ":"
|
|
|