sql - MySQL transaction and triggers -

hey guys, here 1 not able figure out. have table in database, php inserts records. created trigger compute value inserted well. computed value should unique. happens time time have exact same number few rows in table. number combination of year, month , day , number of order day. thought single operation of insert atomic , table locked while transaction in progress. need computed value unique...the server version 5.0.88. server linux centos 5 dual core processor.

here trigger:

create trigger bi_order_data before insert on order_data each row begin   set new.auth_code = get_auth_code(); end; 

corresponding routine looks this:

create function `get_auth_code`() returns bigint(20) begin     declare my_auth_code, acode bigint;     select max(d.auth_code) my_auth_code         orders_data d         join orders o on (o.order_id = d.order_id)         date(now()) = date(o.date);      if my_auth_code null         set acode = ((date_format(now(), "%y%m%d")) + 100000) * 10000 + 1;     else         set acode = my_auth_code + 1;     end if;     return acode; end 

i thought single operation of insert atomic , table locked while transaction in progress

either table locked (myisam used) or records may locked (innodb used), not both.

since mentioned "transaction", assume innodb in use. 1 of innodb advantages absence of table locks, nothing prevent many triggers' bodies executed simultaneously , produce same result.