Thread: Trigger problem

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

am  Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl folgendes:
> Hi list,
>
>     act = 'DELETION of row with id: ' || OLD.id;
>                   act = 'UPDATE OF ' || field || ' with id: ' ||
> ...
> INSERT INTO history(aennam, action, table_name) VALUES(current_user,
> act, tab);
>
> 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

Maybe sometime the concateneted fields (e.g. field, OLD.id) contains
NULL-values. If so, the complete string 'act' will be NULL.

Solution: use coalesce(field,''). Hope that helps.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Thanks for pinpointing that out. After including your solution it shows
at least something like "UPDATE OF  with id: 123456567". Unfortunately
after using coalesce, too, when building the field variable like: field
= 'field gruppe from: ' || coalesce(OLD.gruppe, '') || ' TO ' ||
coalesce(NEW.gruppe, ''); there still seem to be null values. Of course,
it can be that the user deletes a value and then there is a NULL value,
say for NEW.gruppe for example, but shouldn't the coalesce in this case
avoid that the whole field-string is empty?


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




>>> On Tue, Nov 27, 2007 at 10:53 AM, in message
<20071127095336.GF31593@a-kretschmer.de>, "A. Kretschmer"
<andreas.kretschmer@schollglas.com> wrote:
> am  Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl
> folgendes:
>> Hi list,
>>
>>     act = 'DELETION of row with id: ' || OLD.id;
>>                   act = 'UPDATE OF ' || field || ' with id: ' ||
>> ...
>> INSERT INTO history(aennam, action, table_name)
VALUES(current_user,
>> act, tab);
>>
>> 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
>
> Maybe sometime the concateneted fields (e.g. field, OLD.id) contains
> NULL- values. If so, the complete string 'act' will be NULL.
>
> Solution: use coalesce(field,''). Hope that helps.
>
>
> Andreas