Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Oracle Delete Trigger - Constrain Delete to 1 Row

Oracle Delete Trigger - Constrain Delete to 1 Row

From: Bill Bell <billb_at_deletethis.ti.com>
Date: Mon, 11 Apr 2005 11:23:51 -0500
Message-ID: <d3e8ao$q8h$1@home.itg.ti.com>


I want to create a trigger to limit the delete to 1 row per delete. This is prevent users from accidentally removing more records than they want. The schema uses cascase deletes. I tried some thing like this.

Any help is appreciated

AP_ARCHIVE_VIEW_ALL is a view into the table.

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   cnt PLS_INTEGER;
   multiRow EXCEPTION;
BEGIN
   cnt := 0;
   SELECT COUNT (*)

     INTO cnt
     FROM AP_ARCHIVE_VIEW_ALL;

   IF cnt > 1
   THEN
     RAISE multiRow;
   END IF;
EXCEPTION
   WHEN multiRow
   THEN

     RAISE_APPLICATION_ERROR (-20002,
                              'Can only delete 1 archive record at a time'
                             );

END;
-- 
Regards,        Tel# (214) 480-1433  Fax# (214) 480-2356
   Bill Bell     Email = billb_at_ti.com
Received on Mon Apr 11 2005 - 11:23:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US