Need help in writting trigger

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.

 

 

Answer 1

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

Answer 2

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

Answer 3

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

 

Answer 4

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