Error using sequence within a trigger

From: Robin S. Lee <robinl_at_itsa.ucsf.edu>
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

Original text of this message