0

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!

Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
kmcamara
  • 187
  • 3
  • 5
  • 16

1 Answers1

2

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

DROP TRIGGER IF EXISTS t1;
CREATE TRIGGER t1
...
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • Ike was correct here. I accidentally had another trigger on this table. Once I deleted it . . . I could create my t1 here with no problem. – kmcamara Jun 09 '11 at 17:04