Oracle SQL Instead of Delete Trigger

Question

I want to create an 'instead of delete' trigger to restrict the deletion of a row when the value entered exceeds a certain amount.

I have an Invoice table with 2 columns: Invoice(InvoiceID Number, Total Number)

I want the trigger to fire if I try to delete a row that has a stored value in Total >= 100 and prevent the deletion.

So far I have a rough sketch of what I want, but I'm not sure if the exact syntax is correct.

CREATE OR REPLACE TRIGGER IOFD_INVOICE
INSTEAD OF DELETE ON INVOICE
BEGIN 
  DECLARE
    TTL INTEGER;

  SELECT TOTAL = TTL
   FROM INVOICE

  IF TTL >= 100
   BEGIN
    RAISERROR('Record cannot be deleted.')
    ROLLBACK
   END

  ELSE
   BEGIN
    DELETE FROM INVOICE
  END

END;

I thought instead of triggers could be used on tables but I get the following error Message:

 Error report -
 ORA-25002: cannot create INSTEAD OF triggers on tables
 25002. 00000 -  "cannot create INSTEAD OF triggers on tables"
 *Cause:    Only BEFORE or AFTER triggers can be created on a table.
 *Action:   Change the trigger type to BEFORE or AFTER.

"but I'm not sure if the exact syntax is correct." what does Mr DB say when you ttry in a dummy DB? — Mitch Wheat
– Mitch Wheat, Commented Nov 28, 2016 at 0:45

Gary Myers · Accepted Answer · 2016-11-28 05:14:46Z

3

INSTEAD OF triggers are only applicable to views.

The logic can be accomplished by a simple

CREATE OR REPLACE TRIGGER BD_INVOICE
BEFORE DELETE ON INVOICE FOR EACH ROW
BEGIN
  IF :OLD.TOTAL >= 100 THEN
    RAISE_APPLICATION_ERROR(-20001,'Record cannot be deleted.');
  END IF;
END;

answered Nov 28, 2016 at 5:14

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Instead of Delete Trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related