vendredi 23 octobre 2015

Trigger with IF condition in PHPmyadmin

I have a problem with a trigger on my database (I'm using phpmyadmin). When I Insert a new row in my table 'customer'.

+-------------+------+------------+--------------+  
| id_customer | name |  group     |  subscribed  |  
+-------------+------+------------+--------------+  
|    1        | John | Business   |    true      |  
|    2        | Rose | Particular |    true      |    
|    3        | Ann  | Business   |    false     |    
+-------------+------+------------+--------------+  

I want to add a new row in my table 'groups_customer'

+----------+-------------+  
| id_group | id_customer |  
+----------+-------------+  
|   3      |     1       |  
|   4      |     2       |  
+----------+-------------+

So in case I Insert a new customer that is subscribed and with the group 'Business' it will add a line in 'groups_customer' with the id_group=3
In case it's a new subscribed and 'Particular' customer, it will add id_group=4
In any other case, it will not add any rows on 'groups_customer'

So this is my trigger:

CREATE TRIGGER register_client_group
AFTER INSERT
ON customer
FOR EACH ROW
BEGIN
IF (NEW.group='Business' AND NEW.subscribed=true)  
THEN
INSERT INTO groups_customer (id_group, id_customer) VALUES (3, NEW.id_customer);  
ELSEIF (NEW.group='Particular' AND NEW.subscribed=true)  
THEN
INSERT INTO groups_customer (id_group, id_customer) VALUES (4, NEW.id_customer);
END IF
END;

The MySQL said:

"#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 '' at line 8 "

Aucun commentaire:

Enregistrer un commentaire