help me with this trigger

[Thejo Gundala]

I am new to databases. So, trying my best to design a inventory management system. I would like to do the following

Database name is "yh_electronic"

Table Name is - orders

attributes are:-

Order_Total DECIMAL (15,2)
Total_Paid DECIMAL (15,2)
Balance_Due DECIMAL (15,2)

I would like to have a Balance_Due column auto filled when order_Total and Total_Paid columns are inserted with new values or when Total_paid value is updated. So, I have tried to create a trigger with the below query:

DELIMITER $$

DROP TRIGGER IF EXISTS yh_electronics.orders_AINS$$

USE yh_electronics$$

CREATE TRIGGER orders_AINS AFTER INSERT ON orders FOR EACH ROW

UPDATE Balance_Due SET NEW.Balance_Due=NEW.Order_Total-NEW.Total_paid$$

DELIMITER ;

The query is executed well.

But when I insert a new row with values for Order_Total and Total_Paid i am seeing error code:1442. cannt update table 'order' in stored function/trigger becuase its is already used by statement which invoked this stored function/trigger

can any one please help me out with this if trigger is not possible to create , then some one help me how to create a procedure for this. Thank you



Edited 1 time(s). Last edit at 04/11/2014 09:38PM by Thejo Gundala.