Thread: trigger, how to determine action calling?

Hi,
Is it possible to determine easily which action called triger?
For example:
CREATE TRIGGER log_znw BEFORE INSERT OR UPDATE ON tab
   FOR EACH ROW EXECUTE PROCEDURE log_tab();
How to check in called function if it was INSERT or UPDATE?

tia

Marcin Mazurek

--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/

Hello Marcin,

Once, Thursday, August 31, 2000, 2:22:10 PM, you wrote:

MM> Hi,
MM> Is it possible to determine easily which action called triger?
MM> For example:
MM> CREATE TRIGGER log_znw BEFORE INSERT OR UPDATE ON tab
MM>    FOR EACH ROW EXECUTE PROCEDURE log_tab();
MM> How to check in called function if it was INSERT or UPDATE?

If you use plpgsql language for your procedure, then TG_OP variable is
what you need, if pltcl, then $TG_op variable.

--
Best regards,
 Yury  ICQ 11831432
 mailto:yura@vpcit.ru

Marcin Mazurek wrote:
>
> Hi,
> Is it possible to determine easily which action called triger?
> For example:
> CREATE TRIGGER log_znw BEFORE INSERT OR UPDATE ON tab
>    FOR EACH ROW EXECUTE PROCEDURE log_tab();
> How to check in called function if it was INSERT or UPDATE?

Sure.  Here's a snippet of C that does this.  You might like to check
out the sample SPI programs included with the PostgreSQL source (in
/contrib/spi.)

  if (!CurrentTriggerData)
    elog(ERROR, "nodeinsert: triggers are not initialized");
  if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
    elog(ERROR, "nodeinsert: can't process STATEMENT events");
  if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
    elog(ERROR, "nodeinsert: must be fired before event");

  if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
    trigger_tuple = CurrentTriggerData->tg_trigtuple;
  else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
    /* trigger_tuple = CurrentTriggerData->tg_newtuple; */
    elog(ERROR, "nodeinsert: must be fired on INSERT, not UPDATE");
  else
    elog(ERROR, "nodeinsert: must be fired on INSERT, not DELETE");

--

Ron Peterson
Systems Manager
Wallace Floyd Design Group
273 Summer Street
Boston, MA  02210
617.350.7400 tel
617.350.0051 fax
rpeterson@wallacefloyd.com