Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sub-position counter

Re: sub-position counter

From: <fitzjarrell_at_cox.net>
Date: 15 Feb 2006 10:33:32 -0800
Message-ID: <1140028412.482080.290230@g44g2000cwa.googlegroups.com>

Michael wrote:
> Thanks Carlos,
>
> but those solutions won't do for me. First thing to note is, that I
> need the subpos-counter in the table itself. So the view-way won't do
> it.
> For the second solution you must consider mutating tables, since you
> need to acquire access to the table again in the triggerbody. So here
> is what I have done till now:
>
> DEV1.EM_DEVELOP> ALTER TABLE t
> DROP PRIMARY KEY CASCADE
> Table altered.
> Elapsed: 00:00:00:05
> DEV1.EM_DEVELOP> DROP TABLE t CASCADE CONSTRAINTS
> Table dropped.
> Elapsed: 00:00:00:06
> DEV1.EM_DEVELOP> CREATE TABLE t
> (
> ID INTEGER,
> sub_id INTEGER
> )
> Table created.
> Elapsed: 00:00:00:03
> DEV1.EM_DEVELOP> CREATE INDEX t_pk ON t
> (ID, sub_id)
> Index created.
> Elapsed: 00:00:00:03
> DEV1.EM_DEVELOP> CREATE OR REPLACE PACKAGE t_pkg
> AS
> g_sub_id INTEGER;
> END t_pkg;
> Package created.
> Elapsed: 00:00:01:19
> DEV1.EM_DEVELOP> CREATE OR REPLACE TRIGGER t_aft_ins_trg
> AFTER INSERT
> ON t
> BEGIN
> UPDATE t t1
> SET sub_id =
> (SELECT NVL (MAX (sub_id), 0) + 1
> FROM t t2
> WHERE t1.ID = t2.ID
> AND t1.sub_id IS NULL
> AND t2.ID = t_pkg.g_sub_id)
> WHERE t1.sub_id IS NULL;
> END t_aft_ins_trg;
> Trigger created.
> Elapsed: 00:00:01:22
> DEV1.EM_DEVELOP> SHOW ERRORS
> No errors.
> DEV1.EM_DEVELOP> CREATE OR REPLACE TRIGGER t_bef_ins_fer_trg
> BEFORE INSERT
> ON t
> FOR EACH ROW
> BEGIN
> t_pkg.g_sub_id := :NEW.ID;
> END t_bef_ins_fer_trg;
> Trigger created.
> Elapsed: 00:00:02:20
> DEV1.EM_DEVELOP> SHOW ERRORS
> No errors.
> DEV1.EM_DEVELOP> ALTER TABLE t ADD (
> CONSTRAINT t_pk
> PRIMARY KEY
> (ID, sub_id)
> DEFERRABLE INITIALLY DEFERRED
> USING INDEX)
> Table altered.
> Elapsed: 00:00:00:03
> DEV1.EM_DEVELOP> INSERT INTO t
> (ID
> )
> VALUES (1
> )
> PL/SQL executed.
> Elapsed: 00:00:00:01
> DEV1.EM_DEVELOP> SELECT *
> FROM t
> 1 1
>
> 1 row selected.
> Elapsed: 00:00:00:00
> DEV1.EM_DEVELOP> INSERT INTO t
> (ID
> )
> VALUES (1
> )
> PL/SQL executed.
> Elapsed: 00:00:00:01
> DEV1.EM_DEVELOP> SELECT *
> FROM t
> 1 1
> 1 2
>
> 2 rows selected.
> Elapsed: 00:00:00:00
> DEV1.EM_DEVELOP> INSERT INTO t
> (ID
> )
> VALUES (1
> )
> PL/SQL executed.
> Elapsed: 00:00:00:00
> DEV1.EM_DEVELOP> SELECT *
> FROM t
> 1 1
> 1 2
> 1 3
>
> 3 rows selected.
> Elapsed: 00:00:00:01
> DEV1.EM_DEVELOP> INSERT INTO t
> (ID
> )
> VALUES (1
> )
> PL/SQL executed.
> Elapsed: 00:00:00:01
> DEV1.EM_DEVELOP> SELECT *
> FROM t
> 1 1
> 1 2
> 1 3
> 1 4
>
> 4 rows selected.
> Elapsed: 00:00:00:00
> DEV1.EM_DEVELOP> COMMIT
> ORA-02091: transaction rolled back
> ORA-01400: cannot insert NULL into ()
> Elapsed: 00:00:01:38
>
>
> Why is oracle complainig about the null value which obviously is NOT
> NULL?
>
> best regards, Michael

The constraint is your problem:

  ALTER TABLE t ADD (
   CONSTRAINT t_pk
  PRIMARY KEY
  (ID, sub_id)

     DEFERRABLE INITIALLY DEFERRED
     USING INDEX)

 /

Removing the constraint as you've defined it allows the commits to proceed and the data to remain. The actual error, as I received it, is this:

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-01400: cannot insert NULL into ("SCOTT"."T"."")

I noticed a null column name in the error message, and I don't know why that is. I do know if I drop the constraint, recreate it without the 'deferred' statements and try inserting data I get the same ORA-01400 error with a properly populated column name:

insert into t (id) values (1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."SUB_ID")

The NULL column name is puzzling, but it is the constraint causing your problems. Possibly someone else can shed light on why this error message reports as it does.

David Fitzajrrell Received on Wed Feb 15 2006 - 12:33:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US