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.

6 comments:

rpbouman 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)

Frank 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

rpbouman 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?

rpbouman said...

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

DuckDB bag of tricks: Processing PGN chess games with DuckDB - Rolling up each game's lines into a single game row (6/6)

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB . This post is the sixth installment of a s...