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?
52589
August 09, 2007 12:37AM
18432
August 09, 2007 08:13AM
8179
August 09, 2007 07:52PM
15326
September 24, 2007 08:59PM
5871
September 25, 2007 09:42AM
4935
September 25, 2007 09:30PM
5137
September 26, 2007 06:28AM
3904
September 27, 2007 09:04PM
5781
September 28, 2007 02:35AM
5356
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.