How to use MySQL triggers in PHP?

Using MySQL triggers in PHP requires: Create a trigger: Use the SQL CREATE TRIGGER statement to create a trigger in the database. Fire a trigger: Use the mysqli_query() function to execute a trigger. Practical application: Use MySQL triggers in PHP to enforce data integrity, such as emitting error messages when trying to update negative values.

How to use MySQL triggers in PHP

MySQL trigger is a database object that when certain conditions are met, It automatically performs a series of actions. Triggers are typically used to maintain data integrity in a database or perform other automated tasks.

Creating triggers

The first step to using triggers in PHP is to create them in the database. A trigger can be created with the following SQL statement:

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
  -- 要执行的语句
END

For example, let us create a trigger that inserts the current time when a new row is inserted into the users table:

CREATE TRIGGER insert_timestamp
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END

Trigger triggers using PHP

After you create triggers, you can use PHP to trigger them. Triggers can be executed through the mysqli_query() function:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

$mysqli->query("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com')");
?>

When this code is executed, the insert_timestamp trigger will be fired and will be displayed on users Insert the current time when inserting a new row into the table.

Practical case

The following is a practical case using MySQL triggers in PHP:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

$mysqli->query("CREATE TRIGGER update_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '余额不能为负值';
  END IF;
END
");

$mysqli->query("UPDATE accounts SET balance = -100 WHERE id = 1");
?>

This example is trying to update the account balance to a negative value , shows how to use triggers to enforce data integrity in PHP.

The above is the detailed content of How to use MySQL triggers in PHP?. For more information, please follow other related articles on the PHP Chinese website!