MySQL trigger

The MySQL trigger are action (in a database object) that is associated with a table. It will be activated when a defined action is executed on the table.
The trigger will be executed while you run any one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.
 // MySQL trigger syntax

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

// Trigger activation time can be BEFORE or AFTER.
// The trigger event can be INSERT, UPDATE or DELETE.

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(50) NOT NULL,
    emailAddress VARCHAR(50) NOT NULL,
    mobile VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

CREATE TABLE user_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

Now create a create trigger BEFORE UPDATE that will be invoked before any change is made on the employees table.

DELIMITER $$
CREATE TRIGGER before_user_update 
    BEFORE UPDATE ON user
    FOR EACH ROW 
BEGIN
    INSERT INTO user_audit
    SET action = 'update',
     userId= OLD.userId,
        lastname = OLD.lastname,
        changedat = NOW(); 
END$$
DELIMITER ;

To view all triggers in the database (current database), we can use SHOW TRIGGERS statement as follows:

SHOW TRIGGERS;

Q- What is trigger new and trigger old in MYSQL?

1. OLD and NEW are MySQL extensions to triggers.
2. OLD and NEW keywords are user In trigger body.
3. These are not case sensitive.
4. The OLD and NEW keywords enable you to access columns in the rows affected by a trigger.
5. In an INSERT trigger, only NEW.col_name can be used. there is no old row.
6. In an UPDATE TRIGGER, you can use the OLD keyword to access the row data which is being replaced by the update.
7. The NEW keyword allows accessing the incoming row data which will replace the old row, if successful.

Depending on the type of trigger created, the OLD and NEW rows may not be available to you:

INSERT TRIGGER: Access to the NEW pseudo rows only.
UPDATE TRIGGER: Access to the NEW and OLD pseudo rows i.e. OLD refers to the row before it is updated and NEW refers to the row after it is updated.
DELETE TRIGGER: Access only to the OLD pseudo rows

i.e. there is no OLD row on an INSERT trigger, and no NEW row on a DELETE trigger.