I think if you built a trigger to update value of foo's column after, this trigger will not be compiled cause it will execute two transactions on the same one. Try it...
Best regards. -- Wagner Bianchi 2010/12/21 Jerry Schwartz <je...@gii.co.jp> > 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 > > > > *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 > > >
