Trigger does make change in table

Question

I am inserting few fields into a database, that I can do. I have taken one trigger which inserts data into table "AFTER INSERT" event.

<html>
<body>

<?php
$id=$_POST['id'];
$a=$_POST['id'];
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$city=$_POST['city'];

$con=mysqli_connect('127.0.0.1:3306' ,'root','root','my_db');
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql1="select * from table2";

$result = mysqli_query($con,$sql1);
echo "<table border='1'>

<tr>
<th>Id</th>
<th>Firstname</th>
<th>Lastname</th>
<th>City</th>
</tr>";
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))

{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['fname'] . "</td>";
echo "<td>" . $row['lname'] . "</td>";
echo "<td>" . $row['city'] . "</td>";
echo "</tr>";
}
echo "</table>";

$sql3 = "CREATE TRIGGER MysqlTrigger AFTER INSERT ON temp2 FOR EACH ROW BEGIN INSERT INTO temp VALUES(new.id,new.fname,new.lname,new.city,new.city);"; mysqli_query($con,$sql3);

$sql5="INSERT INTO temp2 (id,fname, lname, city)
VALUES
('$_POST[id]','$_POST[fname]','$_POST[lname]','$_POST[city]')";
mysqli_query($con,$sql5);



echo "1 record added";

//--------------- Trigger started ------------------

print "<h2>CREATE MySQL Trigger In PHP</h2>";
echo "<table border='1'>
<tr>
<th>Id</th>
<th>Firstname</th>
<th>Lastname</th>
<th>City</th>
<th>Status</th>
</tr>";
$sql4="select * from temp";
$res = mysqli_query($con,$sql4);
while($row = mysqli_fetch_array($res,MYSQLI_ASSOC))

{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['fname'] . "</td>";
echo "<td>" . $row['lname'] . "</td>";
echo "<td>" . $row['city'] . "</td>";
echo "<td>" . $row['stat'] . "</td>";
echo "</tr>";
}
echo "</table>"; 

mysqli_close($con);

?>
</body>
</html>

This inserts data into temp2 table successfully, but event in the trigger does not make any changes to temp table.

Where am I making mistake?

Once a trigger has been defined, it is automatically invoked by MySQL every time the event (in your case, after an insertion on temp2) arises. However, in this case the trigger is not defined until after the event has occurred and thus will not be invoked until subsequent insertions arise. — eggyal
@eggyal: Even after defining trigger before insert operation it does not make change! — Catty

SQL.injection SQL.injection · Accepted Answer · 2013-07-03T12:15:04

from the mySQL manual

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

you are missing the delimeters and an end on your trigger

Why exactly are you trying to create the trigger using php? can't you create it with phpMyAdmin on MySQL Workbench? the trigger should live in the database... if it is not supposed to be permanent, you should drop it at the end of your php code...

Trigger does make change in table

1 Answers