Re: MYSQL triggers/procedures returning 0 in count query for a table even if there are records in that table

[Peter Brawley]

use test;
drop table if exists test, replicationaudit;
create table test(i int primary key auto_increment, j int);
create table replicationaudit( 
  id int primary key auto_increment, 
  replicationdescription varchar(32) ,
  replicationstatusid int  
  )  ;
insert into replicationaudit values(1,'full replication',1);
drop trigger if exists testins;
delimiter go
create trigger testins before insert on test for each row
begin
  if( select count(1) 
      from replicationaudit 
      where (ltrim(rtrim(lower(replicationdescription))) = 'full replication' 
          or ltrim(rtrim(lower(replicationdescription))) like '%base replication%') 
      and replicationstatusid = 1
    ) then
      set new.j = 1;
  end if;
end;
go
delimiter ;
insert into test set j=0;
select * from test;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
+---+------+

Can't replicate your issue, what does the above script do in your system, what is your MySQL version?