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
      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
          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
            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
              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
                ... 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
                  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
                    ... 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
                      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
                        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
                          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
                            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
                              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