Create MySQL Trigger in PHP

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




Summary

A trigger is a named block that executes, or fires, in response to an insert, delete, or update statement.