RE: Trigger?

[Jerry Schwartz]

Aha! That was the clue I needed. Thank you so much.

 

So, to make sure I understand:

 

A “BEFORE” trigger is executed **between** the time that the record is
assembled and the time that the action occurs. That’s why the constraints on the
field value were being applied before my trigger was triggered.

 

Contrariwise, I assume that an “AFTER” trigger would be executed last, after
everything has been done.

 

Am I correct?

 

By the way, 

 

SET NEW.foo = IFNULL(NEW.foo, 'ok')

 

works just fine.

 

Regards,

 

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: [phone]

E-mail: jerry@stripped 

Web site: www.the-infoshop.com <http://www.the-infoshop.com/> 

 

From: Wagner Bianchi [mailto:wagnerbianchijr@stripped] 
Sent: Monday, December 20, 2010 6:44 PM
To: Jerry Schwartz
Cc: mysql@stripped
Subject: Re: Trigger?

 

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: [phone]
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