Error in creating trigger using PHP script.. Please help!!!! Geeks
- by Parth
I've been successful in creating a simple trigger with a PHP script. The problem comes when I have to use "DELIMITER" in my trigger creating because I have nested if/then statements. For example, the following DOES work:
$sql = 'CREATE TRIGGER `database`.`detail` BEFORE INSERT on `database`.`vibez_detail` FOR EACH ROW set @a=new.realtime_value';
$junk = mysqli_query($link, $sql);
However, if I need to use "DELIMITER" I get errors galore. Here's what works if I go to the MySQL client at the server:
DELIMITER $$;
DROP TRIGGER `database`.`detail`$$
CREATE TRIGGER `database`.`detail` BEFORE INSERT on `database`.`vibez_detail` FOR EACH ROW BEGIN
set new.data_rate = 69;
insert into alarm_trips_parent values (null, new.data_rate, 6969, now());
set @tripped_time = now();
set @tripped:=true;
end if;
end if;
END$$
DELIMITER ;$$
How can I construct the PHP code to get this to work? Seems the mysqli_query($link, $sql) chokes as soon as I put "DELIMITER" in the $sql variable.
I get the following error when I attempt to do so:
Error: 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 //; CREATE TRIGGER `database`.`detail` BEFORE INS' at line 1
Even The concept for using mysqli_multi_query(); didn't worked for me.... :(
