MySQL Forums
Forum List  »  MySQL Workbench

TRIGGER not working
Posted by: Marco Lucibelli
Date: December 06, 2018 06:19PM

Hi guys,
I'm studying the triggers recently and I can not immediately understand this:

QUERY: insert the screening of the film only if the year of production of the film is smaller than the year inserted in the projection.

USE cin_film; // database

drop trigger if exists vincoloPro;

DELIMITER $$

CREATE TRIGGER vincoloPro

before INSERT ON projection

for each row

begin

DECLARE C INTEGER;

SET @c = (SELECT year from film where id_film = new.id_film);
// insert in c the year corresponding to the film inserted in the tuple
IF (CAST (YEAR (NEW.giorno) AS UNSIGNED) <c) THEN
// convert to integer the year entered on projection day
SIGNAL sqlstate '01000' SET MESSAGE_TEXT = 'You have missed the year';

ELSE

SIGNAL sqlstate '01000' SET MESSAGE_TEXT = 'ADDIOS!';
END IF;
END;

$$


DELIMITER;


But despite the year of production of the film is greater than the year of screening the film, it makes me enter the new tuple quietly:
for example:
MOVIE:
+ --------- + ------------ + -------------------------- -------- + ------------ + ------ +
| id_film | id_regista | title | gender | year |
+ --------- + ------------ + -------------------------- -------- + ------------ + ------ +
| 1 | 15 | Crash | Drama | 1996 |
| 2 | 15 | False Sembianze | Comedy | 1988 |
| 3 | 14 | Pulp Fiction | Police | 1994 |
| 4 | 13 | Breaking the waves | Drama | 1996 |
| 5 | 13 | Dogville | Drama | 2002 |
| 6 | 12 | Alamo | Western | 1960 |
| 7 | 18 | Dangerously in three | Spying | 1985 |
| 8 | 19 | White horse, black heart | Drama | 1989 |
| 9 | 19 | In the garden of good and evil | Police | NULL |
| 10 | 21 | American Beauty | Drama | 1999 |
| 11 | NULL | The exchange | NULL | 2008 |
+ --------- + ------------ + -------------------------- -------- + ------------ + ------ +

mysql> INSERT INTO projection (id_cinema, id_film, day)
-> VALUES ('1', '1', '1877-04-03');
Query OK, 1 row affected (0.08 sec)

(Despite id_film 1 -> Crash is from 1996> 1877)

Options: ReplyQuote


Subject
Views
Written By
Posted
TRIGGER not working
27
December 06, 2018 06:19PM
10
December 06, 2018 10:25PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.