Mysql – Creating MySQL Trigger

MySQLtrigger

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

Best Answer

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 ;

Related Solutions

Mysql – What’s wrong with this MySQL query

You are using the wrong quotes. Use ` instead of '

CREATE TABLE `topics` (
  `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `creator` varchar(255) NOT NULL,
  `createtime` datetime NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB;

I also would recommend specifying the engine.

MYSQL Trigger not working

The first problem is that you don't have an END IF;.

The next problem (assuming that you aren't doing this and just didn't include it in the question) is that when you have a compound statement, you have to change the statement delimiter from ; to something else so that the client will send the entire CREATE TRIGGER statement to the server as a single statement.

Finally, the column "first_name" probably needs to be qualified with NEW (assume you're referencing a column in wp_usermeta called "first_name".

DELIMITER $$
DROP TRIGGER IF EXISTS update_customers $$
CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
FOR EACH ROW 
BEGIN
  IF NEW.wp_metakey = NEW.first_name THEN 
    INSERT INTO test_customer(first_name) values (NEW.wp_meta_value);
  END IF;
END $$
DELIMITER ;

Best Answer

Related Solutions

Mysql – What’s wrong with this MySQL query

MYSQL Trigger not working

Related Question