Creating a trigger via PHP program
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
Subject
Views
Written By
Posted
Creating a trigger via PHP program
33476
March 15, 2006 01:02PM
8573
July 13, 2006 05:58AM
16945
July 13, 2006 06:51AM
7172
June 01, 2007 08:51AM
6949
January 31, 2008 10:47AM
6001
April 02, 2008 08:53AM
Sorry, you can't reply to this topic. It has been closed.
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.