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

    Unanswered: Trigger not working properlt

    I have two table. Based one 1st table data, second table will be inserted data.
    Two table are like this...



    CREATE TABLE IF NOT EXISTS `tb1` (
    `FLAT_NO` varchar(6) NOT NULL ,
    `RECEIPT_DATE` date NOT NULL ,
    `MODE_NAME` varchar(10) NOT NULL COMMENT 'Payment mode',
    `REFERENCE_NO` varchar(15) NOT NULL,
    `AMOUNT` int(5) NOT NULL,
    `RECEIPT_NO` int(3) NOT NULL,
    PRIMARY KEY (`FLAT_NO`,`RECEIPT_DATE`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS `tb2` (
    `RECEIPT_ID` int(11) NOT NULL AUTO_INCREMENT,
    `RECEIPT_DATE` date NOT NULL,
    `PARTICULARS` varchar(30) NOT NULL,
    `RECEIPT_NO` int(3) NOT NULL,
    `CASH_BOX` int(5) NOT NULL,
    `BANK` int(5) NOT NULL,
    PRIMARY KEY (`RECEIPT_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

    Now triggers ----

    DROP TRIGGER IF EXISTS `bi_tb1`;
    DELIMITER //
    CREATE TRIGGER `bi_tb1` BEFORE INSERT ON `tb1`
    FOR EACH ROW BEGIN
    IF 'NEW.MODE_NAME' = 'CASH' THEN
    INSERT INTO tb2 (receipt_date,particulars,receipt_no,cash_box) values (NEW.receipt_date,NEW.flat_no,NEW.receipt_no,NEW.a mount);

    ELSEIF 'NEW.MODE_NAME' = 'CHEQUE' THEN
    INSERT INTO tb2 (receipt_date,particulars,receipt_no,bank) values (NEW.receipt_date,NEW.flat_no,NEW.receipt_no,NEW.a mount);
    END IF;
    END
    //
    DELIMITER ;


    when I am inserting data into tab1 as follows...
    INSERT INTO `tb1` (`FLAT_NO`, `RECEIPT_DATE`, `MODE_NAME`, `REFERENCE_NO`, `AMOUNT`, `RECEIPT_NO`, `REMARKS`) VALUES ('1/13', '2012-07-02', 'cash', '0000', 1400, 11, '');


    I am getting no error while mysql shows query ok, warning 1..
    and select * from tb2 shows nothing...
    please help what's the wrong with trigger ?

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by shersaha View Post
    I am getting no error while mysql shows query ok, warning 1..
    Enter 'show warnings' command, since you are getting warnings, and see what is a warning reason.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Look carefully at your IF statements:

    IF 'NEW.MODE_NAME' = 'CASH' THEN

    I think this should read

    IF NEW.MODE_NAME = 'CASH' THEN
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Sep 2007
    Posts
    18
    Quote Originally Posted by it-iss.com View Post
    Look carefully at your IF statements:

    IF 'NEW.MODE_NAME' = 'CASH' THEN

    I think this should read

    IF NEW.MODE_NAME = 'CASH' THEN
    your are right, without quote, it working fine. thanks a lot...

Posting Permissions

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