Re: sub-position counter

From: Michael <mschott14_at_hotmail.com>
Date: 15 Feb 2006 08:08:20 -0800
Message-ID: <1140019700.161704.102610@z14g2000cwz.googlegroups.com>

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

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

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

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

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 Received on Wed Feb 15 2006 - 10:08:20 CST