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 <[email protected]>
> 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: [email protected]
> Web site: www.the-infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[email protected]
>
>
