Here's my latest attempt:
localhost >CREATE TRIGGER makefoo BEFORE INSERT ON testtrigger
-> FOR EACH ROW
-> SET NEW.foo = IFNULL(NEW.foo, 'ok')
-> |
Query OK, 0 rows affected (0.00 sec)
As you can see, the trigger syntax is correct; but it doesn't do what I want.
localhost >INSERT INTO testtrigger (id, foo) VALUES (NULL, 'xxx');
Query OK, 1 row affected (0.00 sec)
localhost >INSERT INTO testtrigger (id, foo) VALUES (NULL, NULL);
ERROR 1048 (23000): Column 'foo' cannot be null
localhost >INSERT INTO testtrigger (id) VALUES (NULL);
ERROR 1364 (HY000): Field 'foo' doesn't have a default value
So I'm missing something important.
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
>-----Original Message-----
>From: Michael Dykman [mailto:mdyk...@gmail.com]
>Sent: Monday, December 20, 2010 6:25 PM
>To: Jerry Schwartz
>Cc: mysql@lists.mysql.com
>Subject: Re: Trigger?
>
>The expression you supplied looks right enough.. how was it declared?
> as an on UPDATE/on INSERT trigger or just a single case?
>
>
> - michael dykman
>
>On Mon, Dec 20, 2010 at 5:21 PM, Jerry Schwartz <je...@gii.co.jp> wrote:
>> 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=mdyk...@gmail.com
>>
>>
>
>
>
>--
> - michael dykman
> - mdyk...@gmail.com
>
> May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
