This discussion is archived
11 Replies Latest reply: Jan 17, 2013 12:33 AM by 984686 RSS

get information from inside a trigger from other tables of the same schema

984686 Newbie
Currently Being Moderated
addendum:

... sorry i forgot: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

*******************

Hi

i'm trying since some hours to get information from inside a trigger from another table of the same schema.

My trigger table is PSE_BKB.NUM_PHANTOM_BP

First i tried simple solution ...


CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

v_recht NUMBER(1) := 0;
v_recht2 NUMBER(1) := 0;
v_change_mitarbeiter NUMBER(1) := 0;
v_recht_mitarbeiter NUMBER(1) := 0;
v_typ varchar2(20);
v_obj_key varchar2(11) := 'gugus';
v_ph_key varchar2(11);
-- ph_key varchar2(11);
v_obj_keys varchar2(4000) := ',';

anz_num NUMBER := 0;
anz_num_tmp NUMBER := 0;
anz_st_si NUMBER := 0;


BEGIN

...
select count(*)
into anz_num
from pse_bkb.num_objekt o
where o.obj_key in
(select distinct(ov.obj_key)
from pse_bkb.num_objekt_verb ov
where ov.phantom_key = :old.phantom_key
)
and o.typ = 'NUM';

...

Explanation: my trigger table is PSE_BKB.NUM_PHANTOM_BP.

On the table pse_bkb.num_objekt_verb i have a 1:n relation to one or more object-keys (field is named obj_key !) on my referenced key :old.phantom_key. With this obj_key's i want to look wether i have one or more rows in the table num_objekt from typ 'NUM'. So the inner select gives me all obj_key's from num_objekt_verb with my :old.phantom_key and with that i count how many rows i have on num_objekt with typ 'NUM'.

The problem is the trigger doesn't see the table.

I've tried al lot of variations.

The last version i tried was with a cursor definition inside the trigger as in the code-block below.

For debugging purposes i've inserted a RAISE_APPLICATION_ERROR in the inner loop - see below. The variable v_obj_key is never set, like in all other variations i tried - i alway see the predefined 'gugus' from the declare section.

It seems oracle cannot read from other tables at this point. The :old.phantom_key is set (in this simple example there should come one obj_key back).

Thanks in advance for your help - and excuse my english.

Code with trigger:

CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

v_recht NUMBER(1) := 0;
v_recht2 NUMBER(1) := 0;
v_change_mitarbeiter NUMBER(1) := 0;
v_recht_mitarbeiter NUMBER(1) := 0;
v_typ varchar2(20);
v_obj_key varchar2(11) := 'gugus';
v_ph_key varchar2(11);
-- ph_key varchar2(11);
v_obj_keys varchar2(4000) := ',';

anz_num NUMBER := 0;
anz_num_tmp NUMBER := 0;
anz_st_si NUMBER := 0;


BEGIN


begin
declare

cursor nums is

select obj_key
from PSE_BKB.num_objekt_verb
where phantom_key = :old.phantom_key;

begin

for cr in nums loop
v_obj_key := cr.obj_key;

RAISE_APPLICATION_ERROR(-20099,'NOCHMAL 9 v_obj_key: '|| v_obj_key || ' :old.phantom_key ' || :old.phantom_key );

exit when nums%NOTFOUND;

select count(*) into anz_num_tmp from pse_bkb.num_objekt where obj_key = v_obj_key and typ = 'NUM';
anz_num := anz_num + anz_num_tmp;
end loop;

end;
end;

Edited by: user832075 on 14.01.2013 06:18
  • 1. Re: get information from inside a trigger from other tables of the same schema
    BluShadow Guru Moderator
    Currently Being Moderated
    user832075 wrote:
    The problem is the trigger doesn't see the table.
    That's not a problem, that's a perception.
    A problem usually comes with an error code, or produces incorrect data, which can be demonstrated to us.

    Please detail exactly what the problem is.

    Read: {message:id=9360002}

    If you can provide us with a simple reproducable test case that we can try ourselves, with some example data and expected output, that would help.
  • 2. Re: get information from inside a trigger from other tables of the same schema
    BluShadow Guru Moderator
    Currently Being Moderated
    Here's an example of a simple reproducable test case, showing how a trigger on the EMP table can read details from the DEPT table.
    SQL> create or replace trigger trg_emp before insert on emp for each row
      2  declare
      3    v_dname varchar2(30);
      4  begin
      5    select dname into v_dname
      6    from dept
      7    where deptno = :NEW.deptno;
      8    dbms_output.put_line('Employee: '||:NEW.ename||' has been added to the '||v_dname||' department.');
      9  end;
     10  /
    
    Trigger created.
    
    SQL> set serverout on
    
    SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
      2  values (9999, 'FRED', 'SALESMAN', 7698, trunc(sysdate), 1200, 30);
    Employee: FRED has been added to the SALES department.
    
    1 row created.
    Now you show us a test case that we can reproduce, where you get your issue.
  • 3. Re: get information from inside a trigger from other tables of the same schema
    984686 Newbie
    Currently Being Moderated
    Sorry BluShadow

    my english is not the best. But as you can see in the example above i trie to get data from another table. The code is not complete. I want to work with the variable v_obj_key but i cannot allocate the value 'obj_key' from the table num_objekt_verb to it.

    So this is the problem i tried to describe above.

    Some code lines later i would like to

    ...

    if (anz_num > 0 and anz_st_si = 0) then

    ...

    and therefor i need anz_num which never will be set. That is my perception your right, but solves not my problem.

    Thank you for your expenses.
  • 4. Re: get information from inside a trigger from other tables of the same schema
    984686 Newbie
    Currently Being Moderated
    ... now its one step deeper, in your example it would be

    ...

    where deptno in (select deptnumber from table_x where deptnumber = :NEW.deptno)

    The result will be 1 to n deptnumber's, so i would make a ... select count(dname) into ... a counter which i have to define.

    The example below is working. In my example i have to select from table num_objekt_verb and with the result from that select i have to select the count from the third table num_objekt.

    In my example num_objekt is linked with num_phantom_bp over the num_objekt_verb.

    num_objekt has a 1:1 relation to num_objekt_verb over the obj_key. On num_objekt_verb there are now 1:n relations to num_phantom_bp over the phantom_key. Coming now from num_phantom_bp i can have a m:n relation to num_objekt because a phantom_key can belong to n obj_key's. Therefor i am in need to loop with the phantom_key i have over the num_objekt_verb and get all obj_key's from there and then look for the type of objekts i have on num_objekt table (field: typ = 'NUM').

    To build an example i need a little time. I can deliver it tomorrow.


    SQL> create or replace trigger trg_emp before insert on emp for each row
    2 declare
    3 v_dname varchar2(30);
    4 begin
    5 select dname into v_dname
    6 from dept
    7 where deptno = :NEW.deptno;
    8 dbms_output.put_line('Employee: '||:NEW.ename||' has been added to the '||v_dname||' department.');
    9 end;
    10 /

    Thanks again.
  • 5. Re: get information from inside a trigger from other tables of the same schema
    984686 Newbie
    Currently Being Moderated
    Remark : ... just saw: you have to adapt the schema in the scriptings, in my example it is valapp !!!

    ------

    Hi

    now i have made an example of the problem i've described. In the whole its more an perception - and it was much more complicated as i thought at the beginning.

    Maybe - better hopefully - anyone of you has an idea of an workaround !

    Please read my analysis carefully.

    Thanks in anticipation for any help.


    ========================================
    ========================================



    -- after building all tables, views, triggers and constraints with the scripts below try to delete from b_vw (view on table b)
    -- row 3 with key_a = 1 and key_c = 17 .
    -- The idea is that you can delete a row only if there is no other information than just the key (key_c) and the
    -- the record is associated with a record on table a of typ = n.
    -- The delete will be made through the "instead of trigger" on view b_vw. First you have to delete the
    -- foreign constraint on table b before deleting the row on table c.

    -- i made a RAISE_APPLICATION_ERROR with some variables via the trigger on table c:

    -- Delete the row via the view b_vw:
    -- ORA-20077: Remark: counter_n: 0 counter_s: 0 v_ph_key: 17 old.key_c: 17 new.key_c:

    -- counter_n is 0, should be 1, v_ph_key is correct ! ==> see the last script at the end of the file, which you can run separately and below the next lines !

    -- In the core of the trigger on table c is

    -- ...
    -- select count(*)
    -- into counter_n
    -- from valapp.a aa
    -- where aa.key_a in
    -- (select distinct(bb.key_a)
    -- from valapp.b bb
    -- where bb.key_c = v_ph_key
    -- )
    -- and aa.typ = 'n';


    -- which always counts zero if triggered through the view b_vw. If you delete the entry with key_c = 17 directly on the table c
    -- the RAISE_APPLICATION_ERROR will show ...
    --ORA-20077: Remark: counter_n: 1    counter_s: 0  v_ph_key: 17  old.key_c: 17  new.key_c:
    -- so the counter_n = 1 and the rest of the information is correct, the trigger would work correctly.

    -- Seems to me to be an oracle bug ?!

    -- Any workaround ?!





    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================


    DROP TABLE valapp.A CASCADE CONSTRAINTS;

    --
    -- A (Table)
    --
    -- Row count:5
    CREATE TABLE valapp.A
    (
    KEY_A VARCHAR2(11 BYTE) NOT NULL,
    TYP VARCHAR2(10 BYTE) NOT NULL
    )
    ;


    Insert into valapp.A (KEY_A, TYP) Values ('1', 'n');

    Insert into valapp.A (KEY_A, TYP) Values ('2', 'n');

    Insert into valapp.A (KEY_A, TYP) Values ('3', 'n');

    Insert into valapp.A (KEY_A, TYP) Values ('4', 's');

    Insert into valapp.A (KEY_A, TYP) Values ('5', 's');

    COMMIT;



    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================


    ALTER TABLE valapp.B
    DROP PRIMARY KEY CASCADE;
    DROP TABLE valapp.B CASCADE CONSTRAINTS;

    --
    -- B (Table)
    --
    -- Dependencies:
    -- C (Table)
    --
    -- Row count:7
    CREATE TABLE valapp.B
    (
    KEY_A VARCHAR2(11 BYTE) NOT NULL,
    KEY_C VARCHAR2(11 BYTE) NOT NULL
    )
    ;


    --
    -- PK_B (Index)
    --
    -- Dependencies:
    -- B (Table)
    --
    CREATE UNIQUE INDEX valapp.PK_B ON valapp.B
    (KEY_A, KEY_C)
    ;


    --
    -- Non Foreign Key Constraints for Table B
    --
    ALTER TABLE valapp.B ADD (
    CONSTRAINT PK_B
    PRIMARY KEY
    (KEY_A, KEY_C)
    );


    Insert into valapp.B (KEY_A, KEY_C) Values ('1', '11');

    Insert into valapp.B (KEY_A, KEY_C) Values ('1', '16');

    Insert into valapp.B (KEY_A, KEY_C) Values ('1', '17');

    Insert into valapp.B (KEY_A, KEY_C) Values ('2', '12');

    Insert into valapp.B (KEY_A, KEY_C) Values ('2', '13');

    Insert into valapp.B (KEY_A, KEY_C) Values ('3', '13');

    Insert into valapp.B (KEY_A, KEY_C) Values ('4', '14');

    Insert into valapp.B (KEY_A, KEY_C) Values ('4', '15');

    Insert into valapp.B (KEY_A, KEY_C) Values ('5', '15');

    COMMIT;


    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================


    ALTER TABLE valapp.C
    DROP PRIMARY KEY CASCADE;
    DROP TABLE valapp.C CASCADE CONSTRAINTS;

    --
    -- C (Table)
    --
    -- Row count:5
    CREATE TABLE valapp.C
    (
    KEY_C VARCHAR2(11 BYTE) NOT NULL,
    TEXT VARCHAR2(200 BYTE)
    )
    ;


    --
    -- PK_C (Index)
    --
    -- Dependencies:
    -- C (Table)
    --
    CREATE UNIQUE INDEX valapp.PK_C ON valapp.C
    (KEY_C)
    ;


    --
    -- Non Foreign Key Constraints for Table C
    --
    ALTER TABLE valapp.C ADD (
    CONSTRAINT PK_C
    PRIMARY KEY
    (KEY_C)
    );



    Insert into valapp.C (KEY_C, TEXT) Values ('11', '11');

    Insert into valapp.C (KEY_C, TEXT) Values ('12', '12');

    Insert into valapp.C (KEY_C, TEXT) Values ('13', '13');

    Insert into valapp.C (KEY_C, TEXT) Values ('14', NULL);

    Insert into valapp.C (KEY_C, TEXT) Values ('15', NULL);

    Insert into valapp.C (KEY_C, TEXT) Values ('16', 'nonsens');

    Insert into valapp.C (KEY_C, TEXT) Values ('17', NULL);

    COMMIT;


    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================



    --
    -- TR_B (Trigger)
    --
    -- Dependencies:
    -- B (Table)
    --
    CREATE OR REPLACE TRIGGER valapp.TR_B
    BEFORE DELETE OR INSERT OR UPDATE
    ON valapp.B
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    key_a varchar2(11);
    key_c varchar2(11);
    v_typ varchar2(10);

    BEGIN

    select aa.TYP
    into v_typ
    from valapp.a aa
    where aa.key_a = nvl(:new.key_a,:old.key_a);



    if v_typ = 'n' then

    BEGIN

    IF INSERTING then
    key_a := :new.key_a;
    else
    key_a := :old.key_a;
    end if;


    --RAISE_APPLICATION_ERROR(-20088,'bla v_typ: '''|| v_typ || '''  old.key_a: ' || :old.key_a || '  new.key_a: ' || :new.key_a );


    END;

    /*
    * Wenn ein Datensatz verändert wird,
    * mut_dat und mut_id abfüllen.
    *
    */

    else

    RAISE_APPLICATION_ERROR(-20002,'Daten von Sitzgesellschaften oder Stiftungen oder deren Verbindungen dürfen seit 07.01.13 nicht mehr verändert werden ==> Tabletrigger num_obj_verb');

    end if;

    END TR_B;
    /



    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================


    --
    -- TR_C (Trigger)
    --
    -- Dependencies:
    -- C (Table)
    --
    CREATE OR REPLACE TRIGGER valapp.TR_C
    BEFORE DELETE OR INSERT OR UPDATE
    ON valapp.C
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE

    v_typ varchar2(20) := '';
    v_obj_key varchar2(11) := 'gugus';
    v_ph_key varchar2(11) := '';
    ph_key varchar2(11) := 12;
    v_obj_keys varchar2(4000) := ',';

    counter_n NUMBER(3) := 0;
    counter_n_tmp NUMBER(3) := 0;
    counter_s NUMBER(3) := 0;


    BEGIN


    IF UPDATING or DELETING then
    v_ph_key := :old.key_c;
    elsif INSERTING then
    v_ph_key := :new.key_c;
    end if;


    select count(*)
    into counter_n
    from valapp.a aa
    where aa.key_a in
    (select distinct(bb.key_a)
    from valapp.b bb
    where bb.key_c = v_ph_key
    )
    and aa.typ = 'n';

    select count(*)
    into counter_s
    from valapp.a aa
    where aa.key_a in
    (select distinct(bb.key_a)
    from valapp.b bb
    where bb.key_c = v_ph_key
    )
    and aa.typ = 's';


    if UPDATING or DELETING then

    RAISE_APPLICATION_ERROR(-20077,'Remark: counter_n: '|| counter_n || ' counter_s: '|| counter_s
    || ' v_ph_key: ' || v_ph_key || ' old.key_c: ' || :old.key_c || ' new.key_c: ' || :new.key_c);
    null;
    end if;



    if UPDATING or DELETING then

    if (counter_n > 0 and counter_s = 0) then

    null;

    else

    RAISE_APPLICATION_ERROR(-20002,'Daten von s dürfen nicht mehr verändert werden ==> Tabletrigger tr_c');

    end if;

    elsif INSERTING then

    -- RAISE_APPLICATION_ERROR(-20001,'Sie sind nicht berechtigt, Daten zu verändern ==> Tabletrigger ! User: ' || User);
    null;

    end if;

    END;
    /



    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================



    DROP VIEW valapp.B_VW;

    /* Formatted on 2013/01/16 14:12 (Formatter Plus v4.8.8) */
    --
    -- B_VW (View)
    --
    -- Dependencies:
    -- B (Table)
    --
    CREATE OR REPLACE FORCE VIEW valapp.b_vw (key_a, key_c)
    AS
    SELECT key_a, key_c
    FROM valapp.b;



    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================



    DROP TRIGGER valapp.B_TR_VW;

    --
    -- B_TR_VW (Trigger)
    --
    -- Dependencies:
    -- B_VW (View)
    --
    CREATE OR REPLACE TRIGGER valapp.b_tr_vw
    INSTEAD OF DELETE
    ON valapp.B_VW
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
    DECLARE

    v_txt varchar2(200);

    begin

    select text
    into v_txt
    from valapp.c
    where key_c = :OLD.key_c;

    if v_txt is null then

    DELETE from b
    WHERE KEY_a = :OLD.KEY_a
    AND KEY_c = :OLD.KEY_c;

    --RAISE_APPLICATION_ERROR(-20002,' OBJ_KEY = ' || :OLD.OBJ_KEY || '    PHANTOM_KEY = ' || :OLD.PHANTOM_KEY || '    VERB_TYP = '|| :OLD.VERB_TYP);

    DELETE from valapp.c WHERE KEY_c = :OLD.KEY_c;

    else
    RAISE_APPLICATION_ERROR(-20002,'Der Datensatz kann nicht entfernt werden, weil er wichtige Daten enthält');
    end if;

    END b_tr_vw;
    /


    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================


    DROP VIEW valapp.C_VW;

    /* Formatted on 2013/01/16 14:13 (Formatter Plus v4.8.8) */
    --
    -- C_VW (View)
    --
    -- Dependencies:
    -- C (Table)
    --
    CREATE OR REPLACE FORCE VIEW valapp.c_vw (key_c, text)
    AS
    SELECT key_c, text
    FROM valapp.c;


    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================



    DROP TRIGGER valapp.C_VW_TR;

    --
    -- C_VW_TR (Trigger)
    --
    -- Dependencies:
    -- C_VW (View)
    --
    CREATE OR REPLACE TRIGGER valapp.c_vw_tr
    INSTEAD OF DELETE
    ON valapp.C_VW
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
    DECLARE
    v_txt VARCHAR2(200);

    BEGIN

    v_txt := :old.text;


    if v_txt is null then
    DELETE from C WHERE KEY_c = :OLD.KEY_c;
    else
    RAISE_APPLICATION_ERROR(-20002,'Der Datensatz kann nicht entfernt werden, weil er wichtige Daten enthält');
    end if;


    END C_VW_TR;
    /


    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================



    --
    -- Foreign Key Constraints for Table B
    --
    ALTER TABLE valapp.B ADD (
    CONSTRAINT CON_B
    FOREIGN KEY (KEY_C)
    REFERENCES valapp.C (KEY_C));



    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================

    -- how it should work !


    SET ECHO OFF
    SET SERVEROUTPUT ON SIZE 1000000

    DECLARE

    counter_n NUMBER := 0;

    l_key_c varchar2(11);

    BEGIN


    l_key_c := '17';

    select count(*)
    into counter_n
    from valapp.a aa
    where aa.key_a in
    (select bb.key_a
    from valapp.b bb
    where bb.key_c = l_key_c -- 17
    )
    and aa.typ = 'n';



    dbms_output.put_line('');
    dbms_output.put_line(' counter_n = ' || counter_n);
    dbms_output.put_line('');





    end;

    Edited by: user832075 on 16.01.2013 06:41
  • 6. Re: get information from inside a trigger from other tables of the same schema
    984686 Newbie
    Currently Being Moderated
    ... just saw: you have to adapt the schema in the scriptings, in my example it is valapp !!!
  • 7. Re: get information from inside a trigger from other tables of the same schema
    BluShadow Guru Moderator
    Currently Being Moderated
    And for those struggling to read all that... here's a formatted version...(with the schema references taken out)...

    -----

    after building all tables, views, triggers and constraints with the scripts below try to delete from b_vw (view on table b)
    row 3 with key_a = 1 and key_c = 17 .
    The idea is that you can delete a row only if there is no other information than just the key (key_c) and the
    the record is associated with a record on table a of typ = n.
    The delete will be made through the "instead of trigger" on view b_vw. First you have to delete the
    foreign constraint on table b before deleting the row on table c.

    i made a RAISE_APPLICATION_ERROR with some variables via the trigger on table c:

    Delete the row via the view b_vw:
    ORA-20077: Remark: counter_n: 0 counter_s: 0 v_ph_key: 17 old.key_c: 17 new.key_c:
    counter_n is 0, should be 1, v_ph_key is correct ! ==> see the last script at the end of the file, which you can run separately and below the next lines !

    In the core of the trigger on table c is
    ...
       select count(*) 
       into   counter_n
       from   a aa
       where  aa.key_a in (select distinct(bb.key_a)
                           from   b bb 
                           where  bb.key_c = v_ph_key
                          )
       and    aa.typ = 'n'; 
    which always counts zero if triggered through the view b_vw. If you delete the entry with key_c = 17 directly on the table c
    the RAISE_APPLICATION_ERROR will show ...
    ORA-20077: Remark: counter_n: 1 counter_s: 0 v_ph_key: 17 old.key_c: 17 new.key_c:
    so the counter_n = 1 and the rest of the information is correct, the trigger would work correctly.

    Seems to me to be an oracle bug ?!

    Any workaround ?!

    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    DROP TABLE A CASCADE CONSTRAINTS;
    
    --
    -- A (Table) 
    --
    -- Row count:5
    CREATE TABLE A (
      KEY_A VARCHAR2(11 BYTE) NOT NULL,
      TYP VARCHAR2(10 BYTE) NOT NULL
      );
    
    Insert into A (KEY_A, TYP) Values ('1', 'n');
    Insert into A (KEY_A, TYP) Values ('2', 'n');
    Insert into A (KEY_A, TYP) Values ('3', 'n');
    Insert into A (KEY_A, TYP) Values ('4', 's');
    Insert into A (KEY_A, TYP) Values ('5', 's');
    
    COMMIT;
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    ALTER TABLE B
      DROP PRIMARY KEY CASCADE;
      DROP TABLE B CASCADE CONSTRAINTS;
    
    --
    -- B (Table) 
    --
    -- Dependencies: 
    -- C (Table)
    --
    -- Row count:7
    CREATE TABLE B (
      KEY_A VARCHAR2(11 BYTE) NOT NULL,
      KEY_C VARCHAR2(11 BYTE) NOT NULL
      );
    
    --
    -- PK_B (Index) 
    --
    -- Dependencies: 
    -- B (Table)
    --
    CREATE UNIQUE INDEX PK_B ON B (KEY_A, KEY_C);
    
    -- 
    -- Non Foreign Key Constraints for Table B 
    -- 
    ALTER TABLE B ADD (CONSTRAINT PK_B PRIMARY KEY (KEY_A, KEY_C));
    
    Insert into B (KEY_A, KEY_C) Values ('1', '11');
    Insert into B (KEY_A, KEY_C) Values ('1', '16');
    Insert into B (KEY_A, KEY_C) Values ('1', '17');
    Insert into B (KEY_A, KEY_C) Values ('2', '12');
    Insert into B (KEY_A, KEY_C) Values ('2', '13');
    Insert into B (KEY_A, KEY_C) Values ('3', '13');
    Insert into B (KEY_A, KEY_C) Values ('4', '14');
    Insert into B (KEY_A, KEY_C) Values ('4', '15');
    Insert into B (KEY_A, KEY_C) Values ('5', '15');
    
    COMMIT;
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    
    ALTER TABLE C
      DROP PRIMARY KEY CASCADE;
      DROP TABLE C CASCADE CONSTRAINTS;
    
    --
    -- C (Table) 
    --
    -- Row count:5
    CREATE TABLE C (
      KEY_C VARCHAR2(11 BYTE) NOT NULL,
      TEXT VARCHAR2(200 BYTE)
      );
    
    --
    -- PK_C (Index) 
    --
    -- Dependencies: 
    -- C (Table)
    --
    CREATE UNIQUE INDEX PK_C ON C(KEY_C);
    
    -- 
    -- Non Foreign Key Constraints for Table C 
    -- 
    ALTER TABLE C ADD (CONSTRAINT PK_C PRIMARY KEY (KEY_C));
    
    Insert into C (KEY_C, TEXT) Values ('11', '11');
    Insert into C (KEY_C, TEXT) Values ('12', '12');
    Insert into C (KEY_C, TEXT) Values ('13', '13');
    Insert into C (KEY_C, TEXT) Values ('14', NULL);
    Insert into C (KEY_C, TEXT) Values ('15', NULL);
    Insert into C (KEY_C, TEXT) Values ('16', 'nonsens');
    Insert into C (KEY_C, TEXT) Values ('17', NULL);
    
    COMMIT;
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    --
    -- TR_B (Trigger) 
    --
    -- Dependencies: 
    -- B (Table)
    --
    CREATE OR REPLACE TRIGGER TR_B
      BEFORE DELETE OR INSERT OR UPDATE ON B 
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
    DECLARE
      key_a varchar2(11);
      key_c varchar2(11);
      v_typ varchar2(10); 
    BEGIN
      select aa.TYP
      into   v_typ
      from   a aa 
      where aa.key_a = nvl(:new.key_a,:old.key_a); 
    
      if v_typ = 'n' then
        BEGIN
          IF INSERTING then
            key_a := :new.key_a;
          else
            key_a := :old.key_a;
          end if;
          --RAISE_APPLICATION_ERROR(-20088,'bla v_typ: '''|| v_typ || ''' old.key_a: ' || :old.key_a || ' new.key_a: ' || :new.key_a );
        END;
    
      /*
      * Wenn ein Datensatz verändert wird,
      * mut_dat und mut_id abfüllen.
      *
      */
      else
        RAISE_APPLICATION_ERROR(-20002,'Daten von Sitzgesellschaften oder Stiftungen oder deren Verbindungen dürfen seit 07.01.13 nicht mehr verändert werden ==> Tabletrigger num_obj_verb'); 
      end if;
    END TR_B;
    /
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    --
    -- TR_C (Trigger) 
    --
    -- Dependencies: 
    -- C (Table)
    --
    CREATE OR REPLACE TRIGGER TR_C
      BEFORE DELETE OR INSERT OR UPDATE ON C 
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
    DECLARE
      v_typ varchar2(20) := '';
      v_obj_key varchar2(11) := 'gugus';
      v_ph_key varchar2(11) := '';
      ph_key varchar2(11) := 12;
      v_obj_keys varchar2(4000) := ',';
    
      counter_n NUMBER(3) := 0;
      counter_n_tmp NUMBER(3) := 0;
      counter_s NUMBER(3) := 0;
    BEGIN
      IF UPDATING or DELETING then
        v_ph_key := :old.key_c;
      elsif INSERTING then
        v_ph_key := :new.key_c;
      end if;
    
      select count(*) 
      into   counter_n
      from   a aa
      where  aa.key_a in (select distinct(bb.key_a) 
                          from   b bb 
                          where  bb.key_c = v_ph_key
                         )
      and    aa.typ = 'n'; 
    
      select count(*)
      into   counter_s
      from   a aa
      where  aa.key_a in (select distinct(bb.key_a) 
                          from   b bb 
                          where  bb.key_c = v_ph_key
                         )
      and    aa.typ = 's';
    
      if UPDATING or DELETING then
        RAISE_APPLICATION_ERROR(-20077,'Remark: counter_n: '|| counter_n || ' counter_s: '|| counter_s 
          || ' v_ph_key: ' || v_ph_key || ' old.key_c: ' || :old.key_c || ' new.key_c: ' || :new.key_c);
        null;
      end if;
    
      if UPDATING or DELETING then
        if (counter_n > 0 and counter_s = 0) then
          null;
        else
          RAISE_APPLICATION_ERROR(-20002,'Daten von s dürfen nicht mehr verändert werden ==> Tabletrigger tr_c'); 
        end if;
      elsif INSERTING then 
        -- RAISE_APPLICATION_ERROR(-20001,'Sie sind nicht berechtigt, Daten zu verändern ==> Tabletrigger ! User: ' || User);
        null;
      end if; 
    END;
    /
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    DROP VIEW B_VW;
    
    --
    -- B_VW (View) 
    --
    -- Dependencies: 
    -- B (Table)
    --
    CREATE OR REPLACE FORCE VIEW b_vw (key_a, key_c) AS
      SELECT key_a, key_c
      FROM   b;
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    DROP TRIGGER B_TR_VW;
    
    --
    -- B_TR_VW (Trigger) 
    --
    -- Dependencies: 
    -- B_VW (View)
    --
    CREATE OR REPLACE TRIGGER b_tr_vw
      INSTEAD OF DELETE ON B_VW 
      REFERENCING NEW AS New OLD AS Old
      FOR EACH ROW
    DECLARE
      v_txt varchar2(200);
    begin
      select text 
      into   v_txt
      from   c
      where  key_c = :OLD.key_c;
    
      if v_txt is null then 
        DELETE from b
        WHERE KEY_a = :OLD.KEY_a
        AND KEY_c = :OLD.KEY_c;
    
        --RAISE_APPLICATION_ERROR(-20002,' OBJ_KEY = ' || :OLD.OBJ_KEY || ' PHANTOM_KEY = ' || :OLD.PHANTOM_KEY || ' VERB_TYP = '|| :OLD.VERB_TYP);
    
        DELETE from c WHERE KEY_c = :OLD.KEY_c;
      else
        RAISE_APPLICATION_ERROR(-20002,'Der Datensatz kann nicht entfernt werden, weil er wichtige Daten enthält');
      end if; 
    END b_tr_vw;
    /
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    DROP VIEW C_VW;
    
    --
    -- C_VW (View) 
    --
    -- Dependencies: 
    -- C (Table)
    --
    CREATE OR REPLACE FORCE VIEW c_vw (key_c, text) AS
      SELECT key_c, text
      FROM c;
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    DROP TRIGGER C_VW_TR;
    
    --
    -- C_VW_TR (Trigger) 
    --
    -- Dependencies: 
    -- C_VW (View)
    --
    CREATE OR REPLACE TRIGGER c_vw_tr
      INSTEAD OF DELETE ON C_VW 
      REFERENCING NEW AS New OLD AS Old
      FOR EACH ROW
    DECLARE
      v_txt VARCHAR2(200);
    BEGIN
      v_txt := :old.text; 
      if v_txt is null then 
        DELETE from C WHERE KEY_c = :OLD.KEY_c;
      else
        RAISE_APPLICATION_ERROR(-20002,'Der Datensatz kann nicht entfernt werden, weil er wichtige Daten enthält');
      end if; 
    END C_VW_TR;
    /
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    
    -- 
    -- Foreign Key Constraints for Table B 
    -- 
    ALTER TABLE B ADD (CONSTRAINT CON_B FOREIGN KEY (KEY_C) REFERENCES C (KEY_C));
    
    --==============================================
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --==============================================
    how it should work !
    SET ECHO OFF
    SET SERVEROUTPUT ON SIZE 1000000
    
    DECLARE
      counter_n NUMBER := 0;
      l_key_c   varchar2(11);
    BEGIN
      l_key_c := '17';
    
      select count(*) 
      into   counter_n
      from   a aa
      where  aa.key_a in (select bb.key_a 
                          from   b bb 
                          where  bb.key_c = l_key_c -- 17
                         )
      and    aa.typ = 'n'; 
    
      dbms_output.put_line('');
      dbms_output.put_line(' counter_n = ' || counter_n);
      dbms_output.put_line('');
    end;
    /
  • 8. Re: get information from inside a trigger from other tables of the same schema
    kendenny Expert
    Currently Being Moderated
    Could the problem be that you're creating this trigger using a user id other than PSE_BKB and that user has access to the tables granted thru a role rather than directly?
  • 9. Re: get information from inside a trigger from other tables of the same schema
    BluShadow Guru Moderator
    Currently Being Moderated
    kendenny wrote:
    Could the problem be that you're creating this trigger using a user id other than PSE_BKB and that user has access to the tables granted thru a role rather than directly?
    I think you're probably best trying to follow his full example code (the lastest code provided) rather than guess at the original partial stuff.

    I just don't have time myself to be looking at that.
  • 10. Re: get information from inside a trigger from other tables of the same schema
    John Spencer Oracle ACE
    Currently Being Moderated
    The reason why you are getting count_n = 0 in tr_c is that there are no rows in B with key_c = 17 by the time you get to tr_c. The delete from B has already happened and since you are in a single transaction, the query in tr_c:
    select count(*)
      into   counter_n
      from   a aa
      where  aa.key_a in (select distinct(bb.key_a)
                          from   b bb
                          where  bb.key_c = v_ph_key
                         )
      and    aa.typ = 'n';
    "knows" that the row has been deleted so gets a count of 0.

    I added a set of dbms_output statements to show the order in which the triggers fire, and this is what I get:
    SQL> delete from b_vw where key_a = 1 and key_c = 17 ;
    in trigger b_tr_vw
    In tr_b
    in tr_c
    delete from b_vw where key_a = 1 and key_c = 17
                *
    ERROR at line 1:
    ORA-20077: Remark: counter_n: 0 counter_s: 0 v_ph_key: 17 old.key_c: 17
    new.key_c:
    ORA-06512: at "OPS$ORACLE.TR_C", line 38
    ORA-04088: error during execution of trigger 'OPS$ORACLE.TR_C'
    ORA-06512: at "OPS$ORACLE.B_TR_VW", line 17
    ORA-04088: error during execution of trigger 'OPS$ORACLE.B_TR_VW'
    So, trigger b_tr_vw fires first and issues a delete from B. Then trigger tr_b fires as part of that delete statement and does the check against A, which apparently passes so no error is raised and the delete from B succeeds. Now tr_b_vw gets control back and issues the delete from C. Since the single row in B that has key_c = 17 has already been deleted the check for counter_n > 0 and counter_s = 0 fails and you raise the -20002 error. Note that I am assuming that the -20077 error you raise is just to see the values and is not intended to be part of your real code.

    Since I really have no idea what you are trying to accomplish here I cannot offer much advice except you might want to try moving the two validation queries from tr_c into the tr_b_vw trigger before you issue the delete from b and put the counter_n > 0 and counter_s = 0 check into tr_b_vw to decide whether or not you want to do the delete.

    John
  • 11. Re: get information from inside a trigger from other tables of the same schema
    984686 Newbie
    Currently Being Moderated
    Hi John

    you have got it. I didn't see this behaviour but now its clear.

    I can delete the row if both counter's are zero.

    For your appreciation: the objects in table a can have 1:m links to table c via the table b which holds both PK - from table a key_a and from table c key_c - in this case table b is a linking table. A delete on table c enforces to delete the row in table b because of the foreign constraint on table b to table c with key_c.

    The objects on table a have a typ (type ! in the example s or n).

    Since we have migratet the active objects of type s to antoher system and left the inactive data on the old system, poeple are no more allowed to change any data on these records from type s and all records that are linked to them. All data on table a from type s and all linked data to this obejcts on table b and table c are now frozen.

    Thats the reason i have to check against s and n. The second is that generally deleting is not allowed except all mandatory fields on table c (in my example column text) are null.

    May be your asking why there are at all records with empty mandatory fields: mandatory or not is defined on the gui not the database. Sometimes the users make a new link by mistake and as long as they do not touch one of the mandatory fields they do not have to fill in them. In these cases there is left a "empty" record which the users are allowed to delete.

    I hope my english is not to creepy and you could understand my explanations.

    Thank you very much for your assistance.

    Kind regards, Konrad alias user832075

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points