Can I place IF statement in trigger?
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.
Subject
Views
Written By
Posted
Can I place IF statement in trigger?
52580
August 09, 2007 12:37AM
18429
August 09, 2007 08:13AM
8176
August 09, 2007 07:52PM
15316
September 24, 2007 08:59PM
5868
September 25, 2007 09:42AM
4931
September 25, 2007 09:30PM
5135
September 26, 2007 06:28AM
3901
September 27, 2007 09:04PM
5776
September 28, 2007 02:35AM
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.