how to write trigger in php mysql

Question

In my php site admin have to update contents in the site weekly. If he forgot to update data , I want to sent an email alert to him. How it is possible? Is it possible to sent mail from back end. I have googled and i got we can write trigger. but I dont know how to do that. anybody please help me.

Answer 1

MySQL trigger syntax

In order to create a trigger you use CREATE TRIGGER statement. The following illustrates the syntax of the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END

Let’s examine the syntax above in more detail.

You put the trigger name after the CREATE TRIGGER statement. The trigger name should follow the naming convention [trigger time][table name][trigger event], for example before_employees_update.

Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger. You use BEFORE keyword if you want to process action prior to the change is made on the table and AFTER if you need to process action after change are made.

Trigger event can be INSERT, UPDATE and DELETE. These events cause triggers to be invoked. A trigger only can be invoked by one event. To define a trigger that is invoked by multiple events, you have to define multiple triggers, one for each event.

A trigger must be associated with a specific table. Without a table trigger would not exist therefore you have to specify the table name after the ON keyword.

The SQL code is placed between BEGIN and END keywords.

The OLD and NEW keywords help you develop trigger more efficient. The OLD keyword refers to the existing record before you change the data and the NEW keyword refers to the new row after you change the data.

MySQL trigger example

Let’s start creating a trigger in MySQL to audit the changes of the employees table.

First, we have employees table in our MySQL sample database as follows:

Second, we create a new table named employees_audit to keep the change of the employee records. The following script creates the employee_audit table.

CREATE TABLE employees_audit (
   id int(11) NOT NULL AUTO_INCREMENT,
   employeeNumber int(11) NOT NULL,
   lastname varchar(50) NOT NULL,
   changedon datetime DEFAULT NULL,
   action varchar(50) DEFAULT NULL,
   PRIMARY KEY (id)
)

Third, we create the BEFORE UPDATE trigger to be invoked before a change is made to the employee records.

DELIMITER $$
CREATE TRIGGER before_employee_update
    BEFORE UPDATE ON employees
    FOR EACH ROW BEGIN

    INSERT INTO employees_audit
    SET action = 'update',
        employeeNumber = OLD.employeeNumber,
        lastname = OLD.lastname,
        changedon = NOW();
END$$
DELIMITER ;

If you take a look at the schema, you will see before_employee_update trigger under the employees table as follows:

Now it’s time to update an employee record to test if the trigger is really invoked.

UPDATE employees
SET lastName = 'Phan'
WHERE employeeNumber = 1056

To check if the trigger was invoked by the UPDATE statement, we can query the employees_audit table by using the following query:

SELECT *
FROM employees_audit

The following is the output of the query:

As you see, our trigger was really invoked so that we have a new record in the employees_audit table.

In this tutorial, you have learned how to create a trigger in MySQL. You’ve written a trigger to audit the changes of the employees table.

Answer 2

You can also do this without a trigger if your webhost allows you to do scheduled jobs. You can just have a php script run at regular intervals to check the database and send the email.