MySQL Trigger – Condition Not Working

MySQLtrigger

I am developing a social networking website, where I have 3 tables – 1st for users(userid- pk,auto increment,username,etc.), 2nd for Album(albumid – auto increment,userid – of person who owns the album) and 3rd for photo(photoid – auto increment,albumid -where photo belongs to,path- to photo in disk). Now, Before inserting into photo, my trigger checks if the user owns the album given by album id. The trigger is created using MySql workbench which gives the following:

USE `mydb`;
DELIMITER $$
CREATE TRIGGER `photo_BINS` BEFORE INSERT ON `photo` FOR EACH ROW
BEGIN
    DECLARE found_it INT;

    SELECT 1 INTO found_it FROM album WHERE albumid= NEW.albumid and userid=@userid;
    IF found_it!=1 then
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User does not own album';
END IF;
END

I am passing the value to @userid by php as follows:-

$con=NULL;$stmt=NULL;
$con=mysqli_connect('localhost','username','pswd','mydb');
$stmt= mysqli_prepare($con,'set @userid=?');
mysqli_stmt_bind_param($stmt,'i',$user);
mysqli_stmt_execute($stmt);

$stmt= mysqli_prepare($con,'insert into photo(albumid,path) values(?,?)');
mysqli_stmt_bind_param($stmt,'is',$albumid,$path);
mysqli_stmt_execute($stmt)'
$pid=mysqli_insert_id($con);

The problem is that everytime the value gets entered. Even when I oppose the condtion i.e. fount_it=1 , the insert always takes place. Other variant I've tried is:

if(SELECT 1 FROM album WHERE albumid= NEW.albumid and userid=@userid)!=1 THEN

… still the insert always takes place. Please help.

Best Answer

Found_it is null when declared. If no row matches the query that looks for album and userid found_it is not updated, i.e. it is still null. The test 

found_it!=1 (use found_it <> 1 instead)

therefor evaluates to NULL ( NULL <> 1 => NULL ). You can prevent this by a default constraint in your declaration:

DECLARE found_it INT DEFAULT 0;

Now the test will evaluate to FALSE.

Related Question