Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-trigger/ below:

PostgreSQL - Trigger - GeeksforGeeks

Last Updated : 15 Jul, 2025

A PostgreSQL trigger is a powerful tool that allows automatic invocation of a function whenever a specified event occurs on a table. Events that can trigger a function include INSERT, UPDATE, DELETE, or TRUNCATE. Triggers help maintain data integrity and automate complex database operations.

A trigger is a special user-defined function associated with a table. To create a new trigger, you must define a trigger function first, and then bind this trigger function to a table. The difference between a trigger and a user-defined function is that a trigger is automatically invoked when an event occurs.

PostgreSQL provides two main types of triggers:

1. Row-Level Triggers: Invoked once for each row affected by the event. For example, an 'UPDATE' statement affecting 20 rows will invoke the row-level trigger 20 times.
2. Statement-Level Triggers: Invoked once per SQL statement, regardless of the number of rows affected.

Also Read: Difference between Row level and Statement level triggers.

Triggers can be specified to fire before or after the event:

• BEFORE Trigger: Invoked before the event occurs. Can be used to modify or skip the operation.
• AFTER Trigger: Invoked after the event occurs. All changes made by the event are available to the trigger.

Triggers offer several benefits:

• Automation: Automatically enforce business rules and data integrity.
• Centralized Logic: Maintain cross-functionality within the database, ensuring consistent behavior across multiple applications.
• Historical Data: Automatically log changes to maintain a history of data modifications.

While triggers are powerful, they also have some drawbacks:

• Complexity: Triggers can add complexity to the database. Users must be aware of the triggers and understand their logic to predict data changes accurately.
• Performance Overhead: Excessive use of triggers can lead to performance issues, especially with complex or nested triggers.

• TRUNCATE Event: PostgreSQL supports triggers for the TRUNCATE event.
• Triggers on Views: PostgreSQL allows the definition of statement-level triggers on views.
• User-Defined Functions: PostgreSQL requires a user-defined function for trigger actions, whereas the SQL standard permits any number of SQL commands.

Let’s take a look at an example of creating a new trigger in PostgreSQL to better understand the concept.

In this example, we will create a new table named 'employees' as follows:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(50),
   SALARY REAL
);

When the name of an employee changes, we log the changes in a separate table named 'employee_audits':

CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

First, define a new function called auditlog():

CREATE OR REPLACE FUNCTION auditlog() RETURNS TRIGGER AS $$
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (NEW.ID, current_timestamp);
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

We create a trigger named 'example_trigger' that fires after an 'INSERT' event on the 'COMPANY' table:

CREATE TRIGGER example_trigger 
AFTER INSERT ON COMPANY
FOR EACH ROW 
EXECUTE FUNCTION auditlog();

Insert some sample data for testing. We insert two rows into the employees table.

INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Raju', 25, 'New-Delhi', 33000.00 );

To examine the employees table use the below query:

SELECT * FROM COMPANY;

Output:

• Unlike many other SQL databases, PostgreSQL supports triggers on the TRUNCATE event.
• PostgreSQL allows you to define statement-level triggers on views, enabling complex data manipulations and validations even when using views.
• PostgreSQL allows multiple triggers on the same event. However, the execution order is not guaranteed unless explicitly set using the BEFORE or AFTER keywords.
• PostgreSQL has a powerful rule system that can be used in conjunction with triggers. Rules can transform incoming queries before they reach the execution phase, while triggers act on data changes.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4