Wednesday 9 April 2014

Information on Triggers



Trigger:
It is a named database object that is associated with a table.
Database Trigger or trigger is a stored program that is executed automatically to respond to a specific event associated with table e.g., insert, update or delete.

Reason to use Trigger:
Provides a means to execute an SQL statements or set of statements when we insert, update or delete rows in a table.
  1. Trigger can examine row values to be inserted or updated and it can determine what values were deleted or what they were updated to.
  2. A trigger can change values before they were inserted into a table or used to update a table.

Trigger Concepts:
It’s an object which belongs to a database.
Each trigger within the database should have a different name.
A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table.

Creating Triggers:
Syntax:

CREATE
TRIGGER trigger_name
trigger_time
trigger_event
ON tbl_name
FOR EACH ROW
trigger_statement

trigger_time: {BEFORE | AFTER}
trigger_event: {INSERT | UPDATE | DELETE}
trigger_time : is the trigger action time. It can be BEFORE or AFTER to indicate that   
                       the trigger activates before or after each row to be modified.
trigger_event indicates the kind of operation that activates the trigger.
tbl_name : indicates table name on which trigger to be created

These trigger_event values are permitted.

INSERT: The trigger activates whenever a new row is inserted into the table;

UPDATE: The trigger activates whenever a row is modified;

DELETE: The trigger activates whenever a row is deleted from the table;


Dropping Trigger :

Syntax:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name


How to Create a trigger? 
 

First let's create a table and we can create a trigger in that particular table. 
Here i have used `test` as the database and table will be `City` from `world` table.

You can get world database here:

mysql> create table City like world.city;
Query OK, 0 rows affected (0.09 sec)

You can check your table

 

Now we will insert some records (say 50) to the test.City table from world.City table.


We will now create a trigger for update. In this it will record the deleted data's to a mentioned table.
For that we should create a table as,

mysql> create table City_deleted_records (old_id int(11),old_Name char(35),old_CountryCode char(3),old_District char(15),old_Population int(11),deleted_time time);

Query OK, 0 rows affected (0.19 sec)

The above table will be recorded with deleted data's.

Now we will create a trigger, as we need to enter multiple statements we will start the trigger with delimiter as shown in example;



In this trigger if we delete a record from test.City table that deleted record will be inserted into test.City_deleted_records table.

If you want to check the created trigger  you can view by show command,

mysql> show triggers\G


Now we will delete a record from City table;


mysql> delete from City where ID=25;

Query OK, 1 row affected (0.08 sec)


The above query will delete the record with ID 25 from the City table

Now we need to check it in the City_deleted_records table.

select * from City_deleted_records;


Here, we can see the deleted record from City table with time when it was deleted.