MySQL Trigger ERROR in phpAdmin

Hi Is there any error in this TRIGGER Statement.When ever i try to run this in phpAdmin its giving error saying "#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 "SELECT Count(*) into SIM_CCode_Count".I cant get what's wrong in this..please help me

This is my trigger statement

CREATE TRIGGER Is_CountryCode_There After INSERT on mr_details FOR EACH ROW BEGIN DECLARE SIM_CCode_Count INTEGER; DECLARE NET_CCode_Count INTEGER; SELECT Count(*) into SIM_CCode_Count FROM Country_Main where CountryCode=NEW.SimCntISO; IF SIM_CCode_Count=0 THEN INSERT INTO Country_Main(CountryCode,CountryName) Values(NEW.SIMCntISO,"Unknown"); END IF If NEW.SimCntISO<>NEW.NetCntISO then SELECT Count(*) into NET_CCode_Count FROM Country_Main where CountryCode=NEW.NetCntISO IF NET_CCode_Count=0 THEN INSERT INTO Country_Main(CountryCode,CountryName) Values(NEW.NETCntISO,"Unknown"); END IF END IF END

--------------Solutions-------------

Without proper explanation about your requirement and about tables and what you are expecting this trigger to do,its very difficult to say if any issues there in your trigger.. But as far as i can see there is some minor correction need to be done.. Try this Code and let know in detail your requirements..

CREATE TRIGGER Is_CountryCode_There After INSERT on mr_details FOR EACH ROW
BEGIN
DECLARE SIM_CCode_Count INTEGER;
DECLARE NET_CCode_Count INTEGER;

SELECT Count(*) into SIM_CCode_Count FROM Country_Main where CountryCode=NEW.SimCntISO;

IF SIM_CCode_Count=0 THEN
INSERT INTO Country_Main(CountryCode,CountryName) Values(NEW.SIMCntISO,"Unknown");
END IF;

If (NEW.SimCntISO<>NEW.NetCntISO) then
SELECT Count(*) into NET_CCode_Count FROM Country_Main
where CountryCode=NEW.NetCntISO;

IF NET_CCode_Count=0 THEN
INSERT INTO Country_Main(CountryCode,CountryName) Values(NEW.NETCntISO,"Unknown");
END IF;

End IF;
END;

You have to declare a mysql-statement delimiter before the trigger statement:

DELIMITER |

CREATE TRIGGER ...
(your code)
END|

DELIMITER ;

Otherwise MySQL interprets your ; in this statement as statement commit and executes the code immidiately. With the delimiter changed to a different character you can use the semicolon inside the trigger declaration safely.

See here: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Category:mysql Time:2011-03-07 Views:0

Related post

  • MySQL MAX_JOIN_SIZE Error! Need to optimize query 2009-10-14

    I run this query: SELECT u.user_id, u.fname, u.lname, n.title, n.news_id, n.post, n.zip, z.city,z.state_abbr FROM yc_users u, yc_news n, yc_zipcodes z WHERE u.user_id = n.user_id AND n.zip = z.zip ORDER BY n.stamp LIMIT 10 And get this error: The SEL

  • Can't create MySQL trigger with TRIGGER privilege on 5.1.32 2009-10-26

    My fellow developers and I have our own development schemas on a shared MySQL development database. My assignment requires me to create triggers in my schema, yet I've been unsuccessful so far. CREATE TRIGGER myTrigger AFTER DELETE on myTable FOR EAC

  • MySQL Trigger programatically in C# 2009-11-02

    I have problem with creating a simple MySQL trigger in C#. I'm using StringBuilder to build the following command string: DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ dbname.table$$ CREATE TRIGGER dbname.inserttrigger AFTER INSERT ON dbname.table

  • How to abort INSERT operation in MySql trigger? 2010-03-29

    I have a table containing an url and a string representing its parameters. The problem is I want an url and a parameterstring to be the unique constraint for the table - aka no entries can have the same url AND parameter string. The parameter string

  • Creating a MySQL trigger to verify data on another table 2010-05-13

    I am trying to set up a MySQL trigger that does the following: When someone inserts data into databaseA.bills, it verifies if databaseB.bills already has that row, if it doesn't, then it does an additional insert into databaseB.bills. Here is what I

  • MySQL Trigger - update table with value selected from another table 2010-09-01

    I'm having problems updating row in a table with value selected from another table in MySQL Trigger. My Trigger looks like this CREATE TRIGGER update_user_last_login AFTER INSERT or UPDATE ON last FOR EACH ROW BEGIN DECLARE _user_id INTEGER; SELECT u

  • IF in MySQL trigger 2011-01-28

    Trying to create MySql trigger CREATE TRIGGER updVisible AFTER UPDATE ON photos FOR EACH ROW BEGIN IF NEW.Status = 2 THEN UPDATE otherTable SET IsVisible=0 WHERE PID=NEW.PID END IF; END; But I got error: You have an error in your SQL syntax; check th

  • Is possible to do a ROLLBACK in a MySQL trigger? 2011-07-09

    Just that is the question: is possible to do a ROLLBACK in a MySQL trigger? If answer is yes, then, please, explain how. --------------Solutions------------- If the trigger raises an exception, that will abort the transaction, effectively rolling bac

  • How to fix this IF-ELSE-THEN statement in a MySQL trigger 2011-12-06

    Possible Duplicate: How to fix this MySQL trigger? CREATE TRIGGER Transaction_insert BEFORE INSERT ON Transaction FOR EACH ROW IF Account.CreditBalance + NEW.Amount < Account.CreditLimit WHERE Number = NEW.AccountNumber THEN UPDATE Account SET Cre

  • Recursive MySql Trigger not working 2012-02-02

    I am trying to setup a MySql trigger to run after data is inserted/updated in ODBC My trigger is below: CREATE TRIGGER `myTrigger` AFTER INSERT ON `testTable` FOR EACH ROW UPDATE `testTable` SET `Field One` = CONCAT(`Field One`, ' - Trigger'); My tri

  • MySQL trigger + comparing 2 dates 2012-05-02

    I have a question about some MySQL trigger syntax I am kind of new to using DATE in triggers and am getting an error in this code /* rentalTotal Description: Trigger that keeps track of the rental income */ DROP TRIGGER IF EXISTS rentTotal; DELIMITER

  • Is it true I can't edit a MySQL trigger, I have to drop it and create a new one? 2009-04-04

    Is it true I can't edit a MySQL trigger, I have to drop it and create a new one? Also, being a relative newcomer to triggers, it feels like they seem liable to causing 'erroneous' data. For example I might want a trigger to be fired (inserting data i

  • MySQL trigger/procedure execution delay 2009-06-04

    Is there a decent way to delay execution of mysql trigger? WHILE @condition = 0 sleep for awhile insert into some_table values(NEW.value1, NEW.value2); --------------Solutions------------- Since MySQL 5.0.12, you can do this: SELECT SLEEP(<seconds

  • Insert user IP Address into MYSQL Trigger 2009-08-04

    I'm creating a MYSQL Trigger. I'd like the trigger to record the ip address of the user who initiated the database change. Is this possible? And if so, how can MySQL get the user's IP Addy? --------------Solutions------------- You cannot get the IP a

  • mysql delimiter error 2009-08-12

    Modifed. DROP FUNCTION IF EXISTS PersonName; DELIMITER |; CREATE FUNCTION PersonName( personID SMALLINT ) RETURNS CHAR(20) BEGIN DECLARE pname CHAR(20) DEFAULT ''; SELECT name INTO pname FROM family WHERE ID=personID; RETURN pname; END; | DELIMITER ;

  • Transaction ID in MySQL Trigger 2009-08-14

    Is there a way to retrieve a transaction ID inside a MySQL trigger? --------------Solutions------------- Currently, no. It's been an open request for four years. However, you can use connection_id() which will give the connection session, so you can

  • Mysql trigger/events vs Cronjob 2009-11-14

    I have an auction website which let my users place an unlimited amount of autobiddings. To monitor these autobiddings something has to check the database every second. My question is if it is better to use mysql trigger events or to user a cronjob ev

  • PHP-MySQL connection error 2009-12-29

    I am trying to connect to MySQL via PHP through the following code <?php $dbhost = 'localhost'; $dbuser = 'root'; $pass = 'pass' $con = mysql_connect($dbhost,$dbuser,$pass); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_d

  • How do I exit a series of If / else conditions in a mysql trigger? 2010-06-16

    I have a trigger that checks to see if certain fields changed during the update. If any of these fields changed I update another table. I'd like to "break" out of the if conditions as soon as I know that something changed. Is there a way to do this w

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.676 (s). 13 q(s)