Home » SQL & PL/SQL » SQL & PL/SQL » Prevent deletion (12c)
Prevent deletion [message #666419] Fri, 03 November 2017 07:49 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Hello experts,

I am trying to write triggers on one transaction table cust_ord , if the data is available for that particular customer order ( cust_ord) in another table which is kind of approval table (CUST_APPROVAL) , then user is not
allowed to modify or delete , for me there are two problems , my delete trigger is not working and second one is there a way to combine both the triggers into one single trigger.

test case


CREATE TABLE CUST_ORD (ORD_ID VARCHAR2(12),ORD_CR_ID VARCHAR2(12), ORD_PRICE NUMBER , ORD_DESC VARCHAR2(30))

INSERT INTO CUST_ORD(ORD_ID,ORD_CR_ID,ORD_PRICE,ORD_DESC) VALUES ('1','101',1234,'PRODUCT')

INSERT INTO CUST_ORD(ORD_ID,ORD_CR_ID,ORD_PRICE,ORD_DESC) VALUES ('2','202',100,'PRODUCT2')

COMMIT

SELECT * FROM CUST_ORD


CREATE TABLE CUST_APPROVAL (CUST_ORD_ID VARCHAR2(12),CUST_APPR_ID VARCHAR2(12))

INSERT INTO CUST_APPROVAL(CUST_ORD_ID,CUST_APPR_ID) VALUES ('2','202')

SELECT * FROM CUST_APPROVAL

COMMIT

UPDATE CUST_ORD SET ORD_PRICE=200 WHERE ORD_ID='1'

DELETE FROM CUST_ORD WHERE ORD_ID='2'



/* Formatted on 03/11/2017 4:31:28 PM (QP5 v5.256.13226.35538) */
/* Formatted on 03/11/2017 4:31:53 PM (QP5 v5.256.13226.35538) */
CREATE OR REPLACE TRIGGER trg_before_appr
   BEFORE UPDATE OF ORD_PRICE, ORD_DESC
   ON CUST_ORD
   FOR EACH ROW

DECLARE
   CURSOR C1
   IS
      SELECT 'X'
        FROM CUST_APPROVAL
       WHERE CUST_ORD_ID = :NEW.ORD_ID;

   M_FND   VARCHAR2 (1);
BEGIN
   OPEN C1;

   FETCH C1 INTO M_FND;

   CLOSE C1;

   IF NVL (M_FND, 'Y') = 'X'
   THEN
      IF    (:NEW.ORD_PRICE <> :OLD.ORD_PRICE)
         OR (:NEW.ORD_DESC <> :OLD.ORD_DESC)
      THEN
         RAISE_APPLICATION_ERROR (
            -20000,
            'You are not allowed to modify cust order after it is approved.');
      END IF;
   END IF;
END;





/* Formatted on 03/11/2017 4:40:57 PM (QP5 v5.256.13226.35538) */
CREATE OR REPLACE TRIGGER DEL_TRG_CUST
   BEFORE DELETE
   ON CUST_ORD
   FOR EACH ROW
DECLARE
   CURSOR C1
   IS
      SELECT 'X'
        FROM CUST_APPROVAL
       WHERE CUST_ORD_ID = :NEW.ORD_ID;

   M_FND   VARCHAR2 (1);
BEGIN
   OPEN C1;

   FETCH C1 INTO M_FND;

   CLOSE C1;

   IF NVL (M_FND, 'Y') = 'X'
   THEN
      raise_application_error (-20000, 'No you cannot delete');
   END IF;
END;
/




Re: Prevent deletion [message #666421 is a reply to message #666419] Fri, 03 November 2017 08:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your DELETE trigger is incorrect. You don't have any :NEW when deleting a row, you have :OLD only.
Re: Prevent deletion [message #666422 is a reply to message #666419] Fri, 03 November 2017 08:09 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
m.abdulhaq wrote on Fri, 03 November 2017 08:49


/* Formatted on 03/11/2017 4:40:57 PM (QP5 v5.256.13226.35538) */
CREATE OR REPLACE TRIGGER DEL_TRG_CUST
BEFORE DELETE
ON CUST_ORD
FOR EACH ROW
DECLARE
CURSOR C1
IS
SELECT 'X'
FROM CUST_APPROVAL
WHERE CUST_ORD_ID = :NEW.ORD_ID;

M_FND VARCHAR2 (1);
BEGIN
OPEN C1;

FETCH C1 INTO M_FND;

CLOSE C1;

IF NVL (M_FND, 'Y') = 'X'
THEN
raise_application_error (-20000, 'No you cannot delete');
END IF;
END;
/
Why are you using a cursor? Just check for the existence, plus what John said.

[Updated on: Fri, 03 November 2017 08:10]

Report message to a moderator

Re: Prevent deletion [message #666423 is a reply to message #666422] Fri, 03 November 2017 08:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
joy_division

Why are you using a cursor?
Looks like yet another imaginative way to avoid NO-DATA-FOUND exception.
Re: Prevent deletion [message #666426 is a reply to message #666421] Fri, 03 November 2017 11:20 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks John.
Re: Prevent deletion [message #666428 is a reply to message #666422] Fri, 03 November 2017 11:21 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i am using cursor because i have lookup into another table.
Re: Prevent deletion [message #666429 is a reply to message #666428] Fri, 03 November 2017 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
m.abdulhaq wrote on Fri, 03 November 2017 09:21
i am using cursor because i have lookup into another table.
SELECT statement can lookup into other table.
Re: Prevent deletion [message #666438 is a reply to message #666429] Fri, 03 November 2017 14:17 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
CREATE OR REPLACE TRIGGER del_trg_cust
   BEFORE DELETE OR UPDATE
   ON cust_ord
   FOR EACH ROW
DECLARE
   cnt   NUMBER;
BEGIN
   SELECT COUNT (*)
     FROM cust_approval
    WHERE cust_ord_id = :old.ord_id;

   IF cnt > 0
   THEN
      IF DELETING
      THEN
         raise_application_error (-20000, 'No you cannot delete');
      ELSE
         raise_application_error (
            -20000,
            'You are not allowed to modify cust order after it is approved.');
      END IF;
   END IF;
END;
/
Re: Prevent deletion [message #666445 is a reply to message #666438] Sat, 04 November 2017 10:39 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Bill B.
Previous Topic: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Next Topic: how can get five days
Goto Forum:
  


Current Time: Fri Mar 29 08:54:46 CDT 2024