Triggers in mysql database

In a database, a trigger is a set of user defined Structured Query Language (SQL) statements that will automatically fires an action when a specific operation occurs in a table. A trigger automatically called when an INSERT, DELETE or UPDATE operation executed on an associated table.

Events

Database Triggers are associated with the database DML(Data Manipulation Language) actions INSERT, UPDATE, and DELETE.  Triggers are defined to run when any of these actions executed on a specific table.

Get list of existing triggers

To get list of existing database triggers, you can execute sql statement:

SHOW TRIGGERS

Delete existing trigger

You can delete existing trigger by trigger name.

DROP TRIGGER IF EXISTS `<databaseName>`.`<triggerName>`

Syntax

DELIMITER $$
USE `<databaseName>`$$
DROP TRIGGER IF EXISTS `<databaseName>`.`<triggerName>` $$
USE `<databaseName>`$$
CREATE DEFINER = CURRENT_USER TRIGGER `<databaseName>`.`<triggerName>` <EVENT_TYPE> ON `<associatedTableName>` FOR EACH ROW
BEGIN
// SQL statement
END
$$
DELIMITER ;

Here EVENT_TYPE can be ‘BEFORE UPDATE’, ‘AFTER INSERT’, ‘AFTER DELETE

Examples

Scenario 1: Update comment count in a table when a new comment is created. In this scenario, associated table is `comments` table and target table is the `posts` table. Here is the schema for `comments` table and `posts` table.

CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`comment_by` int(11) NOT NULL DEFAULT '0',
`entity_id` int(11) NOT NULL DEFAULT '0',
`comment` text NOT NULL,
`status` tinyint(1) DEFAULT '1',
`deleted_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`description` text COLLATE utf8_unicode_ci,
`comments_count` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
)

As increment in comment count in target table will take place when a new comment is inserted in `comments` table. Event type for this scenario is ‘AFTER INSERT’.

DELIMITER $$
USE `<databaseName>`$$
DROP TRIGGER IF EXISTS `<databaseName>`.`<triggerName>` $$
USE `<databaseName>`$$
CREATE DEFINER = CURRENT_USER TRIGGER `<databaseName>`.`<triggerName>` AFTER INSERT ON `<associatedTableName - comments>` FOR EACH ROW
BEGIN
UPDATE posts SET comments_count = comments_count + 1 WHERE posts.id = NEW.postId;
END
$$
DELIMITER ;

 

Scenario 2: conditional trigger, decrement in comment count when a comment is deleted. As decrement in comment count in target table will take place when comment status is updated in `comments` table. Event type for this scenario is ‘BEFORE UPDATE’.

DELIMITER $$
USE `<databaseName>`$$
DROP TRIGGER IF EXISTS `<databaseName>`.`<triggerName>` $$
USE `<databaseName>`$$
CREATE DEFINER = CURRENT_USER TRIGGER `<databaseName>`.`<triggerName>` BEFORE UPDATE ON `<associatedTableName - comments>` FOR EACH ROW
BEGIN
IF (NEW.status = 0 ) THEN 
SET NEW.deleted_at=UNIX_TIMESTAMP();
UPDATE posts SET comments_count = comments_count - 1 WHERE posts.id = NEW.postId;
END IF;
END
$$
DELIMITER ;

“SET NEW.deleted_at=UNIX_TIMESTAMP()” statement is to update deleted_at column in `comments` table.

If you are writing a trigger where you need to compare value of column before update and after update, you can get column’s  old value by OLD.columnName and new value by NEW.columnName

If you execute an insert statement in trigger and want to get inserted row id to perform any action. You can get inserted row id by SELECT LAST_INSERT_ID()

Please follow and like us:

Leave a Reply

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

Back to Top