Re: NEW in after insert trugger contained incorrect data

On 11/14/14, 10:09 AM, Adrian Klaver wrote:
>> Trigger update_cpu_load_stat added to table trassa.cpu_load:
>> CREATE TRIGGER update_cpu_load_stat_trigger
>> AFTER INSERT
>> ON trassa.cpu_load_stat
>> FOR EACH ROW
>> EXECUTE PROCEDURE trassa.update_cpu_load_stat();
>
> Another run through showed that the issue is above. You have declared the trigger on trassa.cpu_load_stat instead of trassa.cpu_load. trassa.cpu_load_stat has no value field, hence the error.

Something else to consider: using FOUND to decide whether to INSERT vs UPDATE is a race condition: you can do the SELECT, someone else can then insert or delete, and then you attempt to do the wrong thing.

To handle this correctly, you need an appropriate UNIQUE constraint or primary key, and to follow the pattern in table 40-2 at http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

If you don't, and you have concurrent activity you can end up losing data (and in the case of a DELETE after your SELECT, the data loss will be completely silent).
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com