Creating a trigger that deletes a row when an attr

Question

I would like to create a trigger that will delete a row when one of its attributes becomes negative. So far I have this, but it doesn't appear to be valid sql:

CREATE OR REPLACE TRIGGER ZERO_COPIES_TRIGGER
after
update of counter_attribute
on my_table
referencing new as new
for each row when(new.copies < 0)
begin
  delete from my_table where my_table.id = :new.id;
end;

Answer 1

For the sake of completeness: another option is to have a statement trigger which scans the table and then performs the delete, as in:

CREATE OR REPLACE TRIGGER ZERO_COPIES_TRIGGER
      AFTER UPDATE OF COUNTER_ATTRIBUTE ON MY_TABLE
BEGIN
  FOR aROW IN (SELECT ID
                 FROM MY_TABLE
                 WHERE COPIES < 0)
  LOOP
    DELETE FROM MY_TABLE
      WHERE ID = aROW.ID;
  END LOOP;
END ZERO_COPIES_TRIGGER;

Or, if you really want to have some fun, you could use a compound trigger to handle the deletes, without requiring a table scan, while still avoiding the dreaded "MUTATING TABLE" error, as in:

CREATE OR REPLACE TRIGGER COMPOUND_ZERO_COPIES_TRIGGER
  FOR UPDATE OF COUNTER_ATTRIBUTE ON MY_TABLE
COMPOUND TRIGGER
  TYPE NUMBER_TABLE IS TABLE OF NUMBER;
  tblDELETE_IDS  NUMBER_TABLE;

  BEFORE STATEMENT IS
  BEGIN
    tblDELETE_IDS := NUMBER_TABLE();
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.COPIES < 0 THEN
      tblDELETE_IDS.EXTEND;
      tblDELETE_IDS(tblDELETE_IDS.LAST) := :NEW.ID;
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    IF tblDELETE_IDS.COUNT > 0 THEN
      FOR I IN tblDELETE_IDS.FIRST..tblDELETE_IDS.LAST LOOP
        DELETE FROM MY_TABLE
          WHERE ID = tblDELETE_IDS(I);
      END LOOP;
    END IF;
  END AFTER STATEMENT;
END COMPOUND_ZERO_COPIES_TRIGGER;

Share and enjoy.

Answer 2

This is not going to work. You can't perform DML on a table which is being manipulated by a row-level trigger. You will get a "mutating table" error.

To get the result you want, your best bet is to have a flag or indicator column to identify that the record is to be deleted. Then, have a separate job or process or whatever to actually perform the delete.