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ą...
--
Pozdrawiam
Tomasz Chiliński, Chilan
