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.
BEFORE and AFTER triggers.
6 comments:
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)
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
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
I HAVE rOLAND RT3T TRIGGERS ON MY TOMS AND CANNOT ACHIEVE THE PICKUP OF THE TRIGGER PROPERLY. AM I DOING SOMETHING WRONG?
Anonymous, this makes zero sense to me. please try again.
Post a Comment