MySQL trigger reports error when inserting

Question

I've created a trigger on a table to insert values into one of the columns in the same table after a row is inserted. What gets inserted into the column depends on a set of validations as you can see from the below code.

CREATE TRIGGER `FMY_NUM` AFTER INSERT ON `table_1`
   FOR EACH ROW BEGIN
   DECLARE LC_VAR INTEGER;
   CASE 
      WHEN table_1. txt_avl>= 7 THEN SET LC_VAR = 5;
      WHEN table_1.txt_avl > 9 AND table_1.txt_avl < 5000 THEN SET LC_VAR = 3;
      WHEN table_1.txt_avl > 11 AND table_1.txt_avl < 3000 THEN SET LC_VAR = 2;
      ELSE SET LC_VAR = 1;
   END CASE;
   UPDATE table_1 set table_1.v1order = LC_VAR WHERE v1Pkey = NEW.v1Pkey;   

END

The problem I have is, when I run an insert into table statement, I get the error,

MySQL said: Documentation

#1109 - Unknown table 'table_1' in field list

I've never worked on triggers in MySQL before. Trying to get my head around this. Could someone please help me here?

Luke Woodward · Accepted Answer · 2015-06-19 19:02:07Z

3

There are a number of things wrong with your trigger.

Firstly, the error about Unknown table 'table_1' in field list refers to your use of table_1.txt_avl in the WHEN clauses of your CASE statement. It seems you want to access the value of txt_avl in the row being inserted, and to do that, you use new.txt_avl instead.

This change alone however isn't enough to get the trigger to work. If you make this change to your trigger, you will get the following error (or at least I did, anyway):

ERROR 1235 (42000): This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'

The problem here is the use of an UPDATE statement that updates table_1 within an AFTER INSERT trigger on the same table. If you want to set the value of a column in the new row, you can do this by setting the new value of the column, using SET new.v1order = LC_VAR;. However, you must also change the trigger to fire BEFORE INSERT instead of AFTER INSERT. You can't change a column like this in an AFTER trigger, as by the time your trigger has been called it is too late - the data has already hit the database.

Once this is done the trigger appears to work. Here's a quick session where I create a table and your trigger, insert some data and verify that the trigger fired and did something:

MariaDB [blah]> create table table_1 (v1order int, v1Pkey int, txt_avl int);
Query OK, 0 rows affected (0.18 sec)

MariaDB [blah]> DELIMITER $$
MariaDB [blah]> CREATE TRIGGER `FMY_NUM` BEFORE INSERT ON `table_1`
    ->    FOR EACH ROW BEGIN
    ->    DECLARE LC_VAR INTEGER;
    ->    CASE 
    ->       WHEN new.txt_avl>= 7 THEN SET LC_VAR = 5;
    ->       WHEN new.txt_avl > 9 AND new.txt_avl < 5000 THEN SET LC_VAR = 3;
    ->       WHEN new.txt_avl > 11 AND new.txt_avl < 3000 THEN SET LC_VAR = 2;
    ->       ELSE SET LC_VAR = 1;
    ->    END CASE;
    ->    SET new.v1order = LC_VAR;
    -> END;
    -> $$
Query OK, 0 rows affected (0.06 sec)

MariaDB [blah]> DELIMITER ;
MariaDB [blah]> insert into table_1 (v1Pkey, txt_avl) values (1, 8);
Query OK, 1 row affected (0.03 sec)

MariaDB [blah]> select * from table_1;
+---------+--------+---------+
| v1order | v1Pkey | txt_avl |
+---------+--------+---------+
|       5 |      1 |       8 |
+---------+--------+---------+
1 row in set (0.00 sec)

edited Jun 19, 2015 at 19:02

answered May 23, 2015 at 11:20

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

User12345 Over a year ago

Hello,Many many thanks for the solution. I get a syntax error with this now!! MySQL said: Documentation #1064 - 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 '' at line 3

User12345 Over a year ago

CREATE TRIGGER FMY_NUM BEFORE INSERT ON table_1 FOR EACH ROW BEGIN DECLARE LC_VAR INTEGER; CASE WHEN new.txt_avl>= 7 THEN SET LC_VAR = 5; WHEN new.txt_avl > 9 AND new.txt_avl < 5000 THEN SET LC_VAR = 3; WHEN new.txt_avl > 11 AND new.txt_avl < 3000 THEN SET LC_VAR = 2; ELSE SET LC_VAR = 1; END CASE; SET new.v1order = LC_VAR; END;

User12345 Over a year ago

The above is what I've got. I removed the semicolon after integer declaration & changed WHEN new.txt_avl>= 7to new.txt_avl >=7. Even the earlier running trigger seems to error out while creating. ANy guidance please?

User12345 Over a year ago

Found the problem. It was me being dumb there. While using PHPMyAdmin, the SQL text editor doesn't recognize the option "DELIMITER $$" at the start & DELIMITER; at the end. One has to set the delimiter though & the way to that is change the DELIMITER from the default of ;; to say $$ in the small box right below the SQL editor => [ Delimiter ]Show this query here againRetain query boxRollback when finished

User12345 Over a year ago

Thank you Luke once again. Totally appreciate your pitching in & blowing the problem

Rahul Kumar · Accepted Answer · 2015-05-23 10:31:32Z

0

This error means that your database doesn't have any table named as table_1

answered May 23, 2015 at 10:31

Rahul Kumar

573 silver badges11 bronze badges

1 Comment

User12345 Over a year ago

It's definitely there. I can view it through PHPMyAdmin GUI & also describe the table.Any help in re-writing the PLSQL trigger would also benefit me please.

Collectives™ on Stack Overflow

MySQL trigger reports error when inserting

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related