Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


USE `pucko`$$

CREATE TRIGGER `after_delete_Alien`
    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;

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?

share|improve this question
Do you have a foreign key on that column referencing the Alien.idAlien column and set to ON DELETE CASCADE? – Mr.Brownstone Sep 12 '12 at 22:13
@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... – galaxyAbstractor Sep 12 '12 at 23:07
up vote 2 down vote accepted

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.

share|improve this answer

Put it like this:

declare int_value int default 1;
CREATE TRIGGER call_trigger
 declare v_index int default 1;
 while v_index <= 15 do
   set v_index := v_index + 1;
 end while;


share|improve this answer
Welcome to the site! Please have a look at the help about formatting posts properly: Also you should include some explanation why and how this will help with the original problem. Thanks. – dezso Sep 10 '13 at 5:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.