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

[Jay Pipes]

Let there be light!

Here you go:

CREATE TABLE objects (
object_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, description VARCHAR(200) NOT NULL
, data TEXT NOT NULL
) ENGINE=INNODB;

CREATE TABLE object_deltas (
object_id INT UNSIGNED NOT NULL
, version INT UNSIGNED NOT NULL
, orig_data TEXT NOT NULL
, new_data TEXT NOT NULL
, 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, old_data, new_data)
VALUES (NEW.object_id, 0, '', NEW.data);
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, old_data, new_data)
VALUES (OLD.object_id, @new_version, OLD.data, NEW.data);
END //

Of course, you'd want to adapt the above solution to your personal needs; for instance, you could handle the change of an object_id value of an existing object through a BEFORE UPDATE trigger...

HTH,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com