Re: Trigger efficiency

I do not know of a way to make your trigger run less often, but you could only
have it do the insert if something changes. This is what we do:

CREATE or replace FUNCTION UPDATE_SERVER_HST() RETURNS TRIGGER AS '
begin
if (OLD.ADD_DATE is distinct from NEW.ADD_DATE
or OLD.HOSTNAME is distinct from NEW.HOSTNAME
) then

insert into SERVER_HST( OPERATION, HST_USR_ID, HST_ADD_DATE, SERVER_ID,
UPDATE_COUNT, ADD_DATE, HOSTNAME) values( substr( TG_OP, 1, 1 ),
GET_CONNECTION_USR(), now()
, OLD.SERVER_ID, OLD.UPDATE_COUNT, OLD.ADD_DATE, OLD.HOSTNAME);
end if;

RETURN NULL; -- result is ignored since this is an AFTER trigger

end;
'
language plpgsql
security definer;

That way the insert only happens if there is a change. Since this is a pain
we have a program that writes our history and update_count triggers for us
based on what we run through our modeling tool.

Craig

On Monday 06 February 2006 06:48, Josep Sanmartí wrote:
> Hi,
>
> I've the following problem and I don't know how to solve it: There is a
> table with about 10-12 fields, a couple of those fields are updated very
> often (about 30 times / minute or even more). This is my table (more or
> less):
>
> crete table monitor(
> time Timestamp,
> time2 timestamp,
> ...
> status int,
> );
>
> I need to know how many rows are in the table and keep that number to
> another table every time that there is an INSERT or DELETE on that
> table, so I made a trigger that fires on insert and delete events and
> calls a function that makes de update on the new table, that works well.
> My problem starts when I have to count the rows depending on the status
> field: I added a UPDATE on my trigger and it works! :)...... my trigger:
>
> CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE
> ON monitor EXECUTE PROCEDURE f_barra_aps();
>
> But now, this trigger fires too often and there is an extra load on the
> system that makes everything run slower. I posted before a question
> asking about firing triggers on specific columns...thats not possible. I
> don't know how to solve it, perhaps I should do it without
> triggers.....any help will be appreciated
>
> thanks!