title: Oracle SQL Instead of Delete Trigger
body: <p>I want to create an 'instead of delete' trigger to restrict the deletion of a row when the value entered exceeds a certain amount.</p>
  
  <p>I have an Invoice table with 2 columns: Invoice(InvoiceID Number, Total Number)</p>
  
  <p>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.</p>
  
  <p>So far I have a rough sketch of what I want, but I'm not sure if the exact syntax is correct.</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER IOFD_INVOICE
  INSTEAD OF DELETE ON INVOICE
  BEGIN 
    DECLARE
      TTL INTEGER;
  
    SELECT TOTAL = TTL
     FROM INVOICE
  
    IF TTL &gt;= 100
     BEGIN
      RAISERROR('Record cannot be deleted.')
      ROLLBACK
     END
  
    ELSE
     BEGIN
      DELETE FROM INVOICE
    END
  
  END;
  </code></pre>
  
  <p>I thought instead of triggers could be used on tables but I get the following error Message:</p>
  
  <pre><code> 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.
  </code></pre>