Trigger issue with syntax

Question

Hi to all im trying to create a trigger for this table:

create table Episode(
  title varchar(25) not null,
number int not null,
  length time not null,
  aired date not null,
  serie_name varchar(25),
PRIMARY KEY(title,number),
  FOREIGN KEY (serie_name)REFERENCES Serie(name)
  ) ENGINE=InnoDB;

this is a table of a db that saves some tv series...so the trigger have to check if i m trying to insert a new episode that had benn aired before the precedent one....But i have some problem any solutions? i tried this:

create trigger ControlDataEp
 before insert on Episode
 for each row
begin
if new.aired<(select aired from Episode where title=new.title and number=new.number-1)
    then raise.application_error(-21,'error');
end if;

end;

score 0 · Answer 1 · answered Jun 05 '13 at 03:12

If I were you, I wouldn't use a trigger when there's no need to.

You can use

Please have a look in the respective manual pages I linked for additional information.

To explain, why your solution doesn't work:

if new.aired<(select aired

Your subquery might return multiple rows. Use SELECT MAX(aired) ... instead.

... and number=new.number-1)

It's a bad idea to rely on code outside the database to make sure, that data in the database is okay.

then raise.application_error(-21,'error');

raise.application_error() is not a MySQL built-in function. You can't call functions from your code in MySQL like this. If you really want to raise an error in MySQL use SIGNALs

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

If you don't that much care about returning meaningful error message you can simplify your trigger to one statement

CREATE TRIGGER ControlDataEp
BEFORE INSERT ON Episode
FOR EACH ROW
SET NEW.aired = 
(
  SELECT IF(IFNULL(MAX(aired), 0) < NEW.aired, NEW.aired, NULL)
    FROM Episode 
   WHERE title = NEW.title 
     AND number = NEW.number - 1
);

What it does it violates NOT NULL constraint on aired column.

Here is SQLFiddle demo. Uncomment last insert statement

Now if you do need to return a custom error message:

You can use SIGNAL but only if you on MySql 5.5 and higher
Use one the hackish ways (read e.g. TRIGGERs that cause INSERTs to fail? Possible?, Throw an error in a MySQL trigger)

edited May 23 '17 at 11:49

Community

1
1

answered Jun 05 '13 at 03:35

peterm

91,357
15
148
157

Ah ok it s awesome but is the first time that i see a query like this.. after the – Skyzzo Jun 05 '13 at 08:45
@Skyzzo **[`NEW`](http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html)** is an alias that allows you to refer to the columns of a row that is being inserted (or updated). `NEW.aired` and `NULL` is the 2nd and 3rd argument of **[`IF()`](http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html#function_if)** function. It allows to check for condition and if it's true return the value that is being inserted (NEW.aired) and it's false return `NULL` thus violating the constraint. – peterm Jun 05 '13 at 08:58

Trigger issue with syntax

2 Answers2