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.