Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
1  
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
add comment

2 Answers

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
add comment

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

share|improve this answer
1  
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. –  dezso Sep 10 '13 at 5:53
add comment

Your Answer

 
discard

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.