CODEWITHSUNDEEP
mysql
user3286430
user3286430

Reputation:

Updating a table in my trigger fails

I am trying to update a table using an after insert trigger using this code

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `le_log_trigger` AFTER INSERT ON `messagelog` FOR EACH ROW BEGIN
declare last_inserted_number VARCHAR(100) DEFAULT '0800100200';
declare last_inserted_code VARCHAR(100) DEFAULT 'Lorem Ipsum';
declare current_dataset VARCHAR(100) DEFAULT 'Lorem Ipsum';

set last_inserted_number = NEW.messagefrom;
set last_inserted_code = NEW.statuscode;
set current_dataset = (select task_name from running_tasks limit 1);

if(last_inserted_code = 201) then

update current_dataset set the_status = 'online' where device_number = last_inserted_number;

end if;

END//
DELIMITER ;

My database is called logan and i get this error when the trigger runs

/* SQL Error (1146): Table 'logan.current_dataset' doesn't exist */

Why is current_dataset not being treated as a variable?.

Upvotes: 1

Views: 110

Answers (1)

Marc B
Marc B

Reputation: 360662

Because you're trying to do an UPDATE query. MySQL expects to see a table name where you've got your variable, so it won't go "oh, hey, this was declared as a variable earlier". It'll just assume right away that it's supposed to be a literal table name, and look for that table... and since it can't be found (because it doesn't exist), you get that error.

If you DID by chance have a table where a field name did co-incide with a variable name, eg.

table x (foo int, bar int);
set bar='baz';

and did

UPDATE x SET foo=bar

then you've got a problem. Which bar should be used? Are you referring the variable, or to the field? That's why there's @:

UPDATE x SET foo=bar ; // use "bar" field in the table
UPDATE x SET foo=@bar; // use variable "bar" value.

And note that you couldn't use the variable as a table name, e.g.

SET x = 'tablename';
UPDATE @x SET ...

is just a flat-out syntax error. You'd have to build a query string inside your sproc, and then prepare/execute it.

Upvotes: 2

Related Questions