Assigning column value in Trigger causes "Unknown system variable" error

Question

All the examples I've found seem the same as my code, yet I am having a hard time getting it to work. Here's the table,

CREATE TABLE `samfoo` (
`test` varchar(10) default NULL,
`trig_field` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And here's the trigger:

DELIMITER $$

DROP TRIGGER IF EXISTS footrigger$$

CREATE TRIGGER samtrigger
BEFORE INSERT ON samfoo
FOR EACH ROW
BEGIN
  SET trig_field = 1;
END$$

DELIMITER ;

When I run this, I get the error Unknown system variable 'trig_field'. Any ideas?

a1ex07 · Accepted Answer · 2013-01-10 23:05:20Z

6

It should be SET NEW.trig_field := 1;

answered Jan 10, 2013 at 23:05

a1ex07

37.4k12 gold badges94 silver badges104 bronze badges

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

5 Comments

Sam Selikoff Over a year ago

THANK YOU. For some reason I could not find an example or tutorial, and the documentation (as usual) was way too verbose. What does the := operator mean?

Sam Selikoff Over a year ago

My actual code is SET NEW.date_string := date(time_stamp);, and I'm getting an error: `unknown column 'time_stamp' in 'field list'. Any ideas?

a1ex07 Over a year ago

what is time_stamp? If it's a field in the same table, you need to access it like NEW.time_stamp or OLD.time_stamp (in your case since it's before insert trigger OLD. doesn't make sense, it should be NEW.time_stamp).

Sam Selikoff Over a year ago

Wonderful! So what exactly do NEW and OLD refer to? And what should I read to really understand this stuff? I hate hacking it together like this. You've been extremely helpful, by the way.

a1ex07 Over a year ago

new and old are pseudo-rows that are available inside row-level triggers and allow accessing new and old values of the modified record respectively. new makes no sense in delete triggers, old - in insert triggers. To summarize, in insert triggers you can access only new, in update - new and old, in delete - only old.

Collectives™ on Stack Overflow

Assigning column value in Trigger causes "Unknown system variable" error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related