Javatpoint Logo
Javatpoint Logo

PostgreSQL Triggers

PostgreSQL Triggers are a set of actions or database callback functions that run automatically when a specified database event (i.e. INSERT, UPDATE, DELETE or TRUNCATE statement) is performed on a specified table. Triggers are used to validate input data, enforce business rules, keeping audit trail etc.


Important points about Triggers

  1. PostgreSQL are performed/ invoked in the following cases:
  2. Before the operation is attempted (before constraints are checked and the INSERT, UPDATE or DELETE is attempted).

    Or, after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed).

    Or, Instead of the operation (in the case of inserts, updates or deletes on a view)

  3. A trigger marked FOR EACH ROWS is called once for every row that the operation modifies. On the other hand, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies.
  4. You can define multiple triggers of the same kind for the same event but the condition is they will be fired in alphabetical order by name.
  5. Triggers are automatically dropped when the table that they are associated with is dropped.

PostgreSQL Create Trigger

The CREATE TRIGGER statement is used to create a new trigger in a PostgreSQL table. It is activated when a particular event (i.e. INSERT, UPDATE, and DELETE) occurs for the table.

Syntax:

Here, event_name could be INSERT, UPDATE, DELETE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.

Let's see the syntax of creating a trigger on an INSERT operation.

Let's take an example to demonstrate PostgreSQL create trigger AFTER INSERT statement.

See this example:

In the following example, we keep audit trial for every record being inserted in COMPANY table.

Create a table named COMPANY, by using the following query:

To keep audit trial, we will create a new table called AUDIT where log messages will be inserted whenever there is an entry in COMPANY table for a new record.

Create another table Audit, by using the following query:

Before creating a trigger on company table, first create a function/ procedure named auditlogfunc().

Execute the following query:

PostgreSQL triggers1

Now create a trigger on COMPANY table by using the following query:

Execute the following query:

PostgreSQL triggers2

Insert some values in the COMPANY table.

PostgreSQL triggers3

At the same time, two records will be created in AUDIT table. These records are the result of trigger, which we have created on AFTER INSERT on COMPANY table.

PostgreSQL triggers4

Usage of PostgreSQL Triggers

PostgreSQL triggers can be used for following purposes:

  • Validate input data.
  • Enforce business rules.
  • Generate a unique value for a newly inserted row on a different file.
  • Write to other files for audit trail purposes.
  • Query from other files for cross-referencing purposes.
  • Access system functions.
  • Replicate data to different files to achieve data consistency.

Advantages of using triggers

  • It accelerates the development speed of applications. Because the database stores triggers, you do not have to code the trigger actions into each database application.
  • Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
  • Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
  • Improve performance in client/server environment. All rules run in the server before the result returns.
Next TopicPostgreSQL Alias




Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


B.Tech / MCA