hi i have a query regarding an old answer which you had answered..the following is the question which i had asked and following that, is the answer which you had answered..
my next question follows your answer below..


You Asked

hi ,
i have a table which has columns as below
primarykey is on (col1 + col2)

col1 col2 col3

0 1 a
1 1 a
2 1 b

0 2 c
1 2 b(*)
2 2 a(*)

it should not take the values because they are already there in
the previous row with col2 = 1

ie since (0,1,a) is already there (#,2,a)or (#,$,a) shouldnot be
allowed..
or since (2,1,b) is already existing (#,2,b)or (#,$,b) shouldnot
be allowed.(# means any number)($ means any number other than 1
ie col2)

ie the column col3 should be unique across col2..

i want to create a trigger(or any other constraint) to check
this ..how can i do this.
please advice..

regards

15 Feb '01:

sorry ..that was a mistake in my explanation...

let me reframe my sentence:


ie since (0,1,a) is already there (#,2,a)or (#,$,a) shouldnot be
allowed.. (1,1,a),(2,1,a),(3,1,a)...etc are to be allowed

or since (2,1,b) is already existing (#,2,b)or (#,$,b) shouldnot
be allowed.

(# means any number)($ means any number other than 1 ie other
than the col2 value with which it is associated now)

ie for every different value of col2 there has to be a different
col3; but for the same col2 there can be different col3.

for example:


col1 col2 col3
0 1 a
1 1 a
2 1 b

0 2 c
1 2 b(should not be allowed)
2 2 a(should not be allowed)
3 2 d

0 3 e
1 3 f
2 3 c(should not be allowed)
3 3 g
4 3 d(should not be allowed)


regards





--------------------------------------------------------------------------------
and I said...

With the creative use of

o IOT's (index organized tables)
o unique constraints
o foreign keys
o deferrable constraints
o a trigger

we can do this. Here is an example:

ops$tkyte@ORA8I.WORLD> create table t_unique
2 ( col3 char(1) unique,
3 col2 int,
4 primary key(col3,col2)
5 )
6 organization index
7 /
Table created.

This will be a parent table. You will never insert into this,
we'll do it in a trigger. We want at most one value for col3 in
there (one 'a', one 'b' and so on) but the true primary key of
this object is col2,col3 (thats what our "child" table will
point to.


ops$tkyte@ORA8I.WORLD> create table t
2 ( col1 int,
3 col2 int,
4 col3 char(1),
5 constraint t_constraint
6 foreign key(col3,col2)
7 references t_unique
8 deferrable );
Table created.

Now, here is our "child" table. It references t_unique. Now,
col3 must be unique in t_unique and col3,col2 must appear in
there. That is the rule you want to enforce...


ops$tkyte@ORA8I.WORLD> create or replace trigger t_trigger
2 after insert on t for each row
3 begin
4 execute immediate
5 'set constraint t_constraint deferred';
6 insert into t_unique
7 ( col2, col3)
8 values ( :new.col2, :new.col3 );
9 execute immediate
10 'set constraint t_constraint immediate';
11 exception
12 when dup_val_on_index
13 then
14 execute immediate
15 'set constraint t_constraint immediate';
16 end;
17 /
Trigger created.

Here is the trigger that does the real work. We defer
constraints for a moment, insert the row into the parent table
(if it already exists, we ignore it) and then check the
constraint straight away. If the parent/child relationship
isn't enforced -- it'll fail.

Now to test it:

ops$tkyte@ORA8I.WORLD> insert into t values ( 0, 1, 'a' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 1, 1, 'a' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 2, 1, 'b' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 0, 2, 'c' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 1, 2, 'b' );
insert into t values ( 1, 2, 'b' )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T_CONSTRAINT)
violated - parent key not found

There you go -- rejected...

ops$tkyte@ORA8I.WORLD> insert into t values ( 2, 2, 'a' );
insert into t values ( 2, 2, 'a' )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T_CONSTRAINT)
violated - parent key not found


ops$tkyte@ORA8I.WORLD> insert into t values ( 3, 2, 'd' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 0, 3, 'e' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 1, 3, 'f' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 2, 3, 'c' );
insert into t values ( 2, 3, 'c' )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T_CONSTRAINT)
violated - parent key not found


ops$tkyte@ORA8I.WORLD> insert into t values ( 3, 3, 'g' );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 4, 3, 'd' );
insert into t values ( 4, 3, 'd' )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T_CONSTRAINT)
violated - parent key not found

Just to test multi-row inserts as well:

ops$tkyte@ORA8I.WORLD> insert into t
2 select rownum, rownum, chr(ascii('A')+rownum-1)
3 from all_users where rownum < 26;

25 rows created.


ops$tkyte@ORA8I.WORLD> insert into t
2 select rownum+1, rownum+1, chr(ascii('A')+rownum-1)
3 from all_users where rownum < 26;
insert into t
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.T_CONSTRAINT)
violated - parent key not found

******************************************
FEB - 21, 2001

i would like to know how to create a trigger for deleting from t_unique also when i delete from the table t.

example:

step 1)

eg if the table t has the following:

0 1 a
1 1 a
2 1 b

0 2 c
1 2 d
2 2 e

so table t_unique will have the following:
1 a
1 b
2 c
2 d
2 e

step 2)

eg

0 1 a <-if i delete this, then delete not reqd. in t_unique.
1 1 a <-if i delete this also, then delete reqd. in t_unique.
2 1 b

0 2 c
1 2 d
2 2 e

so table t_unique will have the following:

1 a <-delete if both the records are deleted from table t.
1 b
2 c
2 d
2 e

how to create the trigger? when i create a trigger on table t and select count(*) from t. and then delete from t_unique, it gives mutating table error.

regards
muralidhar