UPDATE FROM SELECT IN TRIGGER

[Mark Wilson]

Below is an excerpt from an AFTER UPDATE TRIGGER that I have created on the table carrier_rates. My goal here is to update information in the lowest_carrier_rates table based on the results of the SELECT from the carrier_rates table. The statement works just fine outside of my trigger but when added to my trigger and then attempting to update a carrier_rates record I receive the following error message: "Can't update table 'carrier_rates' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

While I understand what the error message is staying - technically speaking this update statement is NOT updating the table 'carrier_rates' but rather only selected data from it. Any help is mucho appreciato for this newbie..maybe I need to be selecting the data in a different way? Thanks in advance for any help on this.

CODE:

UPDATE lowest_carrier_rates a, carrier_rates c
SET a.carrier_rate_id = c.carrier_rate_id, a.rate_type = CASE WHEN c.intrastate_rate IS NULL THEN 'A' ELSE CASE WHEN c.intrastate_rate < c.interstate_rate THEN 'B' ELSE 'A' END END WHERE a.npa = c.npa AND a.nxx = c.nxx AND c.npa = NEW.npa AND c.nxx = NEW.nxx;