delimiter $$
create
/*[DEFINER = { user | CURRENT_USER }]*/
trigger `dst_111yao_dsms`.`stcok_h_qty` after update
on `dst_111yao_dsms`.`d_stock_h`
for each row
begin
if exists(select * from `dst_111yao_dsms`.`d_message_out_stock_h` where houseno = NEW.houseno and ware_code = NEW.ware_code)
then
update `dst_111yao_dsms`.`d_message_out_stock_h` set qty = (NEW.total_qty - NEW.wait_qty) where houseno = NEW.houseno and ware_code = NEW.ware_code;
else
insert into `dst_111yao_dsms`.`d_message_out_stock_h` (stock_id,houseno,ware_code,qty) values (NEW.id,NEW.houseno,NEW.ware_code,(NEW.total_qty - NEW.wait_qty));
end if;
end$$
delimiter ;
可以一边插入一边更新新表
注意;分号的位置