Hi,  This is my first post on blogaddition.com. In this tutorial, I’ll talk about “Data Integrity And Consistency Through Trigger ” in MYSQL. As we know that, It is very important for data in a database to be accurate and correct.There are various methods to ensure this , but triggers have their own significance in maintaining the integrity and consistency of data.

A trigger can be used to ensure and enforce business rule s and data integrity and enable the performance of complex actions and cascade these actions to other dependent tables .

Business rules refer to the policies of an organization , which ensure that it’s business runs smoothly .

Data Integrity refers to the accuracy and reliability of data.

For Example , an item can be sold only if sufficient stack is available . If a trigger is used , it would check against incorrect transactions and ensure that only valid data is inserted in table.

Basically Trigger is a pre-compiled database object which consists a set of T-Sql statement that are activated in response to certain action.It is fired whenever data in the underlying table is affected by any of the Data Manipulation Language(DML) statements- INSERT , UPDATE ,or DELETE.

Characteristic of Trigger :

1) Trigger is fired automatically by database server whenever any data modification statement is issued.

2) Trigger can not be explicitly invoked or executed , as in the case of stored procedures.

3) Trigger Prevents incorrect , unauthorized , and inconsistent changes in database.

4) Trigger can not return data to user.

Have a look at the following example.

CREATE TABLE `account` (

`account_id` INT(11) NOT NULL AUTO_INCREMENT,

`account_name` VARCHAR(200) DEFAULT NULL,

`balance` DOUBLE DEFAULT ’0′,

PRIMARY KEY (`account_id`)

)

CREATE TABLE `withdrawl_details` (

`withdrawl_id` INT(11) NOT NULL AUTO_INCREMENT,

`account_id` INT(11) NOT NULL,

`withdrawl_amount` DOUBLE DEFAULT ’0′,

PRIMARY KEY (`withdrawl_id`)

)

DELIMITER $$

DROP TRIGGER account_trigger`$$

CREATE

TRIGGER `account_trigger` BEFORE INSERT ON `withdrawl_details`

FOR EACH ROW BEGIN

DECLARE msg VARCHAR(255);

DECLARE available_balance DOUBLE DEFAULT 0;

SET available_balance=(SELECT balance FROM account WHERE account_id=NEW.account_id);

IF (available_balance-NEW.withdrawl_amount<0) THEN  SIGNAL SQLSTATE `45000`

SET MESSAGE_TEXT = `’Withdrawl amount is more than available balance in account.’`;  END IF ;

END;

$$

DELIMITER ;

Result : - whenever an attempt made to withdrawal amount and balance is insufficient then account_trigger will prevent to maintain the business rules.