How to create a mysql trigger and execute with php

December 25, 2016 MySQL, MySQL, PHP 4 Comments

Spread the love
Last updated:9th March, 2019

How to create mysql trigger with php

What is mysql trigger?

A mysql trigger is a stored database object that is always associated with a database table and is executed automatically when a particular defined event occurs.

Events when a mysql trigger is executed

As stated before a mysql trigger is executed automatically BEFORE or AFTER occurrence of specific events. These events include INSERT, UPDATE and DELETE.

1. Before insertion of a new record

2. After insertion of a new record

3. Before update of a record

4. After update of a record

5. Before deletion of a record

6. After deletion of a record

How to create a mysql trigger

 A mysql trigger can be used to validate values that are going to be inserted into a table or can do some calculations or can perform audit on update or delete of sensitive data.

Syntax of mysql trigger

Hence to create a new trigger, CREATE TRIGGER MySQL statement is used. Below is syntax.
CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name

FOR EACH ROW
 
BEGIN

 //code here
 ...

 END;
 After CREATE TRIGGER statement trigger name is added. Mysql trigger should be named as  [trigger time]_[table name]_[trigger event],  for example a trigger that runs before update of an employee information can be named before_employees_update.
 Trigger is activated BEFORE or AFTER insert, update or delete of a record. You have to define trigger activation time. A mysql trigger cannot exist without association with a table so we have to specify table name after the ON keyword. SQL statements are between BEGIN and END block. Here we define the logic for the trigger.

How to drop a mysql trigger

To drop a mysql trigger, you have to use DROP TRIGGER statement. If a table is dropped all associated triggers are also dropped.
DROP TRIGGER before_subscriber_delete;

In this tutorial we are going to do following tasks.

    1. Create MySQL database test
    2. Create table. subscribers and audit_subscriber.
    3. Create 3 triggers associated with subscribers table.
    4. First trigger is executed before a subscriber is inserted into subscriber table.
    5. Second trigger is executed after deletion of a subscriber.
    6. Third trigger is executed on update of a subscriber information.
    7. Therefore to do this using PHP, we have to create HTML form to add subscribers.
    8. As a subscriber is added before_insert_subscriber mysql trigger executes automatically.
    9. Display all existing subscriber records.
    10. When a record is deleted after_subscriber_delete trigger is executed.
    11. After a record is updated after_subscriber_update trigger is executed.

We are going to create a database test and tables: subscribers and audit_subscriber.

mysql table for subscribers

The subscribers table will store name and email of a subscriber. We will create 3 associated triggers for subscribers table. Triggers are as follows.

  1. Before a new subscriber is inserted              – before_subscriber_insert
  2. After a subscriber is deleted                           – after_subscriber_delete
  3. After a subscriber information is updated  – after_subscriber_update
CREATE DATABASE test;


CREATE TABLE `subscribers` (
  `id` int(11) NOT NULL,

  `fname` varchar(200) NOT NULL,

  `email` varchar(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `subscribers` ADD PRIMARY KEY (`id`);


ALTER TABLE `subscribers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

 audit_subscriber table

This table will be populated by mysql triggers.

CREATE TABLE `audit_subscribers` (
  `id` int(11) NOT NULL,

  `subscriber_name` varchar(200) NOT NULL,

  `action_performed` varchar(400) NOT NULL,

  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `audit_subscribers` ADD PRIMARY KEY (`id`);

ALTER TABLE `audit_subscribers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

mysql trigger to execute before a subscriber insert

DELIMITER $$
CREATE TRIGGER `before_subscriber_insert` BEFORE INSERT ON `subscribers` FOR EACH ROW 

BEGIN

    INSERT INTO audit_subscribers

    SET action_performed  = 'Inserted a new subscriber',

    subscriber_name       =  new.fname;


END

$$

DELIMITER ;

MySQL trigger syntax starts with DELIMITER $$. Delimiters are used in start of a mysql trigger or procedure because in SQL we use semicolon (;) in each statement as a delimiter. In a mysql trigger or procedure we have to write multiple SQL statement and each statement ends with a semicolon.

To tell MySQL that a semicolon is not the end of our trigger, we changed delimiter from semicolon to $$. So MySQL knows that our trigger is starting with $$ and ends with $$. Trigger will execute before insert of a new subscriber.

Next we define Trigger name after CREATE TRIGGER statement. FOR EACH ROW statement states that trigger is going to be executed for each row that is effected by the event.

After BEGIN construct we write trigger code that is insert into audit_subscriber  table, name of the subscriber , action performed and current date time is  entered as a default value of date_added column. Trigger statement are completed with END statement and delimiter.

mysql trigger to execute after a subscriber delete

DELIMITER $$
CREATE TRIGGER `after_subscriber_delete` AFTER DELETE ON `subscribers` 

FOR EACH ROW 

BEGIN

    INSERT INTO audit_subscribers

    SET action_performed  = 'Deleted a subscriber',

    subscriber_name       =  OLD.fname;


END

$$

DELIMITER ;

The trigger above executes after a subscriber is deleted. It is executed for each row and  action_performed and subscriber name is inserted into audit_subscriber table, current date is inserted in to date_added column. Here we used OLD.fname that means existing name of subscriber.

mysql trigger to execute after a subscriber edit

DELIMITER $$
CREATE TRIGGER `after_subscriber_edit` AFTER UPDATE ON `subscribers` 

FOR EACH ROW 

BEGIN

    INSERT INTO audit_subscribers

    SET action_performed  = 'Updated a subscriber',

    subscriber_name       =  OLD.fname;


END

$$

DELIMITER ;

This trigger executes after a subscriber record is updated. Action performed and subscriber_name are entered  into audit_subscriber table and current a date time is added as default value for date_added field.

Create a database connection in php

Create a folder named mysql_trigger  in root folder of your WAMP, MAMP or XAMPP directory. Next create a file named dbconn.php to save database connection information and add following code.

File: dbconn.php

<?php

$host         = "localhost";
$username     = "root";

$password     = "";

$dbname       = "test";

$result = 0;

/*Create connection */
$conn = new mysqli($host, $username, $password, $dbname);


/*Check connection */
if ($conn->connect_error) {

     die("Connection to database failed: " . $conn->connect_error);
} 

?>

Create HTML form for adding user

To execute trigger on insert of a record, we need to create a form and submit information to php page, this information is saved to subscribers table.
 Create a page index.php and add following code.
File: index.php
<?php

require('dbconn.php');

$fname   = '';

$email   = '';

$error   = '';

$success = '';

if(isset($_POST['sub'])){

    $fname = $_POST['fname'];
  
    $email = $_POST['email'];
    
    if(!$fname || !$email){
    
      $error .= 'All fields are required. <br />';
    
    }elseif(!strpos($email, "@" ) || !strpos($email, ".")){
      
      $error .= 'Email is invalid. <br />';

    }

    if(!$error){
      //insert in to database
      $sql    = "insert into subscribers (fname, email) values (?, ?) ";
      $stmt   = $conn->prepare($sql);

      $stmt->bind_param('ss', $fname, $email);

      if($stmt->execute()){

        $success = 'Subscriber added successfully.';

        $fname = '';

        $email = '';

      }else{

        $error .= 'Error while saving subscriber. Try again. <br />';

      }
   }
}

?>
<!DOCTYPE html>
<html>

<head>

  <title>Mysql trigger Example with PHP</title>

  <style>
    .container {
      margin: 0 auto;

      padding: 10px;
    }
    .error {
      width: 100%;
      color: red;
    }
    .success {
      width: 100%;
      color: green;
    }
  </style>

</head>

<body>

<div class="container">

  <h2>Example: mysql trigger on before insert of record</h2>

  <h4>Register a Subscriber</h4>

  <?php if($error) { ?>

    <p class="error"><?php echo $error; ?></p>

  <? } ?>

  <?php if($success) { ?>

    <p class="success"><?php echo $success; ?></p>

  <? } ?>

  <form name="form1" method = "post">

    <p>

    First name:<br>

    <input type="text" placeholder='First Name' name="fname" value="<?php echo $fname; ?>" required >

    </p>

    <p>

    Email:<br>

    <input type="email" placeholder='Email' name="email" value="<?php echo $email; ?>" required >

   </p>

   <p>

    <input type="submit" value="Register Subscriber" name='sub'>

   </p>

  </form>

  <p>Upon clicking "Submit" button, form data is saved into subscriber table and a trigger 
     before_subscriber_insert will execute.</p>

</div>

</body>

</html>

Code explanation

Here in the code we created a form to get subscriber name and email. Then in php code dbconn.php is included to get database object.

if(isset($_POST['sub'])){ ... }
 In if block above we check that submit button is pressed and form data is posted. If the button is pressed then using $_POST super global array, values of name and email are assigned to variables.
After validation is performed, an insert query is passed to $conn‘s prepare function.
Using bind_param method bind parameter values. Before insertion of data to subscribers table, before_subscriber_insert trigger executes and insert values into audit_subscribers table.

Form to add a subscriber:

mysql trigger on before insert of a record

Subscriber insert successful:

mysql trigger on before insert of a record

mysql trigger executed before insert of values to the table

mysql trigger inserted value - before insert of original record

Values inserted into subscriber table after trigger execution

subscriber table values insertion after mysql trigger execution

As you can see the phpmyadmin screen shots above values inserted into audit_subscriber and subscriber‘s table.

Execute mysql trigger on delete and edit of a record

As the trigger is executed on before insertion of a record, we see that how AFTER DELETE mysql trigger is executed. Thus to do this we have to:
1. Display records from database on the page.

2. A php script to delete record.

 3. After deletion of records, a mysql trigger after_subscriber_delete on subscriber’s table

Display records from database

To execute delete or edit mysql triggers you have to display records first. As you can see in image below there are edit and delete links.
File: view_subscribers.php
<?php
require('dbconn.php');

$error   = '';

$success = '';

$result_array = array();

$sql    = " select id, fname, email from subscribers ";

$result = $conn->query($sql);

/*if there are results from database push to result array */

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {

        array_push($result_array, $row);
    }
}

?>

<!DOCTYPE html>

<html>

<head>

  <title>Mysql trigger Example with PHP</title>

  <style>
    .container {
      margin: 0 auto;

      padding: 10px;
    }
    .error {
      width: 100%;
      color: red;
    }
    .success {
      width: 100%;
      color: green;
    }
   

  table {

    border-collapse: collapse;

  }


  table, th, td {
    border: 1px solid black;

  }
  </style>

</head>

<body>

<div class="container">

  <h2>View Subscribers</h2>
  
  <h4>If you click on delete link. Record will be deleted and after delete trigger will execute.</h4>
  
  <h4>If you click on edit link. On update of record and after update trigger will execute.</h4>
  
  <?php if($error) { ?>
 
   <p class="error"><?php echo $error; ?></p>
  
  <? } ?>
  
  <?php if($success) { ?>
 
   <p class="success"><?php echo $success; ?></p>
  
 <? } ?>
    
  <table width="90%" >
      <tr>

      <th>#</th>

      <th>Name</th>

      <th>Email</th>

      <th>Action</th>

    </tr>
  
    <?php for($i=0; $i<count($result_array); $i++){ ?>
  
    <tr>

      <td><?php echo $i+1 ?></td>

      <td><?php echo $result_array[$i]['fname'] ?></td>

      <td><?php echo $result_array[$i]['email'] ?></td>

      <td><a href="subscriber_edit.php?id=<?php echo $result_array[$i]['id'] ?>" >Edit</a>

      &nbsp;|&nbsp;<a onclick="return confirm('Are you sure you want to delete this subscriber?');" 
      href="subscriber_del.php?id=<?php echo $result_array[$i]['id'] ?>">Delete</a>

      </td>

    </tr>
    
    <?php } ?>
    
   </table>

</div>

</body>

</html>

view subscribers - mysql triggers delete and edit records

 Code Explanation

Here first dbconn.php is included to connect to database and display records from database. A query is passed to select records from subscribers table to $conn‘s query method. Results are saved in $result variable.
Further if result set has records then using fetch_assoc function, we fetch each row in result set to $row in a while loop and push each row to $results_array array.
HTML document is added display records below php code. We defined some basic CSS rules for success, error, table, tr, and td. Inside container div, there is HTML table to display records.

Execute mysql trigger on delete of a record

In the picture above we have displayed subscribers records. Clicking on Delete link will display a confirmation message, If user clicks OK then she is redirected to subscriber_del.php page.

In this page record is deleted from database and a trigger after_subscriber_delete is triggered automatically and deleted user name, action performed and current date time is inserted into audit_subscriber table.

mysql trigger on deletion of a record

File: subscriber_del.php

Below is the code to delete a subscriber

<?php
require('dbconn.php');

$id  = intval($_GET['id']);

if($id){
  $sql    = " delete from subscribers where id = ?";

  $stmt   = $conn->prepare($sql);

  $stmt->bind_param('s', $id);

  $stmt->execute();

  header("Location: records.php");

  exit;
}

Record is inserted into audit_subscribers table after record deletion. You can see below a user Jane record is deleted and AFTER DELETE MySQL trigger inserts records into audit_subscribers table.

audit reord after delete mysql trigger execution

Execute mysql trigger on update of a record

Likewise Before Insert and After Delete mysql triggers, let us see after update trigger. Records are already displayed. When a user clicks on Edit link, browser redirects to subscriber_edit.php page.

File: subscriber_edit.php
<?php
require('dbconn.php');

$error   = '';
$success = '';

$fname   = '';

$email   = '';

$error   = '';

$success = '';

$id      = intval($_GET['id']);

if(isset($_POST['sub'])){

    $fname = $_POST['fname'];

    $email = $_POST['email'];

    if(!$fname || !$email){
      
      $error .= 'All fields are required. <br />';

    }elseif(!strpos($email, "@" ) || !strpos($email, ".")){

      $error .= 'Email is invalid. <br />';

    }

    if(!$error){
      //update data in database
      
      $sql    = "update subscribers set fname = ?, email = ? where id = ? ";

      $stmt   = $conn->prepare($sql);

      $stmt->bind_param('sss', $fname, $email, $id);

      if($stmt->execute()){
        
        $success = 'Subscriber added successfully.';

        $fname = '';

        $email = '';

      }else{

        $error .= 'Error while saving subscriber. Try again. <br />';

      }
   }
}

//Fetch existing record to edit
$sql    = " select id, fname, email from subscribers where id = $id";

$result = $conn->query($sql);

$record = $result->fetch_assoc();

$fname  = $record['fname'];

$email  = $record['email'];

?>
<!DOCTYPE html>

<html>

<head>

  <title>Mysql trigger example with PHP</title>

  <style>
    .container {
      margin: 0 auto;

      padding: 10px;
    }
    .error {
      width: 100%;
      color: red;
    }
    .success {
      width: 100%;
      color: green;
    }
  </style>

</head>

<body>

<div class="container">

  <h2>Example mysql trigger: after update of a record</h2>

  <h4>Update a subscriber information</h4>

  <?php if($error) { ?>


    <p class="error"><?php echo $error; ?></p>

  <? } ?>

  <?php if($success) { ?>

    <p class="success"><?php echo $success; ?></p>

  <? } ?>

  <form name="form1" method = "post">

    <input type="hidden" name='id' value="<?php echo $id; ?>" >

    <p>

    First name:<br>

    <input type="text" placeholder='First Name' name="fname" value="<?php echo $fname; ?>" required >

    </p>

    <p>

    Email:<br>

    <input type="email" placeholder='Email' name="email" value="<?php echo $email; ?>" required >

   </p>

   <p>

    <input type="submit" value="Update" name='sub'>

   </p>

  </form>

  <p>Upon clicking "Update" button, form data is updated into subscriber table, a mysql trigger named  
   after_subscriber_update will execute.</p>

</div>

</body>

</html>

Code explanation

In the file, we get id of user from query string using $_GET  super global array. Using id we executed a query to select existing information of subscriber from the database and displayed in HTML form. The id of the subscriber is saved into hidden input field. 

 <input type="hidden" name='id' value="<?php echo $id; ?>" >

When user clicks on Save button, data is posted to the server,  $_POST superglobal array is used to assign data to variables. After performing validation, an update query is executed. As data is saved, AFTER UPDATE trigger is executed, subscriber’s old name, action performed and current date time is inserted into audit_subscriber table.

mysql trigger - after delete of record

In the image above you can see data of user john is updated and update trigger is executed.

Summary

To summarize this article you have seen practical example of how a mysql trigger is created and executed on insert, update  or delete events.

With this in mind to read more about MySQL triggers please visit MySQL reference. If you have any question, comment or feedback please post in the comment below. Thank you for reading.

 

Related Articles:

 

Previous Article:

 

Next Article:

Save

All Rights Reserved 2016 - Programmer Blog