Right now it is giving me a 1064 error at the elseif
. This is my first time creating a trigger in 5.1. I am not sure the version i used before, but I was successful using that one.
DROP TRIGGER IF EXISTS greentrucks.iCustomer;
DELIMITER GO
CREATE TRIGGER greentrucks.iCustomer AFTER INSERT
ON customer
For each ROW BEGIN
DECLARE count int;
SET @count = FOUND_ROWS();
If @count = 0
THEN LEAVE
ELSEIF EXISTS ( SELECT *
FROM INSERTED i
WHERE EXISTS (
SELECT p.email
FROM greentrucks.customer p
WHERE i.email = p.email))
THEN BEGIN
RAISERROR('That Email is already in use!',16,1);
IF @@Trancount >0
ROlLBACK TRANSACTION;
END IF
END IF
END
GO
DELIMITER ;
LEAVE
needs a label. See the LEAVE documentation and the sample in the LOOP documentation. Also, you need to terminate your LEAVE
statement with a semicolon.
MySQL doesn't appear to have any official examples for LEAVE
within a trigger. This blog post puts the label right before the BEGIN
. Based on that, try something like this:
DROP TRIGGER IF EXISTS greentrucks.iCustomer;
DELIMITER GO
CREATE TRIGGER greentrucks.iCustomer AFTER INSERT
ON customer
For each ROW
my_label: BEGIN
DECLARE count int;
SET @count = FOUND_ROWS();
If @count = 0
THEN LEAVE my_label;
... and the rest of your code