MySQL Forums
Forum List  »  Triggers

Re: Get Updated columns
Posted by: Jay Pipes
Date: January 26, 2006 07:55PM

Rich,

Not quite sure how you arrived at this conclusion. See below.

mysql> use test;
Database changed
mysql> CREATE TABLE Rich (
-> my_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
-> , my_char CHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO Rich (my_char) VALUES ('house'),('street');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> DELIMITER //
mysql> CREATE TRIGGER upd_trg BEFORE UPDATE ON Rich
-> FOR EACH ROW
-> BEGIN
-> IF OLD.my_char != NEW.my_char THEN
-> INSERT INTO Rich2 VALUES ('not the same');
-> ELSE
-> INSERT INTO Rich2 VALUES ('same');
-> END IF;
-> END//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> UPDATE Rich SET my_char = 'house2' WHERE my_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE Rich SET my_id = 3 WHERE my_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM Rich2;
+--------------+
| logdata |
+--------------+
| not the same |
| same |
+--------------+
2 rows in set (0.00 sec)

Unless I've got something mixed up, by your argument, the second logged trigger insert would have said "Not the same", since you say that NULL != value. But, as far as I can see, MySQL does indeed return that the OLD.my_char = NEW.my_char when the my_char field is not updated.

Looking forward to seeing how you tested this... Please let me know if you can reproduce your scenario, as it would indeed be a bug.

Thanks!

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3853
December 07, 2005 11:47AM
1982
December 07, 2005 02:09PM
1609
January 25, 2006 09:41AM
Re: Get Updated columns
2265
January 26, 2006 07:55PM
1977
January 27, 2006 12:47PM


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.