MySQL Forums
Forum List  »  Triggers

Trigger Problem,Help me out please
Posted by: Binu Mathew
Date: March 05, 2011 06:50PM

I have two tables with one named att as follows

Field Type Null Key Default Extra
SID varchar(50) NO
CID varchar(50) NO
Date date NO NULL
H1 varchar(1) NO 0
H2 varchar(1) NO 0
H3 varchar(1) NO 0
H4 varchar(1) NO 0
H5 varchar(1) NO 0
H6 varchar(1) NO 0
H7 varchar(1) NO 0
H8 varchar(1) NO 0
H9 varchar(1) NO 0
H10 varchar(1) NO 0

The other table is per with following fields:
SID CID Per

How do I write a trigger for the following:
If an update occurs in any of the fields from h1-h0 on att table then
update the per column in the per table with the following values:
((total no of 1s - total no of 0s)/(total no of 1s + total no of 0s))/100

Thanks in advance
i Developed a Trigger,but it not working,its saying error in line 11,can you say what is the problem??

create TRIGGER `att_up` AFTER UPDATE ON `attentance`
FOR EACH ROW BEGIN
DECLARE Zeros INT;
DECLARE Ones INT;
DECLARE total INT;
DECLARE atted FLOAT;
SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8)) INTO Zeros FROM attentance WHERE StudID=NEW.StudID;
SELECT SUM(h1+h2+h3+h4+h5+h6+h7+h8) INTO Ones FROM attentance WHERE StudID=NEW.StudID;
SELECT SUM(8-(h1+h2+h3+h4+h5+h6+h7+h8))+ SUM(h1+h2+h3+h4+h5+h6+h7+h8) INTO total FROM attentance WHERE StudID=NEW.StudID;
set atted=((ZEROS-Ones)/total)/100;
INSERT into per(per) values (atted);
END$$



Edited 1 time(s). Last edit at 03/05/2011 07:05PM by Binu Mathew.

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger Problem,Help me out please
2071
March 05, 2011 06:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.