> I do not know what the TG_OP is. I'll do some research. If I can avoid
> having a list of triggers for a single action, calling one 'BEFORE AND
> AFTER UPDATE OR DELETE OR INSERT' then this would be great.
No, you can't have one trigger that fires both before and after.
However, you can have one trigger that fires before and another that fires
after, and both of them can call the SAME function, leaving you with
just one block of code to maintain.
Whether this is more efficient than multiple blocks of code is debatable,
but I think it enhances maintainability, which is at least as important
as performance. Computer time is cheap, programmer time is not.
TG_WHEN contains when the trigger fired ('BEFORE' or 'AFTER') and TG_OP
contains the database operation that caused the trigger to fire
('INSERT','DELETE' or 'UPDATE').
These variables can be used as follows:
BEGIN
if TG_OP = ''UPDATE'' and TG_WHEN = ''BEFORE'' then
action 1
return NEW;
end if;
if TG_OP = ''UPDATE'' and TG_WHEN = ''AFTER'' then
action 2
RETURN NULL;
end if;
if TG_OP = ''INSERT'' and TG_WHEN = ''BEFORE'' then
action 3
return NEW;
end if;
if TG_OP = ''INSERT'' and TG_WHEN = ''AFTER'' then
action 4
RETURN NULL;
end if;
if TG_OP = ''DELETE'' and TG_WHEN = ''BEFORE'' then
action 5
return OLD;
end if;
if TG_OP = ''DELETE'' and TG_WHEN = ''AFTER'' then
action 6
RETURN NULL;
end if;
-- SHOULD NEVER GET HERE!
END;
Note: A trigger that fires on an AFTER cannot alter the values for the
row it was fired on, so what it returns is not critical, the pgsql
documentation recommends it return NULL.
--
Mike Nolan