DML:

According to Wikipedia, A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database.

Triggering:

Whenever you operate any DML operations in a table, also do something else in some other table. Suppose, if you run any DML operations in any table A then you also do something in table A’.

DB Outline:

• Suppose we want to monitor a table named tbl_employee. In this table, there will be some fields to identify an Employee such as id, name, salary, join_time. Create a table in your database like the following:

CREATE TABLE `tbl_employee` (
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT,
`salary` INT,
`join_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Employee';

• Now, we want to monitor salary increments of employees in another table. Suppose, our company evaluates employees and based on performances,  raises salary after 3 months. If the performance is not up to the mark, then we give them some month(s) to cope up. To get the updates of their salary history we create another table named tbl_employee_audit. Create a table like the following:

CREATE TABLE `tbl_employee_audit` (
	`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
	`emp_id` MEDIUMINT(8) UNSIGNED NOT NULL,
	`changetype` ENUM('NEW','INCREMENT') NOT NULL,
	`changetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	KEY `ix_blog_id` (`emp_id`),
	KEY `ix_changetype` (`changetype`),
	KEY `ix_changetime` (`changetime`),
	CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`emp_id`) REFERENCES `tbl_employee` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• We want history when a new employee was added to our company that is INSERT
• We want to track when an individual’s salary was raised that is UPDATE (for simplicity we will only update the salary field in tbl_employee of an individual only by salary greater than current)
• So we are using triggering on tbl_employee and getting logs from tbl_employee_audit

Basic Triggering:

• Triggering can be used Before or After DML Operations
• Triggering Body :

DELIMITER $$
CREATE
    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
		-- trigger body
		-- this code is applied to every 
		-- inserted/updated/deleted row
    END;
DELIMITER;

Our Custom Triggering:

• Let’s continue with our example.
• We will create 2 trigger events. one for INSERT and another for UPDATE
• INSERT( new employee entry ) Trigger:

DELIMITER $$

CREATE
TRIGGER `employee_after_insert` AFTER INSERT
ON `tbl_employee`
FOR EACH ROW BEGIN
SET @changetype = 'NEW';
INSERT INTO tbl_employee_audit (emp_id, changetype) VALUES (NEW.id, @changetype);

END$$

DELIMITER;

• UPDATE (when an employee’s salary is increased) Trigger:

DELIMITER $$

DELIMITER $$

CREATE
TRIGGER `salary_after_update` AFTER UPDATE
ON `tbl_employee`
FOR EACH ROW BEGIN
SET @changetype = 'INCREMENT';
INSERT INTO tbl_employee_audit (emp_id, changetype) VALUES (NEW.id, @changetype);

END$$

DELIMITER;

• Let’s run the following queries:

  INSERT INTO tbl_employee (NAME, salary) VALUES ('Tamim',40000 );
  INSERT INTO tbl_employee (NAME, salary) VALUES ('Shakib', 30000);
  

  After these queries, the tables will look like this:
  tbl_employee:

tbl_employee_audit:

• It’s Overall Execution Time: 0.170 sec only! If you wanted to maintain 2 separate queries one after one, the Execution Tine would have been much higher.
• Let’s run the following queries:

 
     UPDATE tbl_employee SET salary = 50000 WHERE id = 1;
     UPDATE tbl_employee SET salary = 40000 WHERE id = 2;
     

After these queries the tables will look like this:
tbl_employee:

tbl_employee_audit:

• So, we are making queries in the tbl_employee table and getting update/history from  tbl_employee_audit table without executing sqls again and again.