CODEWITHSUNDEEP
mysqlsqltriggers
Humanbeings
Humanbeings

Reputation: 109

I can not get run trigger on mysql

I have these tables on mySQL (I write all on command line)

TITOLO(isin varchar(12), maxAss int, minAss int, primary key(isin))

QUOTAZIONE(id int, min int, max int, date date, isin varchar(12), primary key(id), foreign key(isin) references TITOLO(isin))

Now i want to create a trigger that every time i insert a row in QUOTAZIONE it check if min < minASS or max > maxAss if it is true update the value on the table TITOLO.

DELIMITER $$
CREATE TRIGGER AggiornaTitolo
AFTER INSERT ON quotazione
FOR EACH ROW BEGIN
DECLARE massimo int;
DECLARE minimo int;
SELECT maxAss, minAss INTO massimo, minimo FROM titolo WHERE titolo.isin = NEW.isin;
IF massimo > maxAss THEN
UPDATE titolo
SET maxAss = massimo;
IF minimo < minAss THEN
UPDATE titolo
SET minAss = minimo; 
END IF;
END;
$$ DELIMITER ;

but the console write error 1064 (42000) at line 14. Can someone please help me?

Upvotes: 1

Views: 32

Answers (1)

PM 77-1
PM 77-1

Reputation: 13344

As McAdams331 already pointed out you're missing the second ENDIF:

00000001 DELIMITER $$
00000002 CREATE TRIGGER AggiornaTitolo
00000003 AFTER INSERT ON quotazione
00000004 FOR EACH ROW BEGIN
00000005    DECLARE massimo int;
00000006    DECLARE minimo int;
00000007    SELECT maxAss, minAss INTO massimo, minimo FROM titolo WHERE titolo.isin = NEW.isin;
00000008    IF massimo > maxAss THEN
00000009        UPDATE titolo
00000010        SET maxAss = massimo;
00000011        IF minimo < minAss THEN
00000012            UPDATE titolo
00000013            SET minAss = minimo; 
00000014        END IF;
<missing>   END IF;          
00000015 END;
00000016 $$ DELIMITER ;

Upvotes: 1

Related Questions