Creating MySQL Trigger

Question

I have faced syntax error during creating MySQL Trigger.

Create Trigger Count_McNum
before insert on tension
for each row
declare rowcount int ;
set rowcount = (select count(McNum) from tension where median is null) ;
if (rowcount >1000) 
then 
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning:  rowcount> 1000!';
    END IF;
End

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 'declare rowcount int' at line 4

Answer 1

You're missing the small keyword BEGIN. And as you have multiple statements, you have to change the delimiter.

DELIMITER $$
Create Trigger Count_McNum
before insert on tension
for each row
BEGIN
declare rowcount int ;
set rowcount = (select count(McNum) from tension where median is null) ;
if (rowcount >1000) 
then 
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning:  rowcount> 1000!';
    END IF;
End$$
DELIMITER ;