Prevent deletion [message #666419] |
Fri, 03 November 2017 07:49 |
|
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 #666422 is a reply to message #666419] |
Fri, 03 November 2017 08:09 |
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 #666438 is a reply to message #666429] |
Fri, 03 November 2017 14:17 |
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;
/
|
|
|
|