Oracle trigger- instead of delete, update the row

Question

How do I write an Oracle trigger, than when a user deletes a certain record, the delete doesnt actually happen, but instead performs an update on those rows and sets the status of the record to 'D'?

I tried:

create or replace
trigger DELFOUR.T4M_ITEM_ONDELETE
before delete on M_ITEM_H
FOR EACH ROW
BEGIN
  UPDATE
    M_ITEM_H
  SET
    ITEM_STAT = 'D'
  WHERE
    CUST_CODE = 'TEST'
    AND ITEM_CODE = 'GDAY'
  ;

  raise_application_error(-20000,'Cannot delete item');
END;

But I am getting mutating table errors. Is this possible?

Answer 1

If you really need a trigger, the more logical approach would be to create a view, create an INSEAD OF DELETE trigger on the view, and to force the applications to issue their deletes against the view rather than against the base table.

CREATE VIEW vw_m_item_h
AS
SELECT *
  FROM m_item_h;

CREATE OR REPLACE TRIGGER t4m_item_ondelete
  INSTEAD OF DELETE ON vw_m_item_h
  FOR EACH ROW
AS
BEGIN
  UPDATE m_item_h
     SET item_stat = 'D'
   WHERE <<primary key>> = :old.<<primary key>>;
END;

Better yet, you would dispense with the trigger, create a delete_item procedure that your application would call rather than issuing a DELETE and that procedure would simply update the row to set the item_stat column rather than deleting the row.

If you really, really, really want a solution that involves a trigger on the table itself, you could

1. Create a package with a member that is a collection of records that map to the data in the m_item_h table
2. Create a before delete statement-level trigger that empties this collection
3. Create a before delete row-level trigger that inserts the :old.<<primary key>> and all the other :old values into the collection
4. Create an after delete statement-level trigger that iterates through the collection, re-inserts the rows into the table, and sets the item_stat column.

This would involve more work than an instead of trigger since you'd have to delete and then re-insert the row and it would involve way more moving pieces so it would be much less elegant. But it would work.