Forum Stats

  • 3,741,213 Users
  • 2,248,392 Discussions
  • 7,861,674 Comments

Discussions

Before each row - delete trigger issue

user9216096
user9216096 Member Posts: 4
edited Jul 8, 2019 9:06AM in SQL & PL/SQL

We have trigger set up for Delete statement before each row in <tablename>,

Before Each row trigger

begin

if :old:<column_name> = ' ' then

RAISE_APPLICATION_ERROR

(-20001,'Cannot delete <tablename> record with blank <column name> column');

end if;

end;

But, while deleting the records getting below errors, even when we add where condition in delete query as <column_name>  <> '  ' (column not equal to blank)

Error information:

ORA-20001: Cannot delete <tablename> record with blank <column name> column

ORA-06512: at "<triggername>", line 3

ORA-04088: error during execution of trigger <trigger name>

Can you let me know how to resolve this trigger issue. Thanks in advance.

Tagged:
Frank KulashL. Fernigriniuser9216096

Answers

  • KayK
    KayK Member Posts: 1,653 Bronze Crown
    edited Jul 8, 2019 3:25AM

    Hi User92,

    are you sure that your column content a single space ? Or do you want to check an empty column ?

    Perhaps you try it with :old:<column_name> is null or :old:<column_name> is not null.

    regards
    Kay

  • BEDE
    BEDE Oracle Developer Member Posts: 2,266 Gold Trophy
    edited Jul 8, 2019 3:27AM

    I think you should use is not null. '' is the same as null. And nulls are to be treated using nvl or coalesce functions or is not null or is null, depending what you really need to do. In your case I think you should use is not nul.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jul 8, 2019 3:27AM
    user9216096 wrote:We have trigger set up for Delete statement before each row in <tablename>,Before Each row triggerbegin if :old:<column_name> = ' ' then RAISE_APPLICATION_ERROR(-20001,'Cannot delete <column_name> record with blank <column_name> column');end if;end;But, while deleting the records getting below errors, even when we add where condition in delete query as <column_name> <> ' ' (column not equal to blank)Error information:ORA-20004: Cannot delete <tablename> record with blank <column name> columnORA-06512: at "<triggername>", line 3ORA-04088: error during execution of trigger <trigger name>Can you let me know how to resolve this trigger issue. Thanks in advance.

    Please share DDL to create the table and DML to insert the rows and the delete statement you’re running.

    The error stack you shared has a different error number to the error in your code - is that a mistake in how you’re sharing it with us or is there some other code that raises -20004?

    Remember that ‘ ‘ (one space) is not the same as ‘  ‘ (two spaces) and is not the same as blank or ‘’ or null

    Frank Kulash
  • user9216096
    user9216096 Member Posts: 4
    edited Jul 8, 2019 3:51AM

    Hi all,

    I am able to retrieve the data with single space in the select query

    SELECT * FROM temp where tempname=' ';

    create table temp( tempname varchar2(200),desc varchar2(200));

    create table temp2(tempname varchar2(200),desc varchar2(200));

    insert into temp values (' ',' ');

    insert into temp values ('Test1','Test One');

    insert into temp values ('Test2','Test Two');

    insert into temp2 values('Test2','Test Two');

    commit;

    delete from temp where tempname not in (select tempname from temp2) or tempname<>' ';

    commit;

    row level trigger code:

    begin

      if :old.tempname = ' ' then

         RAISE_APPLICATION_ERROR

        (-20001, 'Cannot delete temp record with blank tempname column');

      end if;

    end;

    please find the sample DML and DDL.

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy
    edited Jul 8, 2019 4:29AM

    The problem is OR - you should use AND:

    delete from temp where tempname not in (select tempname from temp2) OR tempname<>' ';

    By the way:

    1. Your code does not pass - DESC is a bad name for the column.

    2. You have not written a complete trigger definition. I suppose it's like this:

    create or replace trigger bdr_temp

      before delete on temp

      for each row

    begin

      if :old.tempname = ' ' then

         RAISE_APPLICATION_ERROR

          (-20001, 'Cannot delete temp record with blank tempname column');

      end if;

    end;

    /

    Regards,

    Zlatko

  • KayK
    KayK Member Posts: 1,653 Bronze Crown
    edited Jul 8, 2019 4:38AM

    So what do you expect ?

    - delete from temp where tempname not in (select tempname from temp2);

         This will delete nothing because you try to delete the row with the blank/space

    - delete from temp where  tempname<>' ';

         And this will delete the rows without the blank/space

    But your combination of both where-clauses can't delete any row because the trigger fires for one of your rows.

    This ends and rollback the complete statement.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,266 Gold Trophy
    edited Jul 8, 2019 4:47AM

    delete from temp where tempname not in (select tempname from temp2) or tempname<>' ';

    Simply makes no sense.

    There will be nothing in temp table which is not found in the temp table, so that: tempname not in (select tempname from temp2) is nonsense.

    Then, there is this wonderful condition or tempname<>' ' which, as I said before should be written where tempname is not null.

    I don't understand the business logic for this and I suspect there might be some application logic issues.

  • user9216096
    user9216096 Member Posts: 4
    edited Jul 8, 2019 5:27AM

    I want to delete the entries in temp table when the tempname is not there in temp2 table, but it not allowing me to delete due to trigger handling the blank. So, I have added the condition tempname<>' ' to delete query. still I am getting the issue.

    My requirement is to remove the not used records from the temp table.

    There is blank row (not a null value)in table, which they created in PROD too for some purpose and created the trigger on top of it.

    I have referred the below link, but the resolutions are not clear

    http://www.dba-oracle.com/t_ora_04088_error_during_execution_of_trigger_string_string.htm

    https://stackoverflow.com/questions/20020529/error-during-trigger-execution-ora-06512-ora-04088

    Can we disable and enable the trigger to delete records. Is there any other way to handle it.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,266 Gold Trophy
    edited Jul 8, 2019 6:13AM

    To avoid the trigger firing in the session in which you want to delete and, at the same time, have the trigger fire in any other session you should set a context before the delete and have the trigger check for that context and not fire if so set.

    See:

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5002.htm

    and

    https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sessio.htm#ARPLS68034

    If you disable the trigger for a while and many other sessions operate on that table, there is a probability that other sessions do what the trigger was intended to prevent them from doing.

    Or, in case you do not have grants on dbms_session, you may use a variable in a package for setting the "context" and refer that in the trigger. That one with a variable in package was once the only means to do such a thing when dbms_session.set_context did not exist.

  • KayK
    KayK Member Posts: 1,653 Bronze Crown
    edited Jul 8, 2019 6:19AM

    Yes, you can disable trigger. But is that what you really want ?

    You want to delete all rows from temp where no entries exists in temp2.

    But the blank/space doesn't exists in temp2, that is the reason your statement tries to delete this row.

    Then the trigger fires and your COMPLETE statement fails for all rows, not only the row with the blank.

    I think you have 2 ways:

    - proceed your deletes row by row with execption handling of -20001 or

    - insert 1 row with blank in temp2

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,131 Gold Trophy
    edited Jul 8, 2019 9:06AM

    Have you tried what I suggested

    delete from temp where tempname not in (select tempname from temp2) AND tempname <> ' ';

    ?

    You don't want to delete these rows

    ... where tempname IN (...) OR tempname = ' '

    ie you want to delete these rows

    ... where NOT (tempname IN (...) OR tempname = ' ')

    which is equivalent to this

    ... where tempname NOT IN (...) AND tempname <> ' '

    Let's remember

    NOT (a OR b) = NOT a AND NOT b

    NOT (a AND b) = NOT a OR NOT b

    Note: Equality sign here is a metalogical operator.

    Regards,

    Zlatko

    L. Fernigriniuser9216096
Sign In or Register to comment.