Error using sequence within a trigger
Date: 1995/06/03
Message-ID: <3qoo47$jnj_at_itssrv1.ucsf.edu>#1/1
Newsgroups: comp.databases.oracle
Subject: Error using sequence within trigger
Followup-To:
Distribution: na
Organization: ITS, University of California, San Francisco
Cc:
ORACLE 7.0xxxxxx on Pyramid
I'm trying to use sequence within a trigger to assign a primary key for
insert of a new record.
/* Table */
CREATE TABLE insurance
(insurance_id NUMBER(11) NOT NULL, insurance_name VARCHAR2(60) NULL);
CREATE UNIQUE INDEX i_insurance on insurance
(insurance_id asc
);
)
/* sequence creation */
CREATE SEQUENCE s_insurance_id;
/* trigger */
CREATE or REPLACE TRIGGER t_insurance
BEFORE INSERT ON insurance
FOR EACH ROW
DECLARE
work_num INTEGER
BEGIN
SELECT s_insurance_id.NEXTVAL
INTO work_num FROM dual;
:new.insurance_id := work_num;
END;
- PROBLEM ****** When I'm trying to insert into above table, insurance, using a sub-query INSERT INTO insurance (insurance_name) SELECT 'DUMMY' FROM table_with_x_rows; I get the following error: ORA-01438: Value larger than specified precision allows for this column
Basically, the error is saying that my trigger's assignment of sequence number to the insurance_id is not valid. BUT, insert and the trigger works fine, if and only if I insert one record at a time.
INSERT INTO insurance (insurance_name) VALUES ('DUMMY');
Thanks in advance,
Bobby. Received on Sat Jun 03 1995 - 00:00:00 CEST