Skip to content

Trigger

  • A PostgreSQL trigger is a user-defined function invoked automatically whenever an event associated with a table occurs.

  • An event could be any of the following: INSERT, UPDATE, DELETE or TRUNCATE.

  • PostgreSQL provides two main types of triggers: row and statement-level triggers. The differences between the two kinds are how many times the trigger is invoked and at what time.

  • For example, if you issue an UPDATE statement that affects 20 rows, the row-level trigger will be invoked 20 times, while the statement level trigger will be invoked 1 time.

  • Triggers are useful in case the database is accessed by various applications, and you want to keep the cross-functionality within the database that runs automatically whenever the data of the table is modified.

  • For example, if you want to keep the history of data without requiring the application to have logic to check for every event such as INSERT or UDPATE.

  • You can also use triggers to maintain complex data integrity rules which you cannot implement elsewhere except at the database level. For example, when a new row is added into the customer table, other rows must be also created in tables of banks and credits.

  • The main drawback of using a trigger is that you must know the trigger exists and understand its logic to figure it out the effects when data changes.

  • Even though PostgreSQL implements SQL standard, triggers in PostgreSQL has some specific features:

  • PostgreSQL fires trigger for the TRUNCATE event.
  • PostgreSQL allows you to define the statement-level trigger on views.
  • PostgreSQL requires you to define a user-defined function as the action of the trigger, while the SQL standard allows you to use any SQL commands.