List:General Discussion« Previous MessageNext Message »
From:Wagner Bianchi Date:December 22 2010 12:21am
Subject:Re: Trigger?
View as plain text  
I think if you built a trigger to update value of foo's column after, this
trigger will not be compiled cause it will execute two transactions on the
same one. Try it...

Best regards.
--
Wagner Bianchi


2010/12/21 Jerry Schwartz <jerry@stripped>

> 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: 860.674.8341
>
> E-mail: jerry@stripped
>
> Web site: 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: 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