MySQL Forums
Forum List  »  Triggers

Update Trigger problem
Posted by: Jon Bowlas
Date: February 22, 2006 06:03AM

Hi All,

I have a slight problem with an update trigger which returns the error 'Not allowed to return a result set from a trigger':

CREATE TRIGGER trg_upd_dept
AFTER UPDATE ON DEPT
FOR EACH ROW
SELECT @new_version := MAX(version) + 1
FROM DEPT
WHERE id = OLD.id;
INSERT INTO DEPT_deltas (id, version, orig_DEPTCODE, new_DEPTCODE,
orig_DEPTNAME, new_DEPTNAME, orig_STANDFIRST, new_STANDFIRST, orig_DEPTINTRO,
new_DEPTINTRO, orig_AFFILTUTOR, new_AFFILTUTOR, orig_OTHERCONTACTTITLE,
new_OTHERCONTACTTITLE, orig_OTHERCONTACTNAME, new_OTHERCONTACTNAME,
orig_DEPTEMAIL, new_DEPTEMAIL, orig_ADMISSION, new_ADMISSION, orig_DEPTURL,
new_DEPTURL, orig_ENDTEXT, new_ENDTEXT, orig_DEPTPREFIX, new_DEPTPREFIX,
orig_DEPTSUFFIX, new_DEPTSUFFIX)
VALUES (OLD.id, @new_version, OLD.DEPTCODE, NEW.DEPTCODE, OLD.DEPTNAME,
NEW.DEPTNAME, OLD.STANDFIRST, NEW.STANDFIRST, OLD.DEPTINTRO, NEW.DEPTINTRO,
OLD.AFFILTUTOR, NEW.AFFILTUTOR, OLD.OTHERCONTACTTITLE, NEW.OTHERCONTACTTITLE,
OLD.OTHERCONTACTNAME, NEW.OTHERCONTACTNAME, OLD.DEPTEMAIL, NEW.DEPTEMAIL,
OLD.ADMISSION, NEW.ADMISSION, OLD.DEPTURL, NEW.DEPTURL, OLD.ENDTEXT,
NEW.ENDTEXT, OLD.DEPTPREFIX, NEW.DEPTPREFIX, OLD.DEPTSUFFIX, NEW.DEPTSUFFIX)

So having trawled through the forums I discovered that triggers do not allow returning the results set, that is unless I put the result in a variable with the SELECT INTO syntax.

So I revised my trigger to the following:

CREATE TRIGGER trg_upd_dept
AFTER UPDATE ON DEPT
FOR EACH ROW
SELECT MAX(version)+1 INTO @new_version FROM DEPT where id = OLD.id;
INSERT INTO DEPT_deltas (id, version, orig_DEPTCODE, new_DEPTCODE, orig_DEPTNAME, new_DEPTNAME, orig_STANDFIRST, new_STANDFIRST, orig_DEPTINTRO, new_DEPTINTRO, orig_AFFILTUTOR, new_AFFILTUTOR, orig_OTHERCONTACTTITLE, new_OTHERCONTACTTITLE, orig_OTHERCONTACTNAME, new_OTHERCONTACTNAME, orig_DEPTEMAIL, new_DEPTEMAIL, orig_ADMISSION, new_ADMISSION, orig_DEPTURL, new_DEPTURL, orig_ENDTEXT, new_ENDTEXT, orig_DEPTPREFIX, new_DEPTPREFIX, orig_DEPTSUFFIX, new_DEPTSUFFIX) VALUES (OLD.id, @new_version, OLD.DEPTCODE, NEW.DEPTCODE, OLD.DEPTNAME, NEW.DEPTNAME, OLD.STANDFIRST, NEW.STANDFIRST, OLD.DEPTINTRO, NEW.DEPTINTRO, OLD.AFFILTUTOR, NEW.AFFILTUTOR, OLD.OTHERCONTACTTITLE, NEW.OTHERCONTACTTITLE, OLD.OTHERCONTACTNAME, NEW.OTHERCONTACTNAME, OLD.DEPTEMAIL, NEW.DEPTEMAIL, OLD.ADMISSION, NEW.ADMISSION, OLD.DEPTURL, NEW.DEPTURL, OLD.ENDTEXT, NEW.ENDTEXT, OLD.DEPTPREFIX, NEW.DEPTPREFIX, OLD.DEPTSUFFIX, NEW.DEPTSUFFIX)

Only now it returns the error that 'OLD.id isn't in the field list', so I'd really appreciate it if someone could point out the glaring error I've made so I can get this to work.

Many thanks

Jon



Edited 1 time(s). Last edit at 02/22/2006 06:03AM by Jon Bowlas.

Options: ReplyQuote


Subject
Views
Written By
Posted
Update Trigger problem
2742
February 22, 2006 06:03AM
1656
February 24, 2006 08:37AM


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.