Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Triggers

Often, anticipated SQL events should precede or follow a particular action. This action might be a consistency check on a set of values to be inserted, the formatting of supplied data before it is inserted, or a modification to a separate table following the removal or modification of a set of rows. Traditionally, such actions are handled at the programmatic level within an application connected to the database, rather than the database software itself.

To ease the responsibility of the application's database interaction, PostgreSQL supports a non-standard programmatic extension known as a trigger . A trigger defines a function which occurs before, or after, another action on a table. A trigger is implemented through C, Pl/pgSQL or any other functional language (with the exception of SQL) that PostgreSQL can use to define a function (see the Section called Extending PostgreSQL " later in this chapter for more on creating functions, or Chapter 11 for more on PL/pgSQL).

Warning

As triggers are a PostgreSQL-specific extension, be sure not to implement a trigger-based solution when a high degree of portability to other RDBMS systems is important.

Triggers may affect any of the following SQL events on a table:

  • INSERT

  • UPDATE

  • DELETE

Creating a trigger

In order to create a trigger, a function must first exist for it to execute. PostgreSQL supports many types of functions, including those defined by SQL, PL/pgSQL, and C. As of PostgreSQL 7.1.x, a trigger may use a function defined in any language, with the exception that the function cannot be defined as a purely SQL function.

Once a function is defined, a trigger may be defined to call that function either before or after an event on a specified table. Here is the syntax to create a trigger, followed by a description of its syntax:

  CREATE TRIGGER 
name
 { BEFORE | AFTER } { 
event
 [ OR 
event
 ... ] }
                 ON 
tablename

                 FOR EACH { ROW | STATEMENT }
                 EXECUTE PROCEDURE 
functionname
 ( 
arguments
 )

CREATE TRIGGER name

name is any arbitrary name for the new trigger. A trigger may have the same name as an existing trigger in a database provided that it is defined to operate on a different table. Also, like most other non-system database objects, triggers must only have a unique name (and table to operate on) within the database they are created in.

{ BEFORE | AFTER }

The BEFORE keyword instructs the defined function to be executed before the event is attempted, which also precedes any built-in constraint checking on the values involved in the case of an INSERT or DELETE event. Alternatively, the AFTER keyword causes the function to be called only after the attempted action has finished.

{ event [ OR event ... ] }

event is any one of the supported SQL events; multiple events may be listed, separated by the OR keyword.

ON tablename

tablename is the name of the table which, when modified by event , initiates this trigger.

FOR EACH { ROW | STATEMENT }

The keyword following the FOR EACH clause determines how many times the function should be called when the defined event is triggered. Use the ROW keyword to specify that the function is to be executed once for each affected row . Conversely, if the function should be executed only once for the calling statement, the STATEMENT keyword is used.

EXECUTE PROCEDURE functionname ( arguments )

functionname is the name of the existing function to be executed, with passed arguments .

Note: Only the database object's owner, or a super user, can create a trigger on a database object.

While PostgreSQL tables support constraints to perform simple checks against static criteria, sometimes more involved procedures may be needed to validate input values. This is a typical example of where a trigger might be useful.

A trigger may be used to validate input values by preparing a validation function to be executed before values are inserted into a table, or before values in a table are updated. The function can then be made responsible for verifying that the values meet a complex set of restrictions, and even return an appropriate error through PostgreSQL's error logging system.

Suppose that you have written a function in a procedural language that validates attempted INSERT or UPDATE values on the shipments table, and that then performs an update on the stock table to decrement the inventory for the shipment. This function could be written in any language that PostgreSQL supports (with the noted exception of pure SQL).

Specifically, suppose that this function verifies that both the provided customer_id and isbn exist in their respective customers and editions tables. If at least one is missing, a meaningful error is returned. Otherwise, the SQL statement is allowed to execute, and on a successful INSERT statement, the stock table is automatically decremented to reflect the drop in stock from the shipment.

Example 7-35 creates a trigger to be "fired" immediately before an INSERT or UPDATE statement is processed on the shipments table. The trigger invokes the check_shipment_addition() function once per each modified row.

Example 7-35. Creating the check_shipment trigger

booktown=# 
CREATE TRIGGER check_shipment

booktown-# 
               BEFORE INSERT OR UPDATE

booktown-# 
               ON shipments FOR EACH ROW

booktown-# 
               EXECUTE PROCEDURE check_shipment_addition();

CREATE

Since the check_shipment trigger is configured to execute the check_shipment_addition() function for both INSERT and UPDATE statements, the integrity of the customer_id and isbn columns are fairly robustly maintained. Its use of the ROW keyword ensures that each added or modified row will be processed by the check_shipment_addition() validation function.

No arguments are passed to the check_shipment_addition() function, as it uses internal PL/pgSQL variables to check incoming rows. See Example 11-53, in Chapter 11, for the implementation of the check_shipment_addition() function, written in PL/pgSQL.

Viewing a trigger

Triggers are stored in the pg_trigger PostgreSQL system table, and can have their characteristics queried after creation. The structure of the pg_trigger table is shown in Table 7-3.

Table 7-3. The pg_trigger table

Column

Type

tgrelid

oid

tgname

name

tgfoid

oid

tgtype

smallint

tgenabled

boolean

tgisconstraint

boolean

tgconstrname

name

tgconstrrelid

oid

tgdeferrable

boolean

tginitdeferred

boolean

tgnargs

smallint

tgattr

int2vector

tgargs

bytea

Most of the columns in the Table 7-3 column are unlikely to be useful in a direct query. The most immediately relevant attributes of the pg_trigger system table are tgrelid and tgname.

The tgrelid value is the trigger's relation identifier number. This value is of type oid, and corresponds to the relfilenode column in the pg_class. The tgname is the identifier which represents the name of the trigger, as specified in the CREATE TRIGGER command when the trigger was created.

Removing a trigger

The DROP TRIGGER command removes a trigger permanently from the database. Similar to the CREATE TRIGGER command, using this command requires you to be either the owner of the trigger, or a superuser.

Here is the syntax to remove an existing trigger:

  DROP TRIGGER 
name
 ON 
table

Example 7-36 drops the check_shipment trigger placed on the shipments table.

Example 7-36. Dropping a trigger

booktown=# 
DROP TRIGGER check_shipment ON shipments;

DROP

The DROP statement indicates that the trigger was successfully dropped. Notice that you must specify not only the name of the trigger that you wish to remove, but also the table on which it is placed.

If you are unsure which table a particular trigger is placed on, you can derive this information from PostgreSQL's system tables. For example, you can perform a join between the pg_trigger system table's tgrelid column and the pg_class system table's relfilenode column, comparing the name of the trigger against the tgname column. Example 7-37 demonstrates such a query to check the assigned relation (relname) associated with the trigger named check_shipment.

Example 7-37. Selecting a trigger's assigned table

booktown=# 
SELECT relname FROM pg_class

booktown-# 
               INNER JOIN pg_trigger

booktown-# 
               ON (tgrelid = relfilenode)

booktown-# 
               WHERE tgname = 'check_shipment';

  relname
-----------
 shipments
(1 row)

Caution

If you drop a function that a trigger is defined to use, the trigger will fail, and redefining the function with the same name will not correct the problem. Such a trigger must be recreated after its function is recreated.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire