CODEWITHSUNDEEP
plsql
Renna
Renna

Reputation: 25

TRIGGER does not fire for deleting, but fire for inserting and updating

This is my first time creating Trigger. I found some similar questions, but I still cannot solve my error from those suggestions so I will ask here. Here are my tables:

Create Table Bill
(
    Bill_Number     Number(6,0)  primary key,                                                  
    Paid_YN         Char(1),             
    Posted_YN       Char(1)             
 );

And this table:

Create Table Bill_Item
(
    Bill_Number         Number(6,0)   References Bill (Bill_Number),                                          
    Menu_Item_Number    Number(5,0),
    Quantity_Sold       Number(3,0),
    Selling_Price       Number(6,2)
);

I need to create a trigger to prevent any insert, update, or delete on Bill_Item table if Paid_YN or/and Posted_YN is 'Y'. The messages must show which event and what reason (paid, posted or both).

And this is my code. Trigger works well with inserting, and updating but does not fire with deleting.

CREATE OR REPLACE TRIGGER TR_NO_POST
BEFORE INSERT OR UPDATE OR DELETE ON Bill_Item
FOR EACH ROW
BEGIN
    SELECT Paid_YN, Posted_YN
    INTO V_Paid_YN, V_Posted_YN
    FROM Bill 
    WHERE Bill_Number = :NEW.Bill_Number;        
    IF inserting THEN            
        IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
                RAISE_APPLICATION_ERROR(-20001, 'Bill has been paid. Cannot add more items!');
        ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N'THEN
                RAISE_APPLICATION_ERROR(-20002, 'Bill has been posted. Cannot add more items!');
        ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
                RAISE_APPLICATION_ERROR(-20003, 'Bill has been paid and posted. Cannot add more items!');
        END IF;                    
    ELSIF updating THEN
        IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
                RAISE_APPLICATION_ERROR(-20011, 'Bill has been paid. Cannot change!');
        ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N'THEN
                RAISE_APPLICATION_ERROR(-20022, 'Bill has been posted. Cannot change!');
        ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
                RAISE_APPLICATION_ERROR(-20033, 'Bill has been paid and posted. Cannot change!');
        END IF;  
    ELSIF deleting THEN
        IF V_Paid_YN = 'Y' AND V_Posted_YN = 'N' THEN
                RAISE_APPLICATION_ERROR(-20111, 'Bill has been paid. Cannot delete!');
        ELSIF V_Posted_YN = 'Y' AND V_Paid_YN = 'N'THEN
                RAISE_APPLICATION_ERROR(-20222, 'Bill has been posted. Cannot delete!');
        ELSIF V_Paid_YN = 'Y' AND V_Posted_YN = 'Y' THEN
                RAISE_APPLICATION_ERROR(-20333, 'Bill has been paid and posted. Cannot delete!');
        END IF;  
    END IF;    
END TR_NO_POST;

Upvotes: 0

Views: 48

Answers (1)

Anuar Aidynbayev
Anuar Aidynbayev

Reputation: 71

You have to select from "Bill table" to check Paid_YN column value. Something like this:

CREATE OR REPLACE TRIGGER TR_NO_POST
BEFORE INSERT OR UPDATE OR DELETE ON Bill_Item
FOR EACH ROW
  paid_res Char(1);
  posted_res Char(1);
BEGIN  
  select max(h.paid_yn), 
         max(h.posted_yn) 
  into paid_res, posted_res   
  from Bill h 
  where h.Bill_Number = :NEW.Bill_Number;

  if paid_res = 'Y' then 
    RAISE_APPLICATION_ERROR(-20001, 'Bill has been paid!');
  elsif posted_res = 'Y' then
    RAISE_APPLICATION_ERROR(-20001, 'Bill has been posted!');
  end if;
END TR_NO_POST;

Upvotes: 1

Related Questions