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.