CODEWITHSUNDEEP
phpmysqltriggersphpmyadmindelimiter
Matarishvan
Matarishvan

Reputation: 2422

Mysql Error for using Delimiters in triggers

This is my mysql trigger statement

DELIMITER |
CREATE TRIGGER kk AFTER UPDATE
    ON location FOR EACH ROW
 BEGIN
        IF NEW.name not in(SELECT A.name FROM filter A  WHERE (NEW.name = A.name))THEN  

        IF NEW.location_name != OLD.location_name THEN
            INSERT INTO filter(old_location_name,new_location_name) 
            VALUES (OLD.location_name, NEW.location_name);

        ELSE
            
                UPDATE filter SET old_location_name = OLD.location_name , new_location_name = NEW.location_name WHERE name = OLD.name;  

        END IF; 

        ELSE

            UPDATE filter SET old_location_name = OLD.location_name , new_location_name = NEW.location_name WHERE name = OLD.name;

  
        END IF; 
              
  END;|
DELIMITER ;

This works fine, when i paste this code in phpMyadmin.

And in my project

$sqlDrop = "DROP TRIGGER IF EXISTS `kk`";       
$resDrop = parent::_executeQuery($sqlDrop); 

echo $sql = "same above delimiter trigger query";
$rs = parent::_executeQuery($sql );

Now the $sql statement echos in a single line

 DELIMITER | CREATE TRIGGER kk AFTER UPDATE ON location FOR EACH ROW BEGIN IF NEW.name not in(SELECT A.name FROM filter A  WHERE (NEW.name = A.name))THEN IF NEW.location_name != OLD.location_name THEN INSERT INTO filter(old_location_name,new_location_name,name) VALUES (OLD.location_name, NEW.location_name);    ELSE UPDATE filter SET old_location_name = OLD.location_name , new_location_name = NEW.location_name WHERE name = OLD.name; END IF; ELSE UPDATE filter SET old_location_name = OLD.location_name , new_location_name = NEW.location_name WHERE name = OLD.name; END IF; END;| DELIMITER ;

And the query is not executed. And when i paste the echoed statement in my phpMyadmin, its showing me an error

enter image description here

So what could be the problem. Is it the right way of executing trigger queries?

Upvotes: 4

Views: 150

Answers (2)

Arun
Arun

Reputation: 684

I'm not sure if this really helps you. How about Executing each queries seperately?

$startDelimit = 'DELIMITER |';
$resStart = parent::_executeQuery($startDelimit);

Then your create trigger statement

$sql = "CREATE TRIGGER kk AFTER UPDATE ...... END IF; END;";
$rs = parent::_executeQuery($sql );

And then last statement

$endDelimit = '| DELIMITER ;';
$resEnd = parent::_executeQuery($endDelimit );

Upvotes: 2

Rahul
Rahul

Reputation: 77866

Not sure about any PHP related issue but try modifying your trigger code like below

DELIMITER |
CREATE TRIGGER kk AFTER UPDATE
    ON location FOR EACH ROW
 BEGIN
        IF NEW.name not in(SELECT A.name FROM filter A  WHERE NEW.name = 
A.name) AND NEW.location_name != OLD.location_name THEN      
            INSERT INTO filter(old_location_name,new_location_name) 
            VALUES (OLD.location_name, NEW.location_name);    
        ELSE    
                UPDATE filter SET old_location_name = OLD.location_name ,
 new_location_name = NEW.location_name WHERE name = OLD.name;      
        END IF;     
END;|
DELIMITER ;

Upvotes: 2

Related Questions