MySQL Forums
Forum List  »  Triggers

update trigger to query before inserting into new database
Posted by: William Peterson
Date: June 25, 2007 02:38AM

Hi. I am trying to get a trigger to run a query and insert results in a different database based on an update done to a specific field.

Overall: There are two databases: shoppingcarttest and test.
In shoppingcarttest there are two tables of importance called: tracking and trackitem

in tracking, there are two fields of importance: track_id, track_status
in trackitem, there are also two fields of importance: trackitem_tracknum, trackitem_itemnumber

In the database called 'test' there is only one table of importance called:trigger
In the trigger table is one field of importance called:shoppingcartid

What I want to do is whenever shoppingcarttest.tracking(track_status) changes from P (for pending) to C (for complete), I want it to get the order number stored in shoppingcarttest.tracking(track_id)
Then, I want it to run the ordernumber through shoppingcarttest.trackitem(trackitem_tracknum) and take the trackitem_itemnumber and insert it into test.trigger(shoppingcartid)

the furthest I got was this

CREATE TRIGGER addinventory
IF (NEW.tracking_status != OLD.tracking_status) THEN
INSERT INTO test.trigger(shoppingcartid) (SELECT * FROM trackitem WHERE trackitem(trackitem_tracknum)=tracking(tracking_id));

which only activates if the tracking_status column is changed but then gives me this error message.
#1305 - FUNCTION shoppingtest.trackitem does not exist

Apparently it is looking for a function and not a table.

Is there any way I can acheive this? Please help.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.