Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Thursday, January 26, 2006

What Id should I use inside my trigger

That's amazing - almost like a chain reaction! Suddenly, everybody at Planet MySQL is writing something on triggers.

Well, to all those that want to try it too - that is - have a trigger perform an insert or update in anohter table here's a little tip that might be of use.

Suppose our trigger table has got an auto_increment column, and you'd want to be able to get your hands on the value of that item, what should you do? The answer was a bit of a surprise to me...

Suppose we have to tables, A and B and we want to have a trigger on A that inserts into B:
So, we need to someway transfer the generated id value for A into the corresponding field in B. Normally, you'd resort to the LAST_INSERT_ID, but this does tnot work inside a trigger.

Instead, use the new. pseudocolumn. That contains the new-to-be-inserted value in both the BEFORE and AFTER triggers.

7 comments:

Scott Noyes said...

Not according to the manual:

"In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the automatically generated sequence number that will be generated when the new record actually is inserted."

http://dev.mysql.com/doc/refman/5.1/en/using-triggers.html

Roland Bouman said...

Whoops! my bad. Thanks for pointing that out.

Indeed, the NEW. value does not work in the BEFORE INSERT trigger. It works only in the AFTER INSERT trigger.

I just reran the test, and my observation regarding LAST_INSERT_ID() does hold. That evaluates to 0 in both the BEFORE and the AFTER triggers.

(I tested this on MySQL 5.0.17)

Frankly Speaking! said...

Hey Roland,

Great article.

I just found out you were one of the grand prize winners by MySQL. Congrats dude and thanks for sharing your hard work.

Frank

Roland Bouman said...

Hi Frank,

thanks for your kind comments. It would've been nice if I had it right concerning the BEFORE trigger though ;-)

Take care and CU

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

I HAVE rOLAND RT3T TRIGGERS ON MY TOMS AND CANNOT ACHIEVE THE PICKUP OF THE TRIGGER PROPERLY. AM I DOING SOMETHING WRONG?

Roland Bouman said...

Anonymous, this makes zero sense to me. please try again.