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

    Unanswered: mysql trigger error : Before insert

    before trigger is not working-- Please help.

    I have two table- table1,table2

    table1
    ------

    CREATE TABLE IF NOT EXISTS `table1` (
    `FLAT_NO` varchar(6) NOT NULL,
    `SAPTAMI` int(1) NOT NULL,
    `ASTAMI` int(1) NOT NULL,
    `NABAMI` int(1) NOT NULL,
    `DASHMI` int(1) NOT NULL,
    `TOTAL` int(4) NOT NULL,
    PRIMARY KEY (`FLAT_NO`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    table2:
    --------

    CREATE TABLE IF NOT EXISTS `table2` (
    `RATE_ID` int(11) NOT NULL AUTO_INCREMENT,
    `RATE_NAME` varchar(25) NOT NULL,
    `RATE_VALUE` int(4) NOT NULL,
    PRIMARY KEY (`RATE_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    INSERT INTO `table2` (`RATE_ID`, `RATE_NAME`, `RATE_VALUE`) VALUES
    (1, 'GUEST', 130);

    now trigger :

    DROP TRIGGER IF EXISTS `table1`;
    DELIMITER //
    CREATE TRIGGER `bi_table1` BEFORE INSERT ON `table1`
    FOR EACH ROW BEGIN
    DECLARE value INTEGER;
    select RATE_VALUE into value from table2 where rate_name = 'guest';
    INSERT INTO
    table1
    SET NEW.total= (new.saptami+new.astami+new.nabami+new.dashmi)*val ue ;
    end
    //
    DELIMITER ;

    This trigger is not working. could not find the proble.please help..
    Thanks in advance
    Last edited by shersaha; 06-24-12 at 14:26. Reason: correction

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i am not familiar with the "not working" error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    sorry, error messge is not "not working".

    error message is- ERROR 1442<HY000>: Can't update table table1 in stored function /trigger
    because it is already used by statement which invoke this stored function/trigger.

    Now I think it is clear. thanks in advance.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, yes, it is clear now

    you are trying to update a column which is calculated as a sum of other column values on the same row

    the more appropriate approach is not to actually store the sum as a separate column, but to calculate the sum whenever you need it

    if necessary, you can declare a view which has the sum built in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You cannot issue a new INSERT statement back into table1 because this will cause an infinite loop. You insert a value inside the trigger and this causes a new call to the trigger code etc.

    Looking at your code, all you need to do is to remove the INSERT statement as follows (SET NEW.TOTAL will assign the value which will be assigned to the TOTAL field during the INSERT):

    Code:
    DELIMITER //
    CREATE TRIGGER `bi_table1` BEFORE INSERT ON `table1`
    FOR EACH ROW BEGIN
    DECLARE value INTEGER;
    select RATE_VALUE into value from table2 where rate_name = 'guest';
    SET NEW.total= (new.saptami+new.astami+new.nabami+new.dashmi)*value;
    end
    //
    DELIMITER ;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Sep 2007
    Posts
    18
    thanks to all who have helped me. Specially it-iss.com, its working for me . thanks again.

Posting Permissions

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