Data Integrity Using Trigger

Data Integrity Using Trigger

0 Shares

Trigger is a rule that constitutes a set of T-SQL statements activated in response to a certain actions such as insert or delete. Trigger are used to ensure data integrity before or after performing data manipulation.

Trigger Feature

Trigger can be useful for :

  • Examining data before it is inserted or updated or verify deletes or update
  • Acting as a data filter by modifying data if it is out of range before an insert or update.
  • Modifying how INSERT, UPDATE and DELETE behave for a Table.
  • Logging functionality
  • Automatic summary tables.

Types of Trigger

Mysql server supports the following types of triggers

  • Data Modification Language (DML) triggers
  • Data Definition Language (DDL) triggers

DML Triggers

A DML trigger is fired when data in the underlying table is affected by DML statements such as INSERT ,UPDATE , or DELETE. These triggers help in maintaining consistent, reliable  and correct data in tables

The DML triggers have the following characteristics

  • Fired automatically by server whenever any data modification statement is issued.
  • Cannot be ex[licity invoked or executed as in the case of the stored procedure.  
  • Prevents incorrect, unauthorized and inconsistent changes in data.
  • Cannot return data to the user.

Depending on the operation that is performed the DML triggers can be further categorized as :

  • Insert Trigger :

Is fired whenever an attempt is made to insert a row in the trigger table. When an INSERT statement is executed a new row is added to both the trigger and the inserted tables.

  • Delete Trigger :

Is fired whenever an attempt is made to delete a row from the trigger table. When a DELETE statement is executed the specified rows from the trigger table are deleted and are added to the deleted table .

  • Update trigger

Is fired when UPDATE statement is executed in the trigger table . It uses two logical tables for its operations the deleted tables that contains the original rows and the inserted table that stores the new rows. After all the rows are updated, the deleted and inserted tables are populated and trigger is fired.

DDL Triggers

A DDL trigger is fired in response to DDL statements such as CREATE TABLE or ALTER TABLE. DDl triggers can be used to perform administrative tasks such as database auditing. Database auditing helps in monitoring DDL operations on a database. DDL operation can include operations such as creation of a table or view or modification of table or procedure.

Creating Triggers

To define a trigger for table use the CREATE TRIGGER statement

Triggers Events

Triggers are associated with individual tables. The methods for activating trigger is called “Events”

 

  • AFTER : These types of events are based on activation times that takes place after changes to the data in the table are written to the underlying database. These types of events can be used for logging or auditing the modification of data within the database. There are three activation events associated with AFTER activation.

 

 

Restriction on Triggers

 

  • Each table can have only one trigger for each timing/event combination (ie: you can’t define two BEFORE INSERT triggers for the same table).
  • Triggers are always executed for each row. The standard FOR EACH STATEMENT option is not supported in MariaDB,
  • Triggers cannot operate on any tables in the mysql, information_schema or performance_schema database.
  • Cannot return a resultset.
  • The RETURN statement is not permitted, since triggers don’t return any values. Use LEAVE to immediately exit a trigger.
  • Triggers are not activated by foreign key actions.

Dropping triggers

To drop a trigger, use the DROP TRIGGER statement. Triggers are also dropped if the table with which they are associated is also dropped.

0 Shares

Rasmi Ranjan

A tech guy who loves to learn code. I'm open source enthusiast and a delicious coffee maker

12 Comments

Leave a Reply

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