Page 1 of 1
a trigger written in PHP
Posted: Wed May 18, 2011 10:04 am
by emelianenko
Hello,
well, actually a trigger written in php is not much different from what you would write in a sql environment, yet, it aint working.
What I want is to have inserted 2 different 'id' from 2 different tables into a third table that contains said 'ids' as Foreign Keys. So when I do an insert in both 2 tables, those newly generated ids are also passed on to the 3rd table, to their FK, as I said.
So I sort of go like this
Code: Select all
$sno_id = mysql_insert_id(); // this gets the last generated id and passes it to a variable, and I will use this variable in the query
$trigger = "CREATE TRIGGER pass_the_ids
AFTER INSERT ON second_table
FOR EACH ROW
BEGIN
INSERT INTO the_third_table
NEW.sid = . '$sno_id' .
END;
";
Well, it gives error after trying to pass the query as $junk = mysqli_query($link, $trigger);
Also you note that I am only passing or trying to pass just one id from one table, havent even tried to do it with 2.
Re: a trigger written in PHP
Posted: Wed May 18, 2011 10:41 am
by Jade
You have a syntax error:
Code: Select all
$trigger = "CREATE TRIGGER pass_the_ids
AFTER INSERT ON second_table
FOR EACH ROW
BEGIN
INSERT INTO the_third_table
NEW.sid = '$sno_id'
END;";
Re: a trigger written in PHP
Posted: Wed May 18, 2011 11:14 am
by emelianenko
Hello
thank you. Now it does not give an error, yet, nothing gets inserted into the third table.
this is all I have concerning this snippet
Code: Select all
$sql2="INSERT INTO courses (name, description, faculty)
VALUES ('$course','$description','$faculty')";
if (!mysql_query($sql2,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
//=====================================================
$trigger = "CREATE TRIGGER pass_the_ids
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO the_third_table // which is actually called course_students as it holds the ids from each table but herein i m trying to pass the students id
NEW.sid = '$sno_id'
END;
";
$result = mysql_query($trigger, $con);
//===================================================
$sql="INSERT INTO students (firstname, lastname, town)
VALUES ('$firstname', '$lastname', '$town')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
$sno_id = mysql_insert_id();
echo $sno_id;
mysql_close($con)
?>
Re: a trigger written in PHP
Posted: Wed May 18, 2011 11:26 am
by Jade
You have to create the trigger before you insert the records otherwise it won't fire.
Re: a trigger written in PHP
Posted: Wed May 18, 2011 11:36 am
by emelianenko
fully right, therefore, I have just moved the block snippet of the trigger above the insert query (as edited) , yet....nothing is happening in that third table (course_students)...
Re: a trigger written in PHP
Posted: Wed May 18, 2011 12:25 pm
by mikosiko
@emelianenko:
You have several problems here:
- You should be creating your trigger OUTSIDE of your php code or strictly controlling that part of the code be executed
only one time, otherwise you will get errors.
- Triggers DOESN'T accept parameters, therefore trying to pass to it the id of the last inserted record from PHP make no sense, a solution for that is to use the MYSQL function LAST_INSERT_ID() inside of your trigger... quick example:
Code: Select all
....
...
BEGIN
DECLARE lastid INTEGER;
SELECT LAST_INSERT_ID() INTO lastid;
INSERT INTO the_third_table (<your_student_id_column>) VALUES (lastid);
...
...
lastly ... reading your apparent objectives seems to me that a trigger is no the right solution... you will be better:
a) writing the INSERT code for your other tables directly in your PHP code (very simple) or
b) creating a FUNCTION or PROCEDURE in MYSQL (both accept parameters, but have some constraints too)
Re: a trigger written in PHP
Posted: Wed May 18, 2011 12:39 pm
by emelianenko
Hi
I implemented this
Code: Select all
DELIMITER //
CREATE TRIGGER ai_course_students_fer
AFTER INSERT ON students
FOR EACH ROW
BEGIN
DECLARE lastid INT;
SELECT LAST_INSERT_ID() INTO lastid;
INSERT INTO course_students (sid) VALUES (lastid);
END;
//
and got the error, that well, I knew about that limitation, but I dont see this limitation taking place here. It says that "this version of mysql does not yet support multiple triggers with the same action time and event for one table. The version is 5.1.41
Well it says "after insert and insert into" but one is the condition and the other is the consequence...
regards