<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >PL/Perl Triggers</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="PL/Perl - Perl Procedural Language" HREF="plperl.html"><LINK REL="PREVIOUS" TITLE="Trusted and Untrusted PL/Perl" HREF="plperl-trusted.html"><LINK REL="NEXT" TITLE="Limitations and Missing Features" HREF="plperl-missing.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plperl-trusted.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plperl.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 37. PL/Perl - Perl Procedural Language</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="plperl.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="plperl-missing.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="PLPERL-TRIGGERS" >37.6. PL/Perl Triggers</A ></H1 ><P > PL/Perl can be used to write trigger functions. In a trigger function, the hash reference <TT CLASS="VARNAME" >$_TD</TT > contains information about the current trigger event. The fields of the <TT CLASS="VARNAME" >$_TD</TT > hash reference are: <P ></P ></P><DIV CLASS="VARIABLELIST" ><DL ><DT ><TT CLASS="LITERAL" >$_TD->{new}{foo}</TT ></DT ><DD ><P > <TT CLASS="LITERAL" >NEW</TT > value of column <TT CLASS="LITERAL" >foo</TT > </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{old}{foo}</TT ></DT ><DD ><P > <TT CLASS="LITERAL" >OLD</TT > value of column <TT CLASS="LITERAL" >foo</TT > </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{name}</TT ></DT ><DD ><P > Name of the trigger being called </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{event}</TT ></DT ><DD ><P > Trigger event: <TT CLASS="LITERAL" >INSERT</TT >, <TT CLASS="LITERAL" >UPDATE</TT >, <TT CLASS="LITERAL" >DELETE</TT >, or <TT CLASS="LITERAL" >UNKNOWN</TT > </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{when}</TT ></DT ><DD ><P > When the trigger was called: <TT CLASS="LITERAL" >BEFORE</TT >, <TT CLASS="LITERAL" >AFTER</TT >, or <TT CLASS="LITERAL" >UNKNOWN</TT > </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{level}</TT ></DT ><DD ><P > The trigger level: <TT CLASS="LITERAL" >ROW</TT >, <TT CLASS="LITERAL" >STATEMENT</TT >, or <TT CLASS="LITERAL" >UNKNOWN</TT > </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{relid}</TT ></DT ><DD ><P > OID of the table on which the trigger fired </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{relname}</TT ></DT ><DD ><P > Name of the table on which the trigger fired </P ></DD ><DT ><TT CLASS="LITERAL" >$_TD->{argc}</TT ></DT ><DD ><P > Number of arguments of the trigger function </P ></DD ><DT ><TT CLASS="LITERAL" >@{$_TD->{args}}</TT ></DT ><DD ><P > Arguments of the trigger function. Does not exist if $_TD->{argc} is 0. </P ></DD ></DL ></DIV ><P> </P ><P > Triggers can return one of the following: <P ></P ></P><DIV CLASS="VARIABLELIST" ><DL ><DT ><TT CLASS="LITERAL" >return;</TT ></DT ><DD ><P > Execute the statement </P ></DD ><DT ><TT CLASS="LITERAL" >"SKIP"</TT ></DT ><DD ><P > Don't execute the statement </P ></DD ><DT ><TT CLASS="LITERAL" >"MODIFY"</TT ></DT ><DD ><P > Indicates that the <TT CLASS="LITERAL" >NEW</TT > row was modified by the trigger function </P ></DD ></DL ></DIV ><P> </P ><P > Here is an example of a trigger function, illustrating some of the above: </P><PRE CLASS="PROGRAMLISTING" >CREATE TABLE test ( i int, v varchar ); CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$ if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) { return "SKIP"; # skip INSERT/UPDATE command } elsif ($_TD->{new}{v} ne "immortal") { $_TD->{new}{v} .= "(modified by trigger)"; return "MODIFY"; # modify row and execute INSERT/UPDATE command } else { return; # execute INSERT/UPDATE command } $$ LANGUAGE plperl; CREATE TRIGGER test_valid_id_trig BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE valid_id();</PRE ><P> </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="plperl-trusted.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="plperl-missing.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Trusted and Untrusted PL/Perl</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="plperl.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Limitations and Missing Features</TD ></TR ></TABLE ></DIV ></BODY ></HTML >