MySQL Forums
Forum List  »  Triggers

Can I place IF statement in trigger?
Posted by: Allen Zhang
Date: August 09, 2007 12:37AM

DELIMITER ||
CREATE TRIGGER oldFields BEFORE UPDATE ON order_request_item
FOR EACH ROW BEGIN
DECLARE changed INT DEFAULT 0;
DECLARE item_name VARCHAR(20);
DECLARE cat_no VARCHAR(20);
DECLARE description TEXT;
DECLARE quantity INT(11);
DECLARE unit_price FLOAT;
DECLARE size VARCHAR(20);
DECLARE quotation_no VARCHAR(20);
DECLARE comment TEXT;

IF NEW.item_name != OLD.item_name THEN
SET item_name = NEW.item_name;
SET changed = 1;
END IF;
IF NEW.cat_no != OLD.cat_no THEN
SET cat_no = NEW.cat_no;
SET changed = 1;
END IF;
IF NEW.description != OLD.description THEN
SET description = NEW.description;
SET changed = 1;
END IF;
IF NEW.quantity != OLD.quantity THEN
SET quantity = NEW.quantity;
SET changed = 1;
END IF;
IF NEW.unit_price != OLD.unit_price THEN
SET unit_price = NEW.unit_price;
SET changed = 1;
END IF;
IF NEW.quotation_no != OLD.quotation_no THEN
SET size = NEW.size;
SET changed = 1;
END IF;
IF NEW.size != OLD.size THEN
SET quotation_no = NEW.quotation_no;
SET changed = 1;
END IF;
IF NEW.comment != OLD.comment THEN
SET comment = NEW.comment;
SET changed = 1;
END IF;

IF changed >= 0 THEN
INSERT INTO notification_mail SET item_id = NEW.item_id, create_time = now(), mail_to = OLD.end_user, item_name = item_name, cat_no = cat_no, description = description, quantity = quantity, unit_price = unit_price, size = size,quotation_no = quotation_no, comment = comment;
END IF;
END;
||

It seems that the if statement in trigger cause problems.
I want to insert changed values into the notification_mail table, what shall I do?
Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Can I place IF statement in trigger?
52580
August 09, 2007 12:37AM
15316
September 24, 2007 08:59PM
4931
September 25, 2007 09:30PM
3901
September 27, 2007 09:04PM
5354
September 29, 2007 12:12AM


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.