MySQL Forums
Forum List  »  Triggers

Triggers with updates
Posted by: sewcookie
Date: June 28, 2006 08:59AM

Table: registration
sessionID
studentID
registered field (yes/No)

When a new student is added to the registration table,
I want to update the session.numstudents field with the total count. I have tried the following and keep getting errors around the update. If I do a straight update with a set session id, it works, but not with the parameterized update or in either a procedure or a trigger.

CREATE TRIGGER stdcnt
AFTER UPDATE ON registration
FOR EACH ROW BEGIN
UPDATE session SET numstudents=(SELECT count(*) from registration where sessionId=NEW.sessionId) WHERE sessionId=NEW.sessionId;
END;

CREATE PROCEDURE countstudents( sid int,cnt int)
BEGIN
SET cnt = SELECT count(*) from registration where sessionId=sid;
UPDATE session SET numstudents= cnt WHERE sessionId=sid ;
END;

Options: ReplyQuote


Subject
Views
Written By
Posted
Triggers with updates
2524
June 28, 2006 08:59AM


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.