How to create a trigger with multiple actions in MySQL 5.0.45?

Question

I'm working in phpMyAdmin and I'm new to creating MySQL 5.0.45 triggers. I'm trying to create a trigger that will help me validate data by throwing an error when a value it out of range.

This works just fine:

create trigger t1
before insert
on hvi
for each row
begin
declare dummy int;
if new.`Moist (dry%)` <1 then
select `Moist(dry%) cannot be less than 1`
into dummy
from hvi
where id = new.`Moist (dry%)`;
end if;
end;

But I need to add more actions to this trigger. I tired this:

create trigger t1
before insert
on hvi
for each row
begin
declare dummy int;
if new.`Moist (dry%)` <1 then
select `Moist(dry%) cannot be less than 1`
into dummy
from hvi
where id = new.`Moist (dry%)`;
end if;
if new.`Moist (dry%)` >50 then
select `Moist(dry%) cannot be greater than 50`
into dummy
from hvi
where id = new.`Moist (dry%)`;
end if;
end;

but it returned this error "#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' "

Does anyone know how I can add multiple actions to a trigger? (Multiple if-then statements? I'll eventually need to add about 20.)

Thanks!

Answer 1

You need to drop your existing trigger before you create the new one:

DROP TRIGGER IF EXISTS t1;
CREATE TRIGGER t1
...