Open In App

PostgreSQL – CREATE TRIGGER

Improve
Improve
Like Article
Like
Save
Share
Report

To create a new trigger in PostgreSQL, you follow these steps:

  • First, create a trigger function using CREATE FUNCTION statement.
  • Second, bind the trigger function to a table by using the CREATE TRIGGER statement.

A trigger function is similar to an ordinary function. However, a trigger function does not take any argument and has a return value with the type of trigger.

The following illustrates the syntax of creating a trigger function:

Syntax:
CREATE FUNCTION trigger_function() 
   RETURNS trigger AS

A trigger function receives data about its calling environment through a special structure called TriggerData, which contains a set of local variables. Once you define a trigger function, you can bind it to one or more triggers events such as INSERT, UPDATE, and DELETE.

Example:

Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named COMPANY 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 AUDIT :

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 $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

The function inserts the old last name into the AUDIT table including employee id, last name, and the time of change if the last name of an employee changes. Second, bind the trigger function to the employees table. The trigger name is name changes. Before the value of the name column is updated, the trigger function is automatically invoked to log the changes.

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

Third, 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:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads