TRIGGERS: getting information about the row about to be updated

Question

Say I've got the following fields that make up a unique key in my table: (productid varchar(30), dateset date, dateend date)

I intend for dateend to be null until another row with the same productid with a newer dateset has been inserted, like this:

+----------+-------------+-------------+
|productid | dateset     | dateend     |
+----------+-------------+-------------+
|'PRODUCTA'|'2014-10-09' | NULL        |
+----------+-------------+-------------+
|'PRODUCTA'|'2009-08-06' |'2014-10-09' |
+----------+-------------+-------------+

Now, I'm thinking about how I'd safely do this and I figure I'd run a trigger before the new row is inserted. It would essentially find the current max(dateset) where productid = @productid, and if it exists, it will update this row's dateend with @newdateset, the date that our new, succeeding row is inserted. The problem is, I have no idea how to get the variable @productid, or @newdateset. Is there any way to get the information of a row that's now about to be inserted? If I wrote:

insert into products (productid, dateset) values ('DEMOA', '2014-10-10')

How would I get my trigger to say @productid = 'DEMOA', @newdateset = '2014-10-10'?

Thanks,

Zakiir

One of the most important aspects of a trigger is to be able to identify the values. Otherwise how could you build proper logic that uses those values? And so, it is explained in one of the first paragraphs of the [MySQL documentation about triggers](http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html). One of the most important aspects of a developer is to be able to find, interpret and use resources about the tools they work with. And so, I downvoted this question, since you didn't seem to make an effort to even find those docs. — GolezTrol, Oct 10 '14 at 16:09
You're right, I didn't look through the documentation thoroughly. It is there in plain sight, I missed it. NEW.col_name — zedjay72, Oct 10 '14 at 16:24
@zedjay72 If you found an answer to your question, it would help other people who Google later if you answer your own question concisely below. Provide code demonstrating the trigger if you can. — Conspicuous Compiler, Oct 10 '14 at 17:26

score 0 · Answer 1 · edited May 23 '17 at 12:20

To reference a column in a row that's now about to be updated, you'd write NEW.col_name, in my case:

NEW.productid

would reference 'DEMOA' in the following insert statement:

insert into products (productid, dateset) values ('DEMOA', '2014-10-10');

Like so:

delimiter //

CREATE TRIGGER update_row_version BEFORE INSERT ON products 
FOR EACH ROW
BEGIN 
SELECT productid, max(dateset) INTO @productid, @maxdateset FROM products WHERE productid = NEW.productid; 
END;//

delimiter ;

INSERT INTO products (productid, price_local) VALUES ('DEMOA', 3000.00);

select @productid;

+-----------+
| @productid|
+-----------+
| DEMOA     |
+-----------+

Apparently, what I was trying to achieve isn't possible as a trigger does not allow me to UPDATE the same table that I'm trying to INSERT into (Error 1442). There's already a post about it:

mysql trigger stored trigger is already used by statement which invoked stored trigger

TRIGGERS: getting information about the row about to be updated

1 Answers1