delimiter //

create trigger topic_insert_trigger before insert on dw_topic_dim for each row begin

if new.exchange_id is not null then

   case new.exchange_id 

		when 6 then set new.exchange = 'TSX-V'; 
		when 5 then set new.exchange = 'TSX'; 
      when 4 then set new.exchange = 'AMEX'; 
      when 2 then set new.exchange = 'NASDAQ'; 
      when 1 then set new.exchange = 'NYSE'; 
      when 3 then set new.exchange = 'OBB';

   end case;

elseif new.exchange is not null then

   case new.exchange

		when 'TSX-V' then set new.exchange_id = 6;
		when 'TSX' then set new.exchange_id = 5;
      when 'AMEX' then set new.exchange_id = 4;
      when 'NASDAQ' then set new.exchange_id = 2;
      when 'NYSE' then set new.exchange_id = 1;
      when 'OBB' then set new.exchange_id = 3;

   end case;

end if;
end;

//

delimiter ;