DELIMITER ##
create trigger tra_Price after update on assets_cdn_charge for each row
begin
declare res int;
declare ids int;
declare idq int;
declare idt int;
set res = (select price from assets_cdn_charge where price = new.price);
set ids = (select id from assets_cdn_charge where price = new.price);
DECLARE cur CURSOR FOR SELECT id FROM assets_cdn_composite WHERE cdn_charge_id = ids;
open cur;
ins_loop:LOOP
fetch cur into idq;
declare curs cursor for select id from assets_cdn_traffic where domain_name_id = idq;
open curs;
ins1_loop:LOOP
fetch curs into idt;
update assets_cdn_traffic set cost = traffic * res where domain_namd_id = idt;
end LOOP;
close curs;
end LOOP;
close cur;
END; ##
when I run this code,I had get this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE cur CURSOR FOR SELECT id FROM assets_cdn_composite WHERE cdn_charge_id =' at line 9
Probably because:
DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.
...so that line should likely be moved before the two set statements above that point.
I believe that you're going to have other trouble if that fixes that error, because I don't know what you expect that query to retrieve as idq is declared before that point, but is not set to any value?
=====
UPDATE:
Below is an example from a previous comment about the possibility to eliminate cursors altogether. Try this:
BEGIN
UPDATE assets_cdn_traffic
JOIN assets_cdn_composite ON assets_cdn_traffic.domain_name_id = assets_cdn_composite.cdn_charge_id
JOIN assets_cdn_charge ON assets_cdn_charge.id = assets_cdn_composite.cdn_charge_id
SET cost = traffic * NEW.price
WHERE assets_cdn_charge.id = NEW.id
END
However, I would try the update query separately before using in the trigger to make sure that it works as expected. Replace the NEW.price and the NEW.id with test values to verify the handling.