Skip to Main Content
  • Questions
  • Delete trigger for record that does not exist

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, osieman.

Asked: March 10, 2021 - 6:57 am UTC

Last updated: March 16, 2021 - 11:20 am UTC

Version: 12c

Viewed 100+ times

You Asked

Hello, I want to write a delete trigger.

The requirement is:-
1) There is a delete statement for a single record that does not exist.

Delete from t1 where pk = 'myuser'


2) convert the delete to the uppercase value of the field old.pk

Delete from t1 where pk = 'MYUSER'


The problem is a row level delete will not fire as there is no record of myuser.

and we said...

Row-level triggers only fire for rows affected by the DML statement.

You could create a statement-level trigger, but that doesn't have access to the old values for the row - because there is no row!

If the problem here is that the where clause identifies the wrong rows, the solution is to fix the code so the statement locates the rows you want

Rating

  (3 ratings)

Comments

A reader, March 15, 2021 - 5:28 pm UTC

Thank you for your answer.

I agree exactly with what you said.

The delete code is auto generated so I can not change it.

I solved the problem with a compound trigger which creates a temp table in the row level part and then the delete in the statement-level trigger based on the data obtained in the temp table.

INSTEAD OF TRIGGER?

Dieter, March 15, 2021 - 6:56 pm UTC

If you have a view v_tab_a on the table tab_a and the generated DML is on the view, you could also use an INSTEAD-OF-TRIGGER:

create table tab_a (id varchar2(10));

create view v_tab_a as select lower(id) id from tab_a;

CREATE OR REPLACE TRIGGER del_tab_a
  INSTEAD OF DELETE ON v_tab_a
  FOR EACH ROW
BEGIN
  DELETE FROM tab_a a
   WHERE upper(a.id) = upper(:old.id);
END;


insert into tab_a (id) values ('ABC');
commit;

select * from v_tab_a;

delete from v_tab_a where id = 'abc';




Chris Saxon
March 16, 2021 - 11:20 am UTC

True - it's much better to fix the query though!

If you're looking for case-insensitive queries, from 12.2 it's better to use the collate operator or make the column itself case-insensitive with column-level collations:

https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/linguistic-sorting-and-matching.html#GUID-2AC4EE67-D3C0-4E26-87AD-2DA4584F874A

A reader, March 18, 2021 - 12:35 pm UTC

The instead of trigger looks like a very nice bit of code.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.