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 FROM
Alien_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?
Best Answer
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.