MySQL Forums
Forum List  »  Triggers

Update of a single column triggers update of unrelated one
Posted by: Yannick Gansemans
Date: March 12, 2012 04:26AM

Hi,

I am new to mySQL triggers. I have a simple table (id, name, content columns) with an insert and update trigger that should handle the 'content'column. When I issue an update command that does not involve the column handled bu the trigger, it still does its action. What is the proper way to detect if a value was passed for a given column inside the trigger? Testing for NULL does not seem to work. The SQL to illustrate my problem is:


-- Create a test table
--
CREATE TABLE IF NOT EXISTS `ttt` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(40),
`content` varchar(400),
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert trigger
--
delimiter //
DROP TRIGGER IF EXISTS `ttt_insert`//
CREATE TRIGGER ttt_insert BEFORE INSERT ON `ttt`
FOR EACH ROW
BEGIN
IF NEW.name IS NOT NULL THEN
SET NEW.name = CONCAT("new_", NEW.name);
END IF;
END;//
delimiter ;

-- Update trigger
--
delimiter //
DROP TRIGGER IF EXISTS `ttt_update`//
CREATE TRIGGER ttt_update BEFORE UPDATE ON `ttt`
FOR EACH ROW
BEGIN
IF NEW.name IS NOT NULL THEN
SET NEW.name = CONCAT("upd_", NEW.name);
END IF;
END;//
delimiter ;

-- Populate table
--
INSERT INTO ttt (name, content) VALUES("name", "Contents go here.");

mysql> select * from ttt;
+----+----------+-------------------+
| id | name | content |
+----+----------+-------------------+
| 1 | new_name | Contents go here. |
+----+----------+-------------------+
1 row in set (0.00 sec)

-- Update the content for the first row
--
mysql> update ttt set content="New content replacing old" where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttt;
+----+--------------+---------------------------+
| id | name | content |
+----+--------------+---------------------------+
| 1 | upd_new_name | New content replacing old |
+----+--------------+---------------------------+
1 row in set (0.00 sec)

Why is the name field updated by the trigger? How do I avoid that (the update trigger should be able to detect if a vaulue was passed for the 'name' column)?


Any suggestions are welcome. Thanks in advance.

Yannick

Options: ReplyQuote


Subject
Views
Written By
Posted
Update of a single column triggers update of unrelated one
8309
March 12, 2012 04:26AM


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.