locked
Need help in writting trigger RRS feed

  • Question

  • User-2030397957 posted

    Hello All Forum member....

    I have an issue which I need help to solve..

    I have two table

    TestMST and TestElement

    In TestMST Table I have tree Column Named as ID,Name,IDCount

    And In TestElement table there are column having ID,FKID(refence key of ID in TestMST table )

     

    In TestElement table will contain Multiple entry of ID of TestMst table,

     

    Now I want to keep count of ID in TestElementTable

    Suppose there is 5 entry for ID =1

    then in TestMst table I need value like

    1, Name1, 5

    how to get that result using trigger.....

    So that After completion of insert record in TestElemt Table , IDCount in TestMst should get updated.

     

     

    Wednesday, March 30, 2011 12:44 AM

All replies

  • User-2005691517 posted

    Create an after insert trigger on the child table to update the count in master table

    Something like

    Update TestMST set IDCount = (Select count(*) from TestElement where FKID=:NEW.FKID)

    where ID = :NEW.FKID

    Wednesday, March 30, 2011 1:53 AM
  • User-2030397957 posted

    Hi

    Thanks for your quick reply...

    I did the same think before...But I was getting the systax error..I am using MySql 5.0

    Wednesday, March 30, 2011 2:15 AM
  • User-2030397957 posted

    My trigger is

     

    DELIMITER $$
    CREATE
        /*[DEFINER = { user | CURRENT_USER }]*/
        TRIGGER `initialtest`.`trg_Update_GyanTest` AFTER INSERT
        ON Gyan_Test_audit
        FOR EACH ROW BEGIN
    	UPDATE Gyan_Test SET RuleCount = (SELECT COUNT(*) FROM Gyan_Test_audit WHERE Fk_ID=NEW.Fk_ID)
    	WHERE ID = NEW.Fk_ID
        END$$
    DELIMITER ;

    script for the two table in which Iam writting trigger is given below
    CREATE TABLE `Gyan_Test` (
      `ID` INT(11) NOT NULL,
      `RuleName` VARCHAR(50) NOT NULL,
      `RuleCount` INT(11) NOT NULL DEFAULT '0',
      `Sal` DECIMAL(10,0) DEFAULT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1
    
    CREATE TABLE `Gyan_Test_audit` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Fk_ID` int(11) NOT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

     

    Wednesday, March 30, 2011 2:15 AM
  • User-2005691517 posted

    I am not familiar with the MySql syntax but you seem to be missing a semicolon at the end of the update statement in the trigger

    Wednesday, March 30, 2011 2:37 AM