Home » SQL & PL/SQL » SQL & PL/SQL » after delete trigger
after delete trigger [message #213029] Tue, 09 January 2007 03:24 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Good noon All

I have created a trigger (given below) that deletes record from stock if a record is deleted from sale.

What I need is to subtract the amount received from receipts table if a record is deleted from sale.

How can it be done? please help


EX > desc sale
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 SALE_ID                                                        VARCHAR2(10)
 CUSTOMER_ID                                                    VARCHAR2(10)
 CARD_ID                                                        VARCHAR2(50)
 QTY_SOLD                                                       NUMBER(4)
 DISCNT_GIVEN                                                   NUMBER(5,2)
 DISCNT_RECD                                                    NUMBER(5,2)
 AMOUNT                                                         NUMBER(10,2)
 SALE_DATE                                                      DATE

EX > desc stock
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 CARD_ID                                               NOT NULL VARCHAR2(50)
 QTY                                                            NUMBER(4)
 DISCNT_RECD                                                    NUMBER(4,2)

EX > desc receipts
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 CUSTOMER_ID                                                    VARCHAR2(10)
 AMOUNT_RECD                                                    NUMBER(10,2)
 DATE_RECD                                                      DATE

EX > 
EX > 
EX > ---------------After delete on sale  to update stock
EX > 
EX >     CREATE OR REPLACE TRIGGER sale_DEL_TRIG AFTER DELETE
  2          ON SALE
  3           REFERENCING NEW AS NEW OLD AS OLD
  4          FOR EACH ROW
  5        DECLARE
  6        V_COUNT NUMBER;
  7       BEGIN
  8        SELECT COUNT(*) INTO V_COUNT FROM STOCK
  9        WHERE CARD_ID= :OLD.CARD_ID;
 10        IF V_COUNT>0 THEN
 11        UPDATE STOCK SET QTY=NVL(QTY,0)+:OLD.QTY_SOLD
 12        WHERE CARD_ID= :OLD.CARD_ID;
 13         
 14   END IF;
 15     END;
 16  /

Trigger created.

EX > 
EX > 



rzkhan
Re: after delete trigger [message #213038 is a reply to message #213029] Tue, 09 January 2007 03:40 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Will sonething like this in the trigger not work:
(untested)
UPDATE receipts
SET    amount_recd = amount_recd - :old.amount
WHERE  customer_id = :old.customer_id;
Previous Topic: put a result into an array
Next Topic: Connect by help
Goto Forum:
  


Current Time: Wed Dec 07 08:25:37 CST 2016

Total time taken to generate the page: 0.07690 seconds