Skip to Main Content
  • Questions

  • Delete trigger for record that does not exist

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 1000+ 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 Chris 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)

We're not taking comments currently, so please try again later if you want to add a comment.

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library