Introduction
When we want to execute a specific task at a scheduled time, MySQL provides special functionality called triggers. Triggers are basically named blocks of code that are executed, or fired, automatically when a specified type of SQL statement is executed. Triggers can be executed before after an INSERT, UPDATE or DELETE statement is executed on the table.
Syntax
CREATE TRIGGER trigger_Name
{Before|After}{Insert|Update|Delete}ON table_name
For EACH ROW
sql_block |
For Example
Using the following query, you can better understand what a trigger is and how to use a trigger to execute a specific task at a scheduled time. Query
The following query creates a trigger named "MysqlTrigger", this trigger is associated with the name emp table and is fired before an update. When you try to update a name with lower case, it will automatically convert it to UPPER case .
"CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON "emp" FOR EACH ROW SET NEW.name=UPPER(NEW.name);" The following is an Update statement that fires the trigger:
UPDATE emp SET name='ram' where id=2
The following is a select statement that shows the new row:
Select * from emp where id=2 Example of Creating a trigger in PHP
In this example a simple "CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON emp FOR EACH ROW SET NEW.name=UPPER(NEW.name);" query creates a trigger that will be useful when for updating an employee's name based on their id (in other words emp id). Suppose you write the query "UPDATE emp SET name='ram' where id=2", when this query fires, the name corresponding to id 2 is updated. Suppose that before the update, the name corresponding to id 2 is "vinod", then this query changes "vinod" to "ram". In other words, the trigger that was previously created by you, changes "ram" to "RAM".
<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("sharad", $con);
print "<h2>MySQL: Simple Select statement</h2>";
$result = mysql_query("select * from emp");
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
print "<h2>CREATE MySQL Trigger In PHP</h2>";
$sql = "CREATE TRIGGER MysqlTrigger BEFORE UPDATE ON emp FOR EACH ROW SET NEW.name=UPPER(NEW.name);";
mysql_query($sql,$con);
print "<h2>MySQL: Update Statement</h2>";
$qry = mysql_query("UPDATE emp SET name='ram' where id=2");
echo "Table has been updated.";
mysql_query($qry,$con);
print "<h2>MySQL: Effect of Trigger</h2>";
$result = mysql_query("select * from emp");
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
Note: You can not create a trigger on "system tables".
Output
![mysql-trigger-in-php.jpg]()
Summary
A trigger is a named block that executes, or fires, in response to an insert, delete, or update statement.