Author: riteshkamal

Introduction
MySQL Triggers are one of the newer features in MySQL that are helping to make it a viable alternative for large enterprise applications. So, what are MySQL triggers, and why does MySQL's ability to use them make it more attractive to serious database users?

Simply put, triggers are small programs that are stored in the database itself, and are activated by database events which often originate at the application layer. These precipitating database events are UPDATE, DELETE or INSERT queries.

The trigger itself may execute before or after the query that initiates it. Triggers are often used to maintain the integrity of data across tables of an application.
When a user on a website makes a purchase, for example, the first action that occurs in the database may be that a credit is inserted into an accounting table. By way of a trigger this action could initiate a chain reaction of events in other tables throughout the application. The product count of an item could be decremented in an inventory table, a debit deducted from a customer's account balance in another table, a store credit applied to yet another table.

Why to use procedures

You may say that you have been doing this all along in your applications using PHP or Perl or Python or ASP code. What's the big deal about using MySQL triggers? Well, there are some advantages to using triggers over application code for maintaining integrity of data across tables.

A trigger generally performs the types of tasks described faster than application code, and and can be activated easily and quickly behind the scenes and does not need to be a part of your application code. This saves time and spares you from redundant coding. If you ever port your application to another language, chances are your triggers can stay in place without modification, along with your tables and other database objects.

Explanation

To demonstrate how MySQL triggers work, let's set up two simple tables on a database we'll call “sales_records” that have data that is interdependent. Imagine a database that tracks the sales records of three salespeople at a department store.
They work in the electronics department selling things like TVs , stereos, and MP3 players. We have the main table that keeps a record of each sale made. It records the amount of the sale (sale_amt), the date (date), the name of the salesman (name), his id number (employee_id), and the product id (prod_id). We'll call this table (cleverly enough) “sales”.

In the second table, we want to keep some data that will allow us to easily keep track of how each salesperson is doing. It will include the salesperson's id (employee_id), name (name), total number of sales (total_sales), and a column that keeps each salesperson's average amount per sale (ave_sale). We want to see who's moving the high-end items. We'll call this table “performance”.

How to use Triggers and Procedures

Now comes the hard part. As I mentioned, triggers are database objects just as tables are. Triggers, however, are able to execute procedural code that modifies data in your tables. In this case, we want our trigger to fire before any INSERT statement that executes in the sales table. When a sale record is inserted in the sales table, the salesperson's totals must be updated in the performance table. The following code can be typed in your favorite text editor and pasted into your console at the MySQL prompt.

Before you do that though, you want to execute this line: mysql: Delimiter $$ Our procedural code uses semicolons at the end of statements, so we need to set a different delimiter to let MySQL know when our code block is over, and so that it doesn't stop processing our block when it hits a semicolon. Keep in mind that after you finish your block you will have to set the delimiter back to the semicolon, or end any subsequent commands with the new delimiter.

For example if you made errors in your CREATE TRIGGER block and want to delete it, DROP TRIGGER; won't work unless you set the delimiter back to the semicolon. Here is the code for the trigger: OK, let's talk about the code.

• Using the CREATE TRIGGER statement, we've initiated the trigger, naming it 'sales_bi_trg'. MySQL triggers can fire before or after an INSERT, UPDATE or DELETE event.
• This one fires before any data is inserted in the 'sales' table. The FOR EACH ROW clause signifies that the block will act on each row that meets the criteria of our SQL statements.
• The keywords BEGIN and END enclose the trigger statements that will execute when the trigger fires.
• There are two variables declared. The first is 'num_row' which checks to see if the employee has who has made the sale that is to be entered, has had a sale entered in the performance table previously. If there are no employee_id's that match, then this is the employee's first sale, and this meets the 'ELSE' condition of our “IF' statement. This data will be entered as an insert in the performance table rather than an update. If the 'num_row' is greater than 0, then the performance table will be updated. The second variable, 'tot_rows', is a count of how many sales the employee has in the 'sales' table. This value is used to calculate the employee's average sale.
• The count is being done before the sale is inserted in the sale table, so we have to add one to it. When the 'performance' table is updated the average sale = total_sales/(tot_rows+1).
• If our MySQL trigger is working correctly, the 'performance' table will keep a running total of each salespersons total sales, and also the average amount of their total sales. It will do this independently of your application code and be portable to any application platform. To give it a whirl, insert some data into the 'sales' table and monitor the content of the 'performance' table. Here is the statement: Change the numbers and names and try it a few times. (Remember, an employee keeps the same employee_id number for each of his sales.)

If you're feeling adventurous, start thinking about how the MySQL trigger would have to be extended to account for UPDATE and DELETE statements on the 'sales' table. Another eg :

create trigger tb1_bi_trg before insert on tb1 for each row begin declare rnk int;
select new.rank into rnk;
if rnk<=4 then insert into tb2 set salary=5000;
else insert into tb2 set salary=2000;
end if;
end
create view v as select rank,name,marks*rank as income from tb1;

Another eg :
CREATE TABLE Employee1( id int, first_name VARCHAR(30), last_name VARCHAR(15), start_date  DATE, end_date      DATE, city  VARCHAR(10), description   VARCHAR(15) );

insert into Employee1 values (01,'Girish','Tewari','20081225',  '20100625','Nainital','Programmer');

insert into Employee1 values (02,'Komal','Choudhry','20071122', '20100421','Meerut','Programmer');

insert into Employee1 values (03,'Mahendra','Singh','20061012',  '20070512','Lucknow','Programmer');

select * from employee1;

CREATE TABLE Employee_log( user_id       VARCHAR(15), description   VARCHAR(100) );

CREATE TRIGGER Employee_Trigger AFTER UPDATE ON employee1
                 FOR EACH ROW BEGIN INSERT into Employee_log (user_id, description)
                VALUES (user(), CONCAT('Id with ',NEW.id,' is modified ', ' from ',OLD.start_date, ' to ', NEW.start_date));
END$$

update employee1 set start_date='20061231';
PROCEDURES : delimiter // create procedure procedure_name begin select * from table_name; end delimiter ;

Now call the procedure call procedure_name(); it will shows all the contents of the table table_name. declaring variable in a procedure : After "begin" just write the followings declare variable_name datatype(size) default default_value; eg: declare x int default 0 we can also declare more than 1 variables at a time declare x,y int default 0

Assigning variables
declare total_count int default 0 set total_count = 10;
declare total_products int default 0 select count(*) into total_products from products;

In mysql console you can see what you have written in the procedure i.e you can see the code of the procedure by using :

select body, definer, (and/or any other column available) from mysql.proc where name='your_procedure'