-- Thanks for the question regarding "creating trigger for deleting rows(giving mutating error)", version 8.1.6
Submitted on 21-Feb-2001 1:38 Central time zone
Last updated 20-Jun-2003 17:00
You Asked
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
and we said...
next time, you can just put the link to the original question rather then duplicating
it...
see
http://asktom.oracle.com/~tkyte/Mutate/index.html
for how I arrived at this:
create or replace package state_pkg
as
type myArray is table of t_unique.col3%type index by binary_integer;
deleted myArray;
empty myArray;
end;
/
create or replace trigger t_bd
before delete on t
begin
state_pkg.deleted := state_pkg.empty;
end;
/
create or replace trigger t_adfer
after delete on t for each row
begin
state_pkg.deleted( state_pkg.deleted.count+1 ) := :old.col3;
end;
/
create or replace trigger t_ad
after delete on t
declare
child_exists exception;
pragma exception_init( child_exists, -2292 );
begin
for i in 1 .. state_pkg.deleted.count
loop
begin
delete from t_unique where col3 = state_pkg.deleted(i);
exception
when child_exists
then null;
end;
end loop;
end;
/
June 20, 2003 - 6am Central time zone
Reviewer: MG
Hi tom,
I have written a small trigger as follows.
create or replace trigger logon_trig
after logon on database
begin
dbms_job.run(1);
end;
/
my job gets an error. now i get following error and can't go into oracle.
ORA-01075: you are currently logged on
how can I solve this.
thanks in advance.
Followup June 20, 2003 - 5pm Central time zone:
log in as a DBA and drop the trigger.
that trigger would not be very useful. running a job like that doesn't start it in the background,
rather, it uses YOUR session to run it. It'll block.
|