This forum is currently locked. You can't register or post questions at this time. (read more)

Delete Trigger Problem (Debug/No Debug)

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

bud
Member
Posts: 4
Joined: Wed Jun 04, 2014 8:08 pm

Delete Trigger Problem (Debug/No Debug)

Postby bud » Wed Jun 04, 2014 8:17 pm

Hi,

I have a problem with triggers I can't understand. The triggers do not react the same if I am debugging it or just fire it.

I have a table with some work descriptions (WORK). They can be client specific but some clients have the same workno + descriptions.
The clients with the same text are defined in the table mastercopy client.

select * from v$version:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

I am Using:
Allroundautomations PL/SQL Developer Version 9.0.6.1665

----------------------------------------------------------------------
Setup:
----------------------------------------------------------------------

Code: Select all

create table WORK
(
  client             NUMBER not null,
  workno             NUMBER not null,
  text               VARCHAR2(60 CHAR) not null,
  usr                VARCHar2(30));
 
  alter table work
  add constraint PK_WORK primary key (client, WORKno);

Code: Select all

insert into WORK (CLIENT, WORKNO, TEXT, USR)
values (5, 0, 'freetext 123', 'USR1');

insert into WORK (CLIENT, WORKNO, TEXT, USR)
values (22, 0, 'freetext 33', 'USR 55');


Code: Select all

CREATE OR REPLACE PACKAGE WORK_DATA IS
  TABLEDATA WORK%ROWTYPE;
END;

Code: Select all

 create table MASTERCOPYCLIENT
(
  master_client   NUMBER not null,
  referenz_client NUMBER not null
);



Code: Select all

insert into MASTERCOPYCLIENT (MASTER_CLIENT, REFERENZ_CLIENT)
values (10, 11);

insert into MASTERCOPYCLIENT (MASTER_CLIENT, REFERENZ_CLIENT)
values (10, 12);

insert into MASTERCOPYCLIENT (MASTER_CLIENT, REFERENZ_CLIENT)
values (10, 15);

insert into MASTERCOPYCLIENT (MASTER_CLIENT, REFERENZ_CLIENT)
values (10, 10);

--INSERT TRIGGER

Code: Select all

CREATE OR REPLACE TRIGGER WORK_INSERT
BEFORE INSERT  ON WORK FOR EACH ROW
when ( NEW.USR != 'TRIGGER' )
DECLARE
  CURSOR SEL_REF_client IS
    SELECT REFERENZ_client
      FROM MASTERCOPYclient
     WHERE MASTER_client =
           (select master_client
              FROM MASTERCOPYclient
             where REFERENZ_client = :NEW.client)
       and referenz_client != :NEW.client;
  MASTERCOPY SEL_REF_client%ROWTYPE;
BEGIN
  IF INSERTING THEN
    IF NOT SEL_REF_client%ISOPEN THEN
      OPEN SEL_REF_client;
    END IF;
    LOOP
      FETCH SEL_REF_client
        INTO MASTERCOPY;
      EXIT WHEN SEL_REF_client%NOTFOUND;
      INSERT INTO WORK
        (client, WORKno, text, usr)
      VALUES
        (mastercopy.referenz_client, :new.workno, :new.text, 'TRIGGER');
    END LOOP;
  END IF;

END;



Code: Select all

CREATE OR REPLACE TRIGGER WORK_DELUPD
BEFORE UPDATE OR DELETE ON WORK FOR EACH ROW
BEGIN
WORK_DATA.TABLEDATA.CLIENT := :OLD.CLIENT;
WORK_DATA.TABLEDATA.WORKNO := :OLD.WORKNO;
IF UPDATING THEN
WORK_DATA.TABLEDATA.TEXT := :NEW.TEXT;
WORK_DATA.TABLEDATA.USR := :NEW.USR;
END IF;
END;

Code: Select all

CREATE OR REPLACE TRIGGER WORK_MODIFY
AFTER UPDATE OR DELETE ON WORK
DECLARE
  MASTERCLIENT NUMBER;
BEGIN
  IF WORK_DATA.TABLEDATA.USR != 'TRIGGER' OR
     WORK_DATA.TABLEDATA.USR IS NULL THEN
    MASTERCLIENT               := WORK_DATA.TABLEDATA.CLIENT;
    WORK_DATA.TABLEDATA.CLIENT := 0;
    IF UPDATING THEN
      UPDATE WORK
         SET TEXT = WORK_DATA.TABLEDATA.TEXT, USR = 'TRIGGER'
       WHERE WORKNO = WORK_DATA.TABLEDATA.WORKNO
         AND CLIENT IN (SELECT REFERENZ_CLIENT
                          FROM MASTERCOPYCLIENT
                         WHERE MASTER_CLIENT =
                               (select master_CLIENT
                                  FROM MASTERCOPYCLIENT
                                 where REFERENZ_CLIENT = MASTERCLIENT)
                           and referenz_CLIENT != MASTERCLIENT);
    ELSIF DELETING THEN
      WORK_DATA.TABLEDATA.USR := 'TRIGGER';
      DELETE FROM WORK
     
       WHERE WORKNO = WORK_DATA.TABLEDATA.WORKNO
         AND CLIENT IN (SELECT REFERENZ_CLIENT
                          FROM MASTERCOPYCLIENT
                         WHERE MASTER_CLIENT =
                               (select master_CLIENT
                                  FROM MASTERCOPYCLIENT
                                 where REFERENZ_CLIENT = MASTERCLIENT)
                           and referenz_CLIENT != MASTERCLIENT);
    END IF;
  END IF;
END;

----------------------------------------------------------------------
End Setup
----------------------------------------------------------------------

For each we need this Test Setup
I insert one record. This is triggered so that 4 records in total for workno 5.

Code: Select all

insert into WORK (CLIENT, WORKNO, TEXT, USR)
values (12, 5, 'text should be on trigger clients', 'USR 22');


CLIENT WORKNO TEXT USR
1 15 5 text should be on trigger clients TRIGGER
2 10 5 text should be on trigger clients TRIGGER
3 12 5 text should be on trigger clients USR 22
4 11 5 text should be on trigger clients TRIGGER
5 5 0 freetext 123 USR1
6 22 0 freetext 33 USR 55

TEST 1:
--If I delete now on of the records in a test window and go by the debugger to all steps the DELETE is triggered correctly.

Code: Select all

declare
begin
delete WORK where client = 15 and workno = 5;
end;


CLIENT WORKNO TEXT USR
1 5 0 freetext 123 USR1
2 22 0 freetext 33 USR 55


TEST 2:
--If I delete without debugging then only this specific record is deleted.

Code: Select all

delete WORK where client = 15 and workno = 5;


CLIENT WORKNO TEXT USR
1 11 5 text should be on trigger clients TRIGGER
2 15 5 text should be on trigger clients TRIGGER
3 12 5 text should be on trigger clients USR 22
4 5 0 freetext 123 USR1
5 22 0 freetext 33 USR 55

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Delete Trigger Problem (Debug/No Debug)

Postby Tim... » Wed Jun 04, 2014 8:43 pm

Hi.

I am not in a position to test this at the moment, but my first thought would be are these two operation actually running in the same way? When you run with the debugger, is it being run in a different session?

Your code doesn't include any commits (maybe you are using auto-commit), so if anything is running in a different session, it's view of the data will be radically different.

When I'm testing any "unusual" activity, I usually revert to using SQL*Plus, since that doesn't have any fancy functionality that could screw me up.

So I would suggest you test it in SQL*Plus and consider how the transactions will be processed in reality.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Delete Trigger Problem (Debug/No Debug)

Postby Tim... » Thu Jun 05, 2014 9:22 am

Hi.

Just had a quick look today.

I think you need to put some trace messages into your trigger to determine what code is actually firing and what values are being used when they are firing.

I do not believe this is anything to do with if it is run in debug mode or not. It is if it is run first or second. Look at this, running from SQL*Plus, so it is nothing to do with debug.

Code: Select all

test@db11g> select * from work;

    CLIENT     WORKNO TEXT                                                         USR
---------- ---------- ------------------------------------------------------------ ------------------------------
         5          0 freetext 123                                                 USR1
        22          0 freetext 33                                                  USR 55

2 rows selected.

test@db11g> insert into WORK (CLIENT, WORKNO, TEXT, USR)
  2  values (12, 5, 'text should be on trigger clients', 'USR 22');

1 row created.

test@db11g> select * from work;

    CLIENT     WORKNO TEXT                                                         USR
---------- ---------- ------------------------------------------------------------ ------------------------------
         5          0 freetext 123                                                 USR1
        22          0 freetext 33                                                  USR 55
        11          5 text should be on trigger clients                            TRIGGER
        15          5 text should be on trigger clients                            TRIGGER
        10          5 text should be on trigger clients                            TRIGGER
        12          5 text should be on trigger clients                            USR 22

6 rows selected.

test@db11g> delete WORK where client = 15 and workno = 5;

1 row deleted.

test@db11g> select * from work;

    CLIENT     WORKNO TEXT                                                         USR
---------- ---------- ------------------------------------------------------------ ------------------------------
         5          0 freetext 123                                                 USR1
        22          0 freetext 33                                                  USR 55

2 rows selected.

test@db11g> insert into WORK (CLIENT, WORKNO, TEXT, USR)
  2  values (12, 5, 'text should be on trigger clients', 'USR 22');

1 row created.

test@db11g> select * from work;

    CLIENT     WORKNO TEXT                                                         USR
---------- ---------- ------------------------------------------------------------ ------------------------------
         5          0 freetext 123                                                 USR1
        22          0 freetext 33                                                  USR 55
        11          5 text should be on trigger clients                            TRIGGER
        15          5 text should be on trigger clients                            TRIGGER
        10          5 text should be on trigger clients                            TRIGGER
        12          5 text should be on trigger clients                            USR 22

6 rows selected.

test@db11g>
test@db11g> delete WORK where client = 15 and workno = 5;

1 row deleted.

test@db11g> select * from work;

    CLIENT     WORKNO TEXT                                                         USR
---------- ---------- ------------------------------------------------------------ ------------------------------
         5          0 freetext 123                                                 USR1
        22          0 freetext 33                                                  USR 55
        11          5 text should be on trigger clients                            TRIGGER
        10          5 text should be on trigger clients                            TRIGGER
        12          5 text should be on trigger clients                            USR 22

5 rows selected.

test@db11g>


My guess would be you are not handling your package variables as expected and they are screwing up your second run. Putting some trace messages into the triggers will highlight the issue.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

bud
Member
Posts: 4
Joined: Wed Jun 04, 2014 8:08 pm

Re: Delete Trigger Problem (Debug/No Debug)

Postby bud » Thu Jun 05, 2014 2:01 pm

Hi Tim,

thanks for the repley. I will enhance the Triggers with some trace points.

I will come back on this.

Cheers
Bud

bud
Member
Posts: 4
Joined: Wed Jun 04, 2014 8:08 pm

Re: Delete Trigger Problem (Debug/No Debug)

Postby bud » Mon Jun 16, 2014 3:40 pm

Hi Tim,

I inserted a logging into the triggers and found out where my problem was.

The point I missed was that the help %ROWTYPE PACKAGE is session based. After a commit the array is still filled.

Code: Select all

CREATE OR REPLACE PACKAGE WORK_DATA IS
  TABLEDATA WORK%ROWTYPE;
END;


This means the first time you run the test everything is fine. The second time you run it in the same window/session the ROWTYPE "WORK_DATA.TABLEDATA.USR = 'TRIGGER' ".

For this reason I modified the AFTER STATEMENT TRIGGER:

Code: Select all

CREATE OR REPLACE TRIGGER WORK_MODIFY
AFTER UPDATE OR DELETE ON WORK
DECLARE
  MASTERCLIENT NUMBER;
BEGIN
  IF WORK_DATA.TABLEDATA.USR != 'TRIGGER' OR
     WORK_DATA.TABLEDATA.USR IS NULL THEN
    MASTERCLIENT               := WORK_DATA.TABLEDATA.CLIENT;
    WORK_DATA.TABLEDATA.CLIENT := 0;
    IF UPDATING THEN
      UPDATE WORK
         SET TEXT = WORK_DATA.TABLEDATA.TEXT, USR = 'TRIGGER'
       WHERE WORKNO = WORK_DATA.TABLEDATA.WORKNO
         AND CLIENT IN (SELECT REFERENZ_CLIENT
                          FROM MASTERCOPYCLIENT
                         WHERE MASTER_CLIENT =
                               (select master_CLIENT
                                  FROM MASTERCOPYCLIENT
                                 where REFERENZ_CLIENT = MASTERCLIENT)
                           and referenz_CLIENT != MASTERCLIENT);
    ELSIF DELETING THEN
      WORK_DATA.TABLEDATA.USR := 'TRIGGER';
      DELETE FROM WORK
     
       WHERE WORKNO = WORK_DATA.TABLEDATA.WORKNO
         AND CLIENT IN (SELECT REFERENZ_CLIENT
                          FROM MASTERCOPYCLIENT
                         WHERE MASTER_CLIENT =
                               (select master_CLIENT
                                  FROM MASTERCOPYCLIENT
                                 where REFERENZ_CLIENT = MASTERCLIENT)
                           and referenz_CLIENT != MASTERCLIENT);
      work_data.tabledata.usr := NULL;
    END IF;
 
  END IF;

END;


Thank you for the tip.

Cheers
Bud

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Delete Trigger Problem (Debug/No Debug)

Postby Tim... » Mon Jun 16, 2014 4:24 pm

Hi.

One issue though...

If the statement results in an exception, the after-statement trigger does not fire, so nothing will clean up the collection. You should also clean it out in the before statement trigger, just to make sure. Alternatively, use a compound trigger.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

bud
Member
Posts: 4
Joined: Wed Jun 04, 2014 8:08 pm

Re: Delete Trigger Problem (Debug/No Debug)

Postby bud » Tue Jun 17, 2014 2:47 pm

Hi Tim,

thank you for this hint. Absolutly clear. I will clean also in a BEFORE-Statement-Trigger. Unfortunately I am on an Oracle 10g DB. So the "compound Trigger" is not available for me.

Cheers
Bud

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Delete Trigger Problem (Debug/No Debug)

Postby Tim... » Tue Jun 17, 2014 2:58 pm

Hi.

OK. No worries. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron