Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: Table Help how to configure

    I have table like this-

    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',
    `WITHDRAWL` 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 ;


    where balance is calculated value. it will be calculated as follows-
    balance = balance +deposit or balance-withdrawal depending upon whether it is deposited or withdrawn. Deposit or withdrawal can happen multiple times in a day . whether it will be easy using trigger or any other way. Please help.
    Thanks .

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    As I see it this table will be used to keep track of a running total for each bank_id. As such is there an underlying table that keeps track of each transaction for that bank i.e. each deposit and withdrawal? If this is the case then you could always calculate the running total yourself based on the detailed content. However, in very large systems this can take a while if there are large volumes of data. As a result, having a trigger on the detailed table (i.e. for each deposit and withdrawal) will help with a running total. You will need triggers on INSERT's only. I am assuming as this is a banking solution that deleting and updating entries will not be permitted?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    Thanks ronan for your reply. Sorry if I have given some misinformation. There is no need to keep track of running total for each bank_id. Only I need balance of previous row and add/subtract (deposit/withdral) with that balance for current row (i.e. new row inserted) and it will be new balance. now this will be reference for next balance.. if more elaboration is need , I will give. again thaks in advance......

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Remember that using triggers can only be installed by a user with SUPER privileges. This is one way of doing it but also looking at what you are doing you could also run this with a running total depending on what you are looking to achieve.

    Here is the code below for the trigger

    Code:
    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 ;
    Here it is in action.
    Code:
    mysql> insert into bank(t_date, particular, deposit, withdrawl) values (now(), 'test', 100, 0);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from bank;
    +---------+------------+------------+---------+-----------+---------+
    | BANK_ID | T_DATE     | PARTICULAR | DEPOSIT | WITHDRAWL | BALANCE |
    +---------+------------+------------+---------+-----------+---------+
    |       1 | 2012-07-19 | test       |     100 |         0 |     100 | 
    +---------+------------+------------+---------+-----------+---------+
    1 row in set (0.00 sec)
    
    mysql> insert into bank(t_date, particular, deposit, withdrawl) values (now(), 'test2', 0, 50);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from bank;
    +---------+------------+------------+---------+-----------+---------+
    | BANK_ID | T_DATE     | PARTICULAR | DEPOSIT | WITHDRAWL | 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, withdrawl) values (now(), 'test2', 0, 49);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from bank;
    +---------+------------+------------+---------+-----------+---------+
    | BANK_ID | T_DATE     | PARTICULAR | DEPOSIT | WITHDRAWL | 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, withdrawl) values (now(), 'test2', 34, 0);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from bank;
    +---------+------------+------------+---------+-----------+---------+
    | BANK_ID | T_DATE     | PARTICULAR | DEPOSIT | WITHDRAWL | 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)
    Alternatively with a session variable we could have the following where the runtime column is calculating at runtime the balance at each row versus the balance column which is populated using the trigger. Both are possible.

    Code:
    mysql> set @balance := 0;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select bank_id, t_date, particular, deposit, withdrawl, balance, @balance := @balance + deposit - withdrawl as runtime from bank order by bank_id;
    +---------+------------+------------+---------+-----------+---------+---------+
    | bank_id | t_date     | particular | deposit | withdrawl | 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)
    I have created a white paper with more explanations here
    Last edited by it-iss.com; 07-19-12 at 14:08.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Sep 2007
    Posts
    18
    Thanks Ronan for quick reply. and you have shown so much interest on the topic. I am pleased to see that you have write one white paper on it. I have follow the white paper also. In white paper you told that "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." I am agree with that. But if I would like to include that feature also then how will I change the trigger or table structure. Your help and elaboration is requested in the regard also.. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •