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

[Jon Bowlas]

Hi Jay,

Just for clarification, I have the following fields in my database that will all need updating with this trigger:

DEPTCODE char(2)
DEPTNAME varchar(100)
STANDFIRST mediumtext
DEPTINTRO mediumtext
AFFILTUTOR varchar(100)
OTHERCONTACTTITLE varchar(50)
OTHERCONTACTNAME varchar(100)
DEPTEMAIL varchar(50)
ADMISSION varchar(40)
DEPTURL varchar(100)
ENDTEXT mediumtext
DEPTPREFIX varchar(50)
DEPTSUFFIX varchar(50)

So would I adapt your scripts so they're like this?:

CREATE TABLE objects (
object_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, DEPTCODE CHAR(2) NOT NULL
, DEPTNAME varchar(100) NOT NULL
, STANDFIRST mediumtext
, DEPTINTRO mediumtext
, AFFILTUTOR varchar(100)
, OTHERCONTACTTITLE varchar(50)
, OTHERCONTACTNAME varchar(100)
, DEPTEMAIL varchar(50)
, ADMISSION varchar(40)
, DEPTURL varchar(100)
, ENDTEXT mediumtext
, DEPTPREFIX varchar(50)
, DEPTSUFFIX varchar(50)
) ENGINE=INNODB;

CREATE TABLE object_deltas (
object_id INT UNSIGNED NOT NULL
, version INT UNSIGNED NOT NULL
, orig_DEPTCODE CHAR(2) NOT NULL
, orig_DEPTNAME varchar(100) NOT NULL
, orig_STANDFIRST mediumtext
, orig_DEPTINTRO mediumtext
, orig_AFFILTUTOR varchar(100)
, orig_OTHERCONTACTTITLE varchar(50)
, orig_OTHERCONTACTNAME varchar(100)
, orig_DEPTEMAIL varchar(50)
, orig_ADMISSION varchar(40)
, orig_DEPTURL varchar(100)
, orig_ENDTEXT mediumtext
, orig_DEPTPREFIX varchar(50)
, orig_DEPTSUFFIX varchar(50)
, new_DEPTCODE CHAR(2) NOT NULL
, new_DEPTNAME varchar(100) NOT NULL
, new_STANDFIRST mediumtext
, new_DEPTINTRO mediumtext
, new_AFFILTUTOR varchar(100)
, new_OTHERCONTACTTITLE varchar(50)
, new_OTHERCONTACTNAME varchar(100)
, new_DEPTEMAIL varchar(50)
, new_ADMISSION varchar(40)
, new_DEPTURL varchar(100)
, new_ENDTEXT mediumtext
, new_DEPTPREFIX varchar(50)
, new_DEPTSUFFIX varchar(50)
, PRIMARY pk_objectdelta (object_id, version)
, FOREIGN KEY (object_id) REFERENCES objects (object_id)
) ENGINE=INNODB;

DELIMITER //

CREATE TRIGGER trg_ins_object
AFTER INSERT ON objects
FOR EACH ROW
BEGIN
INSERT INTO object_deltas (object_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 (NEW.object_id, 0, '', NEW.DEPTCODE, '', NEW.DEPTNAME, '', NEW.STANDFIRST, '', NEW.DEPTINTRO, '', NEW.AFFILTUTOR, '', NEW.OTHERCONTACTTITLE, '', NEW.OTHERCONTACTNAME, '', NEW.DEPTEMAIL, '', NEW.ADMISSION, '', NEW.DEPTURL, '', NEW.ENDTEXT, '', NEW.DEPTPREFIX, '', NEW.DEPTSUFFIX);
END //

CREATE TRIGGER trg_upd_object
AFTER UPDATE ON objects
FOR EACH ROW
BEGIN
SELECT @new_version := MAX(version) + 1
FROM objects
WHERE object_id = OLD.object_id;
INSERT INTO object_deltas (object_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.object_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);
END //



Edited 1 time(s). Last edit at 02/16/2006 05:39AM by Jon Bowlas.