Trigger Error MySQL

Question

I'm having a trigger error its not executing the syntax seems fine to me

I'm trying to preserve the functional dependency dtype->skill

delimiter |

    CREATE TRIGGER htmlid 
    BEFORE INSERT ON Droid
    FOR EACH ROW
    BEGIN
      IF EXISTS(

       Select dtype t, mainskill m from Droid where t=NEW.dtype AND m!=NEW.m;

    )

      END IF;
    END
    |
    delimiter ;

Any help?

EDIT: I've tried this and it still doesn't work gives a syntax error says check ur manual

create trigger foo before insert on Droid
 for each row
 begin
   IF EXISTS(
 Select dtype t, mainskill m from Droid where t=NEW.dtype AND m!=NEW.m;
) THEN signal sqlstate '45000';
end if;
end;$$

heres the error I'm getting for the answer by anonymous

stack trace:  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near '' atline 5
mysql>
mysql>  SET @recCount = (Select count(*) from Droid where dtype=NEW.dtype AND minskill = NEW.mainskill);
ERROR 1054 (42S22): Unknown column 'NEW.dtype' in 'where clause'
mysql>  If @recCount > 0 THEN
    ->      set raise_err_msg = concat('Error: <put error msg here> ', cast(dtye as char));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'If @ecCount > 0 THEN
     set raise_err_msg = concat('Error: <put error msg her' at line 1
mysql>      signal sqlstate '45000' set message_text = raise_err_msg;
ERROR 1054 (42S22): Unknown column 'raise_err_msg' in 'field list'
mysql>  END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'END F' at line 1

this trigger is doing nothing. Are you trying to update the table Droid? — jose_bacoy, Mar 27 '18 at 18:16
I am trying to avoid an INSERT if there exists a droid in the table which has the same type as the one I'm inserting but if it has a different skill the insert should fail @âńōŋŷXmoůŜ — rahul chawla, Mar 27 '18 at 18:17
trying to preserve the functional dependency that droids of the same dtype->same mainskill therefore using a before insert trigger @âńōŋŷXmoůŜ — rahul chawla, Mar 27 '18 at 18:18
this trigger will not avoid an insert. If you raise an error then the insert will be stopped. — jose_bacoy, Mar 27 '18 at 18:20
how would I do that? and also the trigger itself doesn't even execute due to syntax errors @âńōŋŷXmoůŜ — rahul chawla, Mar 27 '18 at 18:20
You can't reference your aliased columns in your `WHERE` like that (`t` `m`). Also remove that `END IF;` — Aaron Dietz, Mar 27 '18 at 18:24
Pretty sure you're after this: https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342 — Aaron Dietz, Mar 27 '18 at 18:26
@AaronDietz I have made the changes as per the link and it still doesn't execute- syntax error — rahul chawla, Mar 27 '18 at 18:31

jose_bacoy · Accepted Answer · 2018-03-27T18:43:59.150

0

This will raise an error if the same dtype and a different mainskill exists.

create trigger foo before insert on Droid
 for each row
 begin

 declare raise_err_msg varchar(128);

 SET @recCount = (Select count(*) from Droid where dtype=NEW.dtype AND mainskill != NEW.mainskill);
 If @recCount > 0 THEN
     set raise_err_msg = concat('Error: <put error msg here> ', cast(dtype as char));
     signal sqlstate '45000' set message_text = raise_err_msg;
 END IF;

end;$$

edited Mar 27 '18 at 18:43

answered Mar 27 '18 at 18:33

jose_bacoy

12,227
1
20
38

"This will raise an error if the same dtype and mainskill exists" I think you misunderstood, if there already exists a dtype with a different mainskill in the table IT SHOULD RAISE AN ERROR if and only if you try to insert a droid with the same dtype BUT a different skill than the one currently in the table – rahul chawla Mar 27 '18 at 18:37
this is giving me an error in sql fiddle check my fiddle http://sqlfiddle.com/#!9/dfd77a/1 – rahul chawla Mar 27 '18 at 18:39
I'm using MySQL – rahul chawla Mar 27 '18 at 18:43
I updated my answer to put mainskill != NEW.mainskill. You cannot use sqlfiddle for triggers. Sqlfiddle is only for select queries. – jose_bacoy Mar 27 '18 at 18:44
I have tried the same query in my putty client and I have posted the error i'm getting in my post above if you could check it out Id appreciate it – rahul chawla Mar 27 '18 at 18:49
this is the first error thrown ERROR 1054 (42S22): Unknown column 'NEW.dtype' in 'where clause' , the schema for Droid is (cname,dtype,mainskill) so I'm very confused @âńōŋŷXmoůŜ – rahul chawla Mar 27 '18 at 18:56
I'm also getting an error msg : declare raise_err CHAR(30); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 – rahul chawla Mar 27 '18 at 19:36
`EXISTS` would be more efficient than `COUNTing`. – Rick James Apr 10 '18 at 00:47

Trigger Error MySQL

1 Answers1