CODEWITHSUNDEEP
mysqlsqldatabasetriggers
Robert Ross
Robert Ross

Reputation: 1189

Creating a trigger which performs an update on another table

I have two tables - one is called bookloan and the other bookcopy. In the bookloan table I have datetime field called dateReturned, which is null by default and is filled only when a book is returned. In the other table (bookcopy) I have a field is available, which value is changed to 0 when a book is taken. Now I want to change it back to 1 when a book is returned (when my datetime field is no longer null and was updated).

My problem is that my code for this purpose (posted below) doesn't work for some reason.

P.S I don't get any errors.

USE `libdb`;

DELIMITER $$

DROP TRIGGER IF EXISTS libdb.bookloan_BEFORE_UPDATE$$
USE `libdb`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `libdb`.`bookloan_BEFORE_UPDATE`
 BEFORE UPDATE ON `bookloan` 
 FOR EACH ROW



BEGIN 

IF NEW.dateReturned <=> OLD.dateReturned THEN
UPDATE bookcopy 
SET  isAvailable = 1
WHERE bookcopy.idBookCopy = NEW.BookCopy_idBookCopyFK;
END IF;



END$$
DELIMITER ;

Upvotes: 1

Views: 39

Answers (1)

Mureinik
Mureinik

Reputation: 311163

The <=> operator is a null safe equals operator, although according to the description you need to "not equals" operator. Here, specifically, if you only want to check if it turns from null to a non-null value (or vise versa), you can use the logical xor operator:

IF (NEW.dateReturned IS NULL) XOR (OLD.dateReturned IS NULL) THEN

Upvotes: 1

Related Questions