List:General Discussion« Previous MessageNext Message »
From:Wagner Bianchi Date:December 20 2010 11:43pm
Subject:Re: Trigger?
View as plain text  
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 <jerry@stripped>

> 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
>
>

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