CODEWITHSUNDEEP
mysqlsqltriggers
Nunzio Meli
Nunzio Meli

Reputation: 89

MySql - Trigger not work - Abort insert

I had write this trigger but it not work, i recive this error:

11:24:11 CREATE TRIGGER check_venduti AFTER INSERT ON venduti FOR EACH ROW BEGIN SELECT count(*) AS num_rows FROM prodotto WHERE cod_prodotto=NEW.cod_prodotto if numrows < 0 then SIGNAL "error" end Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if numrows < 0 then SIGNAL "error" end' at line 6 0.000 sec

These are my tables:

CREATE TABLE clienti (cod_cliente integer(4) auto_increment,nome varchar(100),cognome varchar(100),primary key(cod_cliente))Engine=InnoDB;
CREATE TABLE prodotto(cod_prodotto integer(4) auto_increment,descrizione varchar(100),qnt integer(4),primary key(cod_prodotto))Engine=InnoDB;
CREATE TABLE venduti (cod_vendita integer(4)  auto_increment,cod_cliente integer(4) REFERENCES clienti(cod_cliente),cod_prodotto integer(4)

REFERENCES prodotto(cod_prodotto),primary key(cod_vendita))Engine=InnoDB;

and this is my trigger: 

delimiter |
CREATE TRIGGER check_venduti
 BEFORE INSERT ON venduti
  FOR EACH ROW
   BEGIN
    SELECT count(*) AS num_rows FROM prodotto WHERE cod_prodotto=NEW.cod_prodotto
    if num_rows == 0 then
     SIGNAL "error"
    end;
   END;
|

now, following your answers i edited my code in this mode:

delimiter |
CREATE TRIGGER check_venduti
 BEFORE INSERT ON venduti
  FOR EACH ROW
   BEGIN
    DECLARE num_rows INT;
    SELECT count(*) INTO num_rows FROM prodotto WHERE cod_prodotto=NEW.cod_prodotto;
     if num_rows==0 then
      SIGNAL "error"
     end;
   END;
|

but I receive the same error:

Upvotes: 0

Views: 298

Answers (1)

eggyal
eggyal

Reputation: 125865

The syntax error arises because you have not terminated the SELECT statement.

Even if you do terminate the SELECT statement, no num_rows variable will be defined in the IF statement.

You can store the result of the SELECT statement in a variable for subsequent testing:

BEGIN
  DECLARE num_rows INT;
  SELECT COUNT(*) INTO num_rows ... ;
  IF num_rows = 0 THEN
    ...

However, it appears that what you're really trying to do is enforce a foreign key constraint on venduti.cod_prodotto:

ALTER TABLE venduti
  ADD FOREIGN KEY (cod_prodotto) REFERENCES prodotto (cod_prodotto);

Upvotes: 1

Related Questions