MySQL Forums
Forum List  »  Triggers

help me with this trigger
Posted by: Thejo Gundala
Date: April 11, 2014 08:46PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
help me with this trigger
2048
April 11, 2014 08:46PM
994
April 12, 2014 07:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.