Re: How can I convert this Postgres stored procedure to MYSQL

[Jon Bowlas]

I managed to resolve the issue with the trg_ins_dept trigger it was a problem with the version of PHPMyAdmin we're using here.

However I have another issue, with the 2nd trigger 'trg_upd_dept', which returns the error: 'Not allowed to return a result set from a trigger', I've checked the forums and apparently this is true you're not allowed to return the results set, that is unless I put the result in a variable with the SELECT INTO syntax. So would this be correct?

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)

The error returned from this is that OLD.id isn't in the field list, which I thought might occur, although I just assumed that OLD.etc, NEW.etc was some sort of mySQL convention. So I'm guessing I'm gonna have to replace all instances of OLD. with orig_ and NEW. with new_ reflecting my database schema, is this correct?

Jon