Aha! That was the clue I needed. Thank you so much.
So, to make sure I understand: A “BEFORE” trigger is executed **between** the time that the record is assembled and the time that the action occurs. That’s why the constraints on the field value were being applied before my trigger was triggered. Contrariwise, I assume that an “AFTER” trigger would be executed last, after everything has been done. Am I correct? By the way, SET NEW.foo = IFNULL(NEW.foo, 'ok') works just fine. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com <http://www.the-infoshop.com/> From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Monday, December 20, 2010 6:44 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Trigger? Well, to produce this result, the first thing that we have to do is to get rid of the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql> show create table testtrigger\G *************************** 1. row *************************** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql> create trigger trg_test -> before insert on testtrigger -> for each row -> begin -> if(NEW.foo IS NULL || NEW.foo = '') then -> set NEW.foo = 'Ok'; -> end if; -> end; -> // Query OK, 0 rows affected (0.04 sec) mysql> insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql> select * from testtrigger; +-----+------+ | id | foo | +-----+------+ | 100 | Ok | +-----+------+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql> alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz <je...@gii.co.jp> I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
