Declaring and using variables in triggers

Question

I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem.

DELIMITER $$
USE `pucko`$$
CREATE TRIGGER `after_delete_Alien`
AFTER DELETE ON `Alien` FOR EACH ROW BEGIN 
 DECLARE vapenid INT;
 DECLARE alien_count INT;
 DECLARE rymdskepp_count INT;
 SET vapenid = (SELECT vapen FROM `Alien_Använder_Vapen` WHERE idAlien = OLD.idAlien);
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = vapenid; // Debug, this shows that vapenid is null?
 DELETE FROM `Alien_Använder_Vapen` WHERE idAlien = OLD.idAlien;
 SET alien_count = (SELECT count(*) FROM `Alien_Använder_Vapen` WHERE vapen = vapenid);
 SET rymdskepp_count = (SELECT count(*) FROM `Rymdskepp_Använder_Vapen` WHERE vapen = vapenid);
 if(alien_count < 1 && rymdskepp_count < 1) THEN
 DELETE FROM `vapen` WHERE idVapen = vapenid;
 END IF;
END$$

The problem I am having is when vapenid is/should be set. I know that there is an alien with id "abc". I know that there is a vapen with id 1. In Alien_Använder_Vapen there is a row with alienId abc and vapen as 1. I use the SIGNAL to debug a bit what the value of vapenid is, and mySQL returns

delete from Alien where idAlien = "abc" Error Code: 1231. Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'

This makes me think that NULL is returned from the query in vapenid, but that doesn't make sense, as SELECT vapen FROMAlien_Använder_VapenWHERE idAlien = 'abc' in a normal query returns 1 correctly.

I've been trying to figure this out for quite some time now but I just can't see what I'm doing wrong. Is my syntax or logic wrong/weird?

• Mr.Brownstone: Do you have a foreign key on that column referencing the Alien.idAlien column and set to ON DELETE CASCADE?
• galaxyAbstractor: @Mr.Brownstone D'oh I thought I removed that, thank you, it all works now :D. Stupid mistakes you make when you try for too long...

Answer 1

As per my comment above, the chances are that you have a foreign key that is set to delete on cascade. This is what is causing your lookup to fail.

I hope this helps you.

Answer 2

Put it like this:

declare int_value int default 1;
DELIMITER $$
CREATE TRIGGER call_trigger
 AFTER INSERT ON ticket
 FOR EACH ROW
BEGIN
 declare v_index int default 1;
 while v_index <= 15 do
 **YOUR QUERY**
 set v_index := v_index + 1;
 end while;
END
$$
DELIMITER;

Source: http://findoutanswer.com/tag/mysql

• András Váczi: Welcome to the site! Please have a look at the help about formatting posts properly: dba.stackexchange.com/editing-help Also you should include some explanation why and how this will help with the original problem. Thanks.