List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:December 21 2010 2:36pm
Subject:RE: Trigger?
View as plain text  
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: jerry@stripped
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@stripped]
>Sent: Monday, December 20, 2010 6:25 PM
>To: Jerry Schwartz
>Cc: mysql@stripped
>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 
>> 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: 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.



Thread
Trigger?Jerry Schwartz20 Dec
  • Re: Trigger?Michael Dykman21 Dec
    • RE: Trigger?Jerry Schwartz21 Dec
  • Re: Trigger?Wagner Bianchi21 Dec
    • RE: Trigger?Jerry Schwartz21 Dec
      • Re: Trigger?Wagner Bianchi22 Dec