Mysql Error 1064 Create Trigger
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL - Create Trigger Error 1064 (near 'DELIMITER ; ') up vote 0 down vote favorite 1 Hope everyone is well. I am trying to create a mysql trigger however i keep getting the following error: [Err] 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 'DELIMITER' at line 1 [Err] DELIMITER ; The code I have is as follows (please note, the actual file is 130 lines long I have just included the part where the problem is, or at least i believe it to be). /* BB Events Insert After Trigger */ DELIMITER $$ CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events` FOR EACH ROW BEGIN UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`; IF NEW.`status_code` = 62465 THEN INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`); UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1) WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`; END IF; IF NEW.`status_code` = 62467 THEN UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id`
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like http://stackoverflow.com/questions/23082874/mysql-create-trigger-error-1064-near-delimiter you, helping each other. Join them; it only takes a minute: Sign up Mysql error #1064 when trying to create trigger up vote 0 down vote favorite I am trying to create a MySQL trigger but don't know why error #1064 occurs. Here is the full error message: #1064 - You have an error in your SQL http://stackoverflow.com/questions/30052332/mysql-error-1064-when-trying-to-create-trigger syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE popularity_cur CURSOR FOR select (sum(tbl_reviews.vote_value)/count(tbl_' at line 22 Line #22 is close cur; and here is what I've done so far: delimiter // CREATE TRIGGER business_popularity_trigger_after_review_insert AFTER INSERT ON tbl_reviews FOR EACH ROW BEGIN DECLARE data_finished INTEGER DEFAULT 0; DECLARE businessId, reviewId, dealId, businessServiceId INT DEFAULT 0; DECLARE businessPopularity INTEGER DEFAULT 0; DECLARE popularity_finished INTEGER DEFAULT 0; DECLARE cur CURSOR FOR select tbl_reviews.review_id, tbl_reviews.deal_id, deals.deal_id, deals.business_service_id, business_services.business_service_id, business_services.business_id from tbl_reviews INNER JOIN deals ON deals.deal_id = new.deal_id INNER JOIN business_services ON business_services.business_service_id = deals.business_service_id where tbl_reviews.review_id = new.review_id group by business_services.business_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET data_finished = 1; OPEN cur; get_data: LOOP fetch cur into reviewId, dealId, businessServiceId, businessId; IF data_finished = 1 THEN LEAVE get_data; END IF; END LOOP get_data; close cur; DECLARE popularity_cur CURSOR FOR select (sum(tbl_reviews.vote_value)/count(tbl_reviews.vote_value)) from tbl_reviews INNER JOIN business_services ON business_services.business_id = businessId INNER JOIN deals ON deals.business_service_id = business_services.business_service_id where deals.deal_is_deleted
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the http://dba.stackexchange.com/questions/72507/mysql-syntax-error-when-creating-a-trigger workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions http://forums.mysql.com/read.php?99,499237,499237 Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from mysql error others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top MySQL Syntax Error When Creating a Trigger up vote 0 down vote favorite I am trying to create a trigger but I am getting mysql error 1064 a syntax error and I am not really sure why I am. CREATE TRIGGER Section_Insert AFTER INSERT ON Section -> FOR EACH ROW BEGIN -> INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 mysql trigger errors syntax share|improve this question asked Jul 27 '14 at 19:11 LearningMySQL 12 You need a delimiter and you are missing END –Mihai Jul 27 '14 at 19:16 Thank you that fixed it. –LearningMySQL Jul 27 '14 at 20:27 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote You need to use the Delimiter /// first if you are creating a trigger. Delimiter /// CREATE TRIGGER Section_Insert AFTER INSERT ON Section FOR EACH ROW BEGIN INSERT INTO Audit(changeTime, tableName, Action) VALUES (NOW(), 'Section', 'INSERT'); End; /// share|improve this answer answered Jul 28 '14 at 4:56 Karthick 721419 add a comment
Community Podcasts MySQL.com Downloads Documentation Section Menu: MySQL Forums :: Triggers :: Error 1064 (42000) when creating trigger New Topic Advanced Search Error 1064 (42000) when creating trigger Posted by: Stephanie Worden () Date: November 16, 2011 10:09AM I am working from a book not MySQL specific, but MySQL 5.5 seems to support triggers fairly well so I'm not sure why the code isn't working. I cannot see any reserved words being used. CREATE TRIGGER INSERT_LOG AFTER INSERT ON ARTISTS REFERENCING NEW ROW AS New FOR EACH ROW BEGIN ATOMIC INSERT INTO ARTIST_LOG (ACTION_TYPE, ARTIST_ID) VALUES ('INSERT', New.ARTIST_ID); END; The error 1064 (42000) message says the error is occurring near "REFERENCING NEW ROW AS New" line, but I don't see why. Navigate:Previous Message•Next Message Options:Reply•Quote Subject Views Written By Posted Error 1064 (42000) when creating trigger 2643 Stephanie Worden 11/16/2011 10:09AM Re: Error 1064 (42000) when creating trigger 1119 Peter Brawley 11/16/2011 10:26AM Re: Error 1064 (42000) when creating trigger 978 Stephanie Worden 11/16/2011 11:10AM Sorry, you can't reply to this topic. It has been closed. powered by phorum Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party. Contact Sales USA: +1-866-221-0634 Canada: +1-866-221-0634 Germany: +49 89 143 01280 France: +33 1 57 60 83 57 Italy: +39 02 249 59 120 UK: +44 207 553 8447 Japan: 0120-065556 China: 10800-811-0823 India: 0008001005870 More Countries» Contact Us Online» Products MySQL Enterprise Edition MySQL Standard Edition MySQL Classic Edition MySQL Cluster CGE MySQL Embedded (OEM/ISV) Services Training Certification Consulting Support Downloads MySQL Community Server MySQL Cluster MySQL Fabric MySQL Utilities MySQL Workbench About MySQL Contact Us How to Buy Partners Job Opportunities Site Map Documentation MySQL Reference Manuals MySQL Workbench Expert Guides Topic Guides MySQL Cluster Legal Legal Policies Your Privacy Rights Terms of Use Trademark Policy Contributor Agreement © 2015, Oracle Corporation and/or its affiliates