[adsense id=”0514458240″ width=”468″ height=”60″]
Reports sometimes need runtime values to be provided. Imagine a bank account requiring a balance field to report the current balance of the account. There are two ways in which this can be performed:
- Using triggers to add the runtime value
- Using session variables to store a runtime value
There are pro’s and con’s to both, primarily in the area of performance. If this information is accessed many times then we would need to recalculate each time the run time values then it may be better to store these values.
There are also conditions to be respected. We can only ever INSERT new values, we can never UPDATE or DELETE existing records. Updating or deleting would require a recalculation of the entire table.
For our example, let us imagine we want to keep a running total of a bank:
CREATE TABLE IF NOT EXISTS `bank` ( `BANK_ID` int(3) NOT NULL AUTO_INCREMENT, `T_DATE` date NOT NULL, `PARTICULAR` varchar(25) DEFAULT NULL, `DEPOSIT` int(6) NOT NULL DEFAULT '0', `WITHDRAWAL` int(6) NOT NULL DEFAULT '0', `BALANCE` int(6) NOT NULL DEFAULT '0', PRIMARY KEY (`BANK_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Trigger
We will only ever add to this table. The balance is calculated based on the previous records balance with deposit added and withdrawal subtracted.
Our trigger will look something like this:
DELIMITER $$ CREATE TRIGGER bank_trig01 BEFORE INSERT ON bank FOR EACH ROW BEGIN DECLARE lBalance INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET lBalance = 0; SELECT BALANCE INTO lBalance FROM bank WHERE BANK_ID = (SELECT MAX(BANK_ID) FROM bank); SET NEW.BALANCE = lBalance + NEW.DEPOSIT - NEW.WITHDRAWL; END $$ DELIMITER ;
If you see the CONTINUE HANDLER FOR NOT FOUND if we are adding the very first record into the table then the balance will be 0.
Now let’s have a look at this in action:
mysql> insert into bank(t_date, particular, deposit, withdrawal) values (now(), 'test', 100, 0); Query OK, 1 row affected (0.01 sec) mysql> select * from bank; +---------+------------+------------+---------+-----------+---------+ | BANK_ID | T_DATE | PARTICULAR | DEPOSIT | WITHDRAWAL| BALANCE | +---------+------------+------------+---------+-----------+---------+ | 1 | 2012-07-19 | test | 100 | 0 | 100 | +---------+------------+------------+---------+-----------+---------+ 1 row in set (0.00 sec) mysql> insert into bank(t_date, particular, deposit, withdrawal) values (now(), 'test2', 0, 50); Query OK, 1 row affected (0.00 sec) mysql> select * from bank; +---------+------------+------------+---------+-----------+---------+ | BANK_ID | T_DATE | PARTICULAR | DEPOSIT | WITHDRAWAL| BALANCE | +---------+------------+------------+---------+-----------+---------+ | 1 | 2012-07-19 | test | 100 | 0 | 100 | | 2 | 2012-07-19 | test2 | 0 | 50 | 50 | +---------+------------+------------+---------+-----------+---------+ 2 rows in set (0.00 sec) mysql> insert into bank(t_date, particular, deposit, withdrawal) values (now(), 'test2', 0, 49); Query OK, 1 row affected (0.00 sec) mysql> select * from bank; +---------+------------+------------+---------+-----------+---------+ | BANK_ID | T_DATE | PARTICULAR | DEPOSIT | WITHDRAWAL| BALANCE | +---------+------------+------------+---------+-----------+---------+ | 1 | 2012-07-19 | test | 100 | 0 | 100 | | 2 | 2012-07-19 | test2 | 0 | 50 | 50 | | 3 | 2012-07-19 | test2 | 0 | 49 | 1 | +---------+------------+------------+---------+-----------+---------+ 3 rows in set (0.00 sec) mysql> insert into bank(t_date, particular, deposit, withdrawal) values (now(), 'test2', 34, 0); Query OK, 1 row affected (0.00 sec) mysql> select * from bank; +---------+------------+------------+---------+-----------+---------+ | BANK_ID | T_DATE | PARTICULAR | DEPOSIT | WITHDRAWAL| BALANCE | +---------+------------+------------+---------+-----------+---------+ | 1 | 2012-07-19 | test | 100 | 0 | 100 | | 2 | 2012-07-19 | test2 | 0 | 50 | 50 | | 3 | 2012-07-19 | test2 | 0 | 49 | 1 | | 4 | 2012-07-19 | test2 | 34 | 0 | 35 | +---------+------------+------------+---------+-----------+---------+ 4 rows in set (0.00 sec)
As we can see this is keeps the running total of balance up to date for each record. Now let’s have a look at doing the same thing but calculating the running total rather than storing it.
Session variables
Session variables are very powerful in MySQL and allow us to perform more dynamic operations on the data being reported back from the database. Here are a few links to other white papers showing what session variables can be used for:
- Refreshing id values in a table
- Reversing two column values in an update statement
- Counting repetitions of values
In our case what we want to do is calculate the runtime balance. In our example we will generate a column called runtime which will be displayed alongside the balance calculated with the trigger above to show that there is a match between both:
mysql> set @balance := 0; Query OK, 0 rows affected (0.01 sec) mysql> select bank_id, t_date, particular, deposit, withdrawal, balance, @balance := @balance + deposit - withdrawal as runtime from bank order by bank_id; +---------+------------+------------+---------+-----------+---------+---------+ | bank_id | t_date | particular | deposit | withdrawal | balance | runtime | +---------+------------+------------+---------+-----------+---------+---------+ | 1 | 2012-07-19 | test | 100 | 0 | 100 | 100 | | 2 | 2012-07-19 | test2 | 0 | 50 | 50 | 50 | | 3 | 2012-07-19 | test2 | 0 | 49 | 1 | 1 | | 4 | 2012-07-19 | test2 | 34 | 0 | 35 | 35 | +---------+------------+------------+---------+-----------+---------+---------+ 4 rows in set (0.00 sec)
In this case here we are starting off with a balance of 0 (SET @balance = 0;). At each row we are calculating the balance based on the DEPOSIT and WITHDRAWAL amounts (adding deposits and removing withdrawals). This is performed using the code (@balance := @balance + deposit – withdrawal). We are referring to the @balance value that is keeping track of the runtime value.