Create Trigger in MySQL

Using CREATE TRIGGER statement you can create a trigger. You have to put the trigger name after the trigger statement.

Let’s see an example of Create Trigger:

For example we have two tables i.e. users and userhistory.

users:
userID
firstName
lastName

userhistory:
userID
last_update

Read Also: INSERT … ON DUPLICATE KEY UPDATE

Now we will create a trigger before update users table:

DELIMITER $
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW BEGIN

INSERT INTO userhistory
SET last_update = NOW(),
userID = OLD.userID;

END$
DELIMITER ;

Whenever user update their information userhistory table also get updated automatically.

Triggers could be defined on the table, schema, view or database with which the event is associated.

Triggers can be written for the following purposes:

• To prevent invalid transactions
• Security authorizations impose
• Like auditing
• Referential integrity enforce
• Event logging
• Information store on table access
• Synchronous replication of tables
• Generate derived column values automatically