Asked by:
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.
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 belowCREATE 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