Hi list,
I have a trigger to log what the users do on the database:
DECLARE _query VARCHAR;
DECLARE valid BOOL;
DECLARE act VARCHAR;
DECLARE tab VARCHAR;
DECLARE field VARCHAR;
BEGIN
IF(TG_OP = 'DELETE') THEN
act = 'DELETION of row with id: ' || OLD.id;
ELSIF(TG_OP = 'UPDATE') THEN
IF NEW.id<>OLD.id THEN
field = 'id from: ' || OLD.id || ' TO ' || NEW.id;
ELSIF NEW.entry_no<>OLD.entry_no THEN
RAISE EXCEPTION 'Die generische Seriennummer kann von Ihnen
nicht
verändert werden!';
RETURN NULL;
ELSIF NEW.gruppe<>OLD.gruppe THEN
valid = NEW.gruppe > 0 AND NEW.gruppe<>999 AND NEW.gruppe IS NOT
NULL;
field = 'field gruppe from: ' || OLD.gruppe || ' TO ' ||
NEW.gruppe;
ELSIF NEW.kombi<>OLD.kombi THEN
valid = (NEW.kombi>=0) AND (NEW.kombi<=2);
field = 'field kombi from: ' || OLD.kombi || ' TO ' ||
NEW.kombi;
END IF;
act = 'UPDATE OF ' || field || ' with id: ' ||
OLD.id;
END IF;
tab = TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME;
INSERT INTO history(aennam, action, table_name) VALUES(current_user,
act, tab);
RETURN NULL;
END;
Now the problem is that a tuple gets added to the table history, but
the field "action" (whatever the user did) is 99% empty, whereas the
others are filled and I don't see why...
Any hint is greatly appreciated
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230
