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.
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
Web site: www.the-infoshop.com
>From: Michael Dykman [mailto:mdykman@stripped]
>Sent: Monday, December 20, 2010 6:25 PM
>To: Jerry Schwartz
>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 <jerry@stripped> wrote:
>> I've never used a trigger before, and I want to make one that sounds like
>> 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).
>> Jerry Schwartz
>> Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>> 860.674.8796 / FAX: 860.674.8341
>> E-mail: jerry@stripped
>> 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=1
> - michael dykman
> - mdykman@stripped
> May the Source be with you.