Witam,
czy ktoś z Was jest w stanie wyczarować odpowiednik kodu w pgsql poniżej dla mysql?
CREATE OR REPLACE FUNCTION process_archive() RETURNS TRIGGER AS $archive$ DECLARE archiveid int; BEGIN IF (TG_OP = 'DELETE') THEN BEGIN INSERT INTO archive (operation, userid, tablename) VALUES(3, lms_current_user(), TG_TABLE_NAME) RETURNING id INTO archiveid; EXCEPTION WHEN OTHERS THEN INSERT INTO archive (operation, userid, tablename) VALUES(3, 0, TG_TABLE_NAME) RETURNING id INTO archiveid; END; EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || '_archive (SELECT $1, $2.*)' USING archiveid, OLD; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN BEGIN INSERT INTO archive (operation, userid, tablename) VALUES(2, lms_current_user(), TG_TABLE_NAME) RETURNING id INTO archiveid; EXCEPTION WHEN OTHERS THEN INSERT INTO archive (operation, userid, tablename) VALUES(2, 0, TG_TABLE_NAME) RETURNING id INTO archiveid; END; EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || '_archive (SELECT $1, $2.*)' USING archiveid, NEW; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN BEGIN INSERT INTO archive (operation, userid, tablename) VALUES(1, lms_current_user(), TG_TABLE_NAME) RETURNING id INTO archiveid; EXCEPTION WHEN OTHERS THEN INSERT INTO archive (operation, userid, tablename) VALUES(1, 0, TG_TABLE_NAME) RETURNING id INTO archiveid; END; EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || '_archive (SELECT $1, $2.*)' USING archiveid, NEW; RETURN NEW; END IF; RETURN NULL; END; $archive$ LANGUAGE plpgsql;
CREATE TRIGGER archive AFTER INSERT OR UPDATE OR DELETE ON assignments FOR EACH ROW EXECUTE PROCEDURE process_archive();
Jak widać funkcji process_archive() mogę używać do wywołań w triggerach różnych tabel sql-owych dzięki czemu uzyskuję sprawnie audyt/archiwum operacji. W postgresql 9.x można jeszcze w definicji triggerów dorzucić WHEN (OLD.* IS DISTINCT FROM NEW.*) co pozwala zapisywać przy triggerze dla update-ów nowe rekordy w archiwum tylko jak są jakieś zmiany w danych.
Jak to uzyskać w mysql? Ktoś wie? Triggery oczywiście w mysql są...