Before each row - delete trigger issue

user9216096

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.

KayK

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

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

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

user9216096

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

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

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

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

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

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

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

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