More

    All About PostgreSQL Triggers – Postgres versions(9,10,11,12,13)

    Definition

    • A “trigger” is defined as any event that sets a course of action in a motion.

    • In PostgreSQL, if you want to take action on specific database events, such as INSERT, UPDATE, DELETE, or TRUNCATE, then trigger functionality can be useful as it will invoke the required function on defined events.

    • The trigger will be associated with the specified table, view, or foreign table and will execute the specified function when certain operations are performed on that table. Depending on the requirement we can create trigger BEFORE, AFTER or INSTEAD of the events/operation.

    • A trigger is a special user-defined function that binds to 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.

    Types of Trigger

    1. Row Level Trigger: If the trigger is marked FOR EACH ROW then the trigger function will be called for each row that is getting modified by the event.
    2. Statement Level Trigger: The FOR EACH STATEMENT option will call the trigger function only once for each statement, regardless of the number of the rows getting modified.

    The differences between above two types of trigger are how many times the trigger is invoked. 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.

    You can specify whether the trigger is invoked before or after an event. If the trigger is invoked before an event, it can skip the operation for the current row or even change the row being updated or inserted. In case the trigger is invoked after the event, all changes are available to the trigger.

    Usefulness of Trigger

    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.

    Drawback of trigger

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

    PostgreSQL Trigger -Specific features over other standard triggers

    • PostgreSQL could fire trigger for the TRUNCATE event as well.
    • 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 number of SQL commands.

    Creating Trigger

    1. Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named employees as follows:
    CREATE TABLE employees(
      id SERIAL PRIMARY KEY,
      first_name VARCHAR(40) NOT NULL,
      last_name VARCHAR(40) NOT NULL
    );

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

    CREATE TABLE employee_audits (
      id SERIAL PRIMARY KEY,
      employee_id INT NOT NULL,
      last_name VARCHAR(40) NOT NULL,
      changed_on TIMESTAMP(6) NOT NULL
    );

    2. Define a new function called log_last_name_changes which inserts the old last name into the employee_audits table including employee id, last name, and the time of change if the last name of an employee changes.

    CREATE OR REPLACE FUNCTION log_last_name_changes()
    RETURNS trigger AS
    $BODY$
    BEGIN
      IF NEW.last_name <> OLD.last_name THEN
          INSERT INTO employee_audits(employee_id,last_name,changed_on)
          VALUES(OLD.id,OLD.last_name,now());
      END IF;
      RETURN NEW;
    END;
    $BODY$
    LANGUAGE plpgsql;

    3. Bind the trigger function to the employees table. The trigger name is last_name_changes. Before the value of the last_name column is updated, the trigger function is automatically invoked to log the changes.

    CREATE TRIGGER last_name_changes
    BEFORE UPDATE
    ON employees
    FOR EACH ROW
      EXECUTE PROCEDURE log_last_name_changes();

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

    INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');

    INSERT INTO employees (first_name, last_name) VALUES ('Lily', 'Bush');

    SELECT * from employees;

    5. Suppose that Lily Bush gets married and she needs to change her last name to Lily Brown. We can update her last name as shown in the following query:

    UPDATE employees SET last_name = 'Brown' WHERE ID = 2;

    SELECT * from employees;

    SELECT * from employee_audits;

     

    Drop Trigger

    • To delete a trigger from a table, you use the DROP TRIGGER statement with the following syntax:
    DROP TRIGGER [IF EXISTS] trigger_name ON table_name [ CASCADE | RESTRICT ];
    • First, specify the name of the trigger which you want to delete after the DROP TRIGGER keywords.
    • Second, use IF EXISTS to conditionally delete the trigger only if it exists. Attempt to delete a non-existing trigger without specifying the IF EXISTS statement results in an error. If you use IF EXISTS to delete a non-existing trigger, PostgreSQL issues a notice instead.
    • Third, specify the name of the table to which the trigger belongs. If the table belongs to a specific schema, you can use the schema-qualified name of the table e.g., schema_name.table_name.
    • Fourth, use CASCADE option if you want to drop objects that depend on the trigger automatically. Note that CASCADE option will also delete objects that depend on objects that depend on the trigger.
    • Fifth, use RESTRICT TO refuse to drop the trigger if any objects depend on it. By default, the DROP TRIGGER statement uses RESTRICT.

    Managing PostgreSQL Trigger

    ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;

    ALTER TABLE table_name DISABLE TRIGGER trigger_name | ALL

    ALTER TABLE employees DISABLE TRIGGER log_last_name_changes;

    Recent Articles

    spot_img

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox