This problem is a continuation of the previous 13 problems a

This problem is a continuation of the previous 13 problems already solved.

Write the trigger to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Test the trigger using the following new INVOICE record:

8005, 1001, \'27-Aug-16\',225.40

Name the trigger trg_updatecustbalance.

Solution

CREATE OR REPLACE TRIGGER TRG_UPDATECUSTBALANCE

AFTER INSERT ON INVOICE

FOR EACH ROW

BEGIN

      UPDATE CUSTOMER

SET CUST_BALANCE = CUST_BALANCE + :NEW.INV_AMOUNT

      WHERE CUST_NUM = :NEW.CUST_NUM;

END;

To test the trigger you do the following:

SELECT * FROM CUSTOMER;

INSERT INTO INVOICE VALUES (8005,1001,’27-AUG-16’,225.40);

SELECT * FROM CUSTOMER;

MS SQL SERVER SOLUTION

create trigger trg_update_custbal

on invoice

after insert

as

update customer

set customer.cust_balance= customer.cust_balance+

(select inv_amount from inserted)

where customer.cust_num = (select cust_num from inserted);

TO TEST

select * from CUSTOMER;

select * from INVOICE;

insert into INVOICE values (8005,1001, \'2013-03-01\', 190);

select * from CUSTOMER; select * from INVOICE;