Bug #11648 Wrong error when creating invalid trigger

• Submitted: 29 Jun 2005 23:20
• Modified: 10 Jul 2005 5:21
• Reporter: Omer Barnir (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.8
• Assigned to: Dmitry Lenev
• CPU Architecture: Any

[29 Jun 2005 23:20] Omer Barnir

Description:
When creating a delete trigger that includes assigning  a value 'new.<col> 
a syntax error (1064) is returned instead of a trigger error  'trg_cant_change_row' error (1362).

As follows:
omer@linux:~/source/src50_0620/client> ./mysql --sock=../mysql-test/var/tmp/master.sock --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.8-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table t1 (f1 integer);
Query OK, 0 rows affected (0.05 sec)

mysql> create trigger tr1 after delete on t1 for each row f1.new=18;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.new=18' at line 1
mysql>         
### OBN> Expected error 1362

How to repeat:
Run the following from the MySQL client:

run the following in the MySQL client:

use test;
create table t1 (f1 integer);
create trigger tr1 after delete on t1 for each row f1.new=18;
drop table t1;

Suggested fix:
Change error code in the above to 1362.

[10 Jul 2005 5:21] Dmitry Lenev

Hi, Omer!

This is not a bug. "Correct" version of your CREATE TRIGGER statement is:

create trigger tr1 after delete on t1 for each row set new.f1=18;

Note the "SET" keyword and difference between "new.f1" and "f1.new" used in your statement. Such "correct" statement fails with error that you expect:

mysql> create trigger tr1 after delete on t1 for each row set new.f1=18;
ERROR 1363 (HY000): There is no NEW row in on DELETE trigger