MySQL Trigger

Below is a primer on MySQL Trigger I wrote for the MySQL course I taught.

Like the name suggests, triggers are MySQL objects that will objects when a specific event occurs (Insert, Update, Delete). So, for example I have an employee table that I’d like to track all updates to it:

Create the table : (Employee)

CREATE TABLE `employee` (

  `employee_id` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `salary` decimal(10,2) NOT NULL,

  PRIMARY KEY  (`employee_id`)

)

 

Create my log table: (update_logs)

CREATE TABLE update_logs (

update_id int(11) NOT NULL AUTO_INCREMENT,

employee_id int(11) NOT NULL,

`salary` decimal(10,2) NOT NULL,

  `name` varchar(50) NOT NULL,

updated_date datetime DEFAULT NULL,

PRIMARY KEY (update_id)

)

 

Now, Creating a trigger which generate the log of updates in the table

DELIMITER $$

CREATE TRIGGER before_update_employee

BEFORE UPDATE ON employee

FOR EACH ROW BEGIN

INSERT INTO update_logs SET

employee_id = OLD.employee_id,

salary = OLD.salary,

name = OLD.name,

updated_date = NOW(); END

 

To check the trigger, Update any row in the employee table, for example

UPDATE `employee` SET `name` = ‘test-updated’ WHERE `employee_id` =100;

You should see a new row inserted in the update_logs table.

 

Note: The above trigger  uses the BEFORE UPDATE Event, so the trigger is actually executed before the actual update in the employee table. This will allow me to track any “attempts”, including failed ones, to modify the Employee table.

Posted in Programming
One comment on “MySQL Trigger
  1. Track Your Software Using PHP and MySQL says:

    Hi there! I realize this is sort of off-topic but I needed to ask.
    Does managing a well-established website such as yours require a large amount
    of work? I am brand new to running a blog however
    I do write in my diary daily. I’d like to start a blog so I can easily share my personal experience and thoughts online. Please let me know if you have any ideas or tips for brand new aspiring bloggers. Thankyou!

Leave a Reply

Your email address will not be published. Required fields are marked *

*


7 + seven =