5

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?

2
  • 1
    Do you have a foreign key on that column referencing the Alien.idAlien column and set to ON DELETE CASCADE? Sep 12, 2012 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... Sep 12, 2012 at 23:07

2 Answers 2

2

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.

0

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

1
  • 2
    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, 2013 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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