Greenplum DB and  database triggers

Post date: Sep 20, 2012 7:9:29 PM

Q. Can the system at login of a user to Greenplum DB, trigger a procedure to call a currently available web service which returns a set of information consisting of EMPID and few other fields?

Ans:  No.

Details:

1. Due to the distributed nature of a Greenplum Database system, the use of triggers is very limited in Greenplum Database. The function used in the trigger must be IMMUTABLE, meaning it cannot use information not directly present in its argument list. The function specified in the trigger also cannot execute any SQL or modify distributed database objects in any way. Given that triggers are most often used to alter tables (for example, update these other rows when this row is updated), these limitations offer very little practical use of triggers in Greenplum Database. For that reason, Greenplum does not support the use of user-defined triggers in Greenplum Database. Triggers cannot be used on append-only tables.

2. SELECT does not modify any rows so you can not create SELECT triggers.

3. Rules and views are more appropriate in such cases.