ตอนที่ 17 : การสร้าง Trigger Events บน Oracle Database (Oracle : Trigger)

CREATE OR REPLACE TRIGGER trigger_name
  { BEFORE | AFTER } { INSERT | UPDATE | DELETE }  ON object_name
  FOR EACH ROW
BEGIN
   --  Insert Case
  IF INSERTING THEN
  //  Statement
  END IF;
    
  --  Update Case
  IF UPDATING THEN
  //  Statement
  END IF;
    
  --  Delete Case
  IF DELETING THEN
  //  Statement
  END IF;

  // :NEW.Column
  // :OLD.Column
  //  Statement
END;

CREATE OR REPLACE TRIGGER DELETE_AUDIT 
  AFTER DELETE ON CUSTOMER 
  FOR EACH ROW
BEGIN
  DECLARE
  sCustomerID VARCHAR(4);
  BEGIN
    sCustomerID := :OLD.CUSTOMER_ID;
    DELETE FROM AUDIT_LOG WHERE CUSTOMER_ID = sCustomerID;
  END;
END;

CREATE OR REPLACE TRIGGER SUM_UPDATE_USED 
  AFTER DELETE OR INSERT OR UPDATE ON AUDIT_LOG 
   FOR EACH ROW
BEGIN
  DECLARE
  sCustomerID VARCHAR(4);
  dSumUsed  DECIMAL(18,2);
  BEGIN

   --  CUSTOMER_ID Insert Case
    IF INSERTING THEN
      sCustomerID := :NEW.CUSTOMER_ID;
    END IF;
    
    --  CUSTOMER_ID Update Case
    IF UPDATING THEN
      sCustomerID := :NEW.CUSTOMER_ID;
    END IF;
    
    --  CUSTOMER_ID Delete Case
    IF DELETING THEN
      sCustomerID := :OLD.CUSTOMER_ID;
    END IF;
    
    -- Select SUM USED
    SELECT SUM(USED) INTO dSumUsed FROM AUDIT_LOG WHERE CUSTOMER_ID = sCustomerID;
    
    -- Update CUSTOMER
    UPDATE CUSTOMER SET USED = dSumUsed WHERE CUSTOMER_ID = sCustomerID;
    
  END;

END;