Trigger?

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@gii.co.jp
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=gcdmg-mysql-2@m.gmane.org

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 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` (
> =A0`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> =A0`foo` char(10) NOT NULL,
> =A0PRIMARY KEY (`id`)
> ) ENGINE=3DInnoDB AUTO_INCREMENT=3D4 DEFAULT CHARSET=3Dutf8
>
> 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 =3D 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@gii.co.jp
> Web site: www.the-infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Trigger?

--0016e6d6481a11a2310497e018e2
Content-Type: text/plain; charset=ISO-8859-1

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

> 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@gii.co.jp
> 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=wagnerbianchijr@gmail.com
>
>

--0016e6d6481a11a2310497e018e2--

RE: Trigger?

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@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@gmail.com]
>Sent: Monday, December 20, 2010 6:25 PM
>To: Jerry Schwartz
>Cc: mysql@lists.mysql.com
>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 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@gii.co.jp
>> 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=mdykman@gmail.com
>>
>>
>
>
>
>--
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Trigger?

------=_NextPart_000_006B_01CBA0F8.3D926910
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

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

=20

So, to make sure I understand:

=20

A â€=9CBEFOREâ€=9D trigger is executed **between** the time that =
the record is assembled and the time that the action occurs. =
Thatâ€=99s why the constraints on the field value were being applied =
before my trigger was triggered.

=20

Contrariwise, I assume that an â€=9CAFTERâ€=9D trigger would be =
executed last, after everything has been done.

=20

Am I correct?

=20

By the way,=20

=20

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

=20

works just fine.

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

Web site: www.the-infoshop.com =20

=20

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

=20

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=3DInnoDB AUTO_INCREMENT=3D4 DEFAULT CHARSET=3Dutf8
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 =3D '') then
-> set NEW.foo =3D 'Ok';
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.04 sec)

mysql> insert into testtrigger set id =3D100, foo =3Dnull;
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 =
=3Dnull 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 =3D100, foo =3Dnull;
ERROR 1048 (23000): Column 'foo' cannot be null

Did you get?



Best regards.

--

Wagner Bianchi

=20

2010/12/20 Jerry Schwartz

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=3DInnoDB AUTO_INCREMENT=3D4 DEFAULT CHARSET=3Dutf8

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 =3D 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@gii.co.jp
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=3Dwagnerbianchijr@gmail.c om

=20


------=_NextPart_000_006B_01CBA0F8.3D926910--

Re: Trigger?

--0016e6d97652d09a7e0497f4bc09
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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

> Aha! That was the clue I needed. Thank you so much.
>
>
>
> So, to make sure I understand:
>
>
>
> A =93BEFORE=94 trigger is executed **between** the time that the record i=
s
> assembled and the time that the action occurs. That=92s why the constrain=
ts on
> the field value were being applied before my trigger was triggered.
>
>
>
> Contrariwise, I assume that an =93AFTER=94 trigger would be executed last=
,
> after everything has been done.
>
>
>
> Am I correct?
>
>
>
> By the way,
>
>
>
> SET NEW.foo =3D 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@gii.co.jp
>
> Web site: www.the-infoshop.com
>
>
>
> *From:* Wagner Bianchi [mailto:wagnerbianchijr@gmail.com]
> *Sent:* Monday, December 20, 2010 6:44 PM
>
> *To:* Jerry Schwartz
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: Trigger?
>
>
>
> Well, to produce this result, the first thing that we have to do is to *g=
et
> 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=3DInnoDB AUTO_INCREMENT=3D4 DEFAULT CHARSET=3Dutf8
> 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 =3D '') then
> -> set NEW.foo =3D 'Ok';
> -> end if;
> -> end;
> -> //
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> insert into testtrigger set id =3D100, foo =3Dnull;
> 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 =3D=
null
> with a query cause column don't accept null values. The column was define=
d
> 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 =3D100, foo =3Dnull;
> ERROR 1048 (23000): Column 'foo' cannot be null
>
> Did you get?
>
> Best regards.
>
> --
>
> Wagner Bianchi
>
>
>
> 2010/12/20 Jerry Schwartz
>
> 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=3DInnoDB AUTO_INCREMENT=3D4 DEFAULT CHARSET=3Dutf8
>
> 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 =3D 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@gii.co.jp
> 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=3Dwagnerbianchijr@gmail.c om
>
>
>

--0016e6d97652d09a7e0497f4bc09--