PostgreSQL - CREATE TRIGGER

In PostgreSQL, a trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be used to maintain the integrity of information in the database and to perform system functions automatically.

Here's the basic syntax to create a trigger in PostgreSQL:

CREATE TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF }
    { INSERT [ OR ] | UPDATE [ OF column_name [, ... ] ] [ OR ] | DELETE }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE FUNCTION trigger_function_name (arguments);

Where:

trigger_name: Name of the trigger.
BEFORE | AFTER | INSTEAD OF: Specifies when the trigger should be fired.
INSERT, UPDATE, DELETE: Specifies the event that will fire the trigger.
ON table_name: The table on which the trigger is to be attached.
FROM referenced_table_name: Used for foreign key actions.
NOT DEFERRABLE, DEFERRABLE, INITIALLY IMMEDIATE, INITIALLY DEFERRED: Specifies the timing of the trigger firing with respect to the transaction.
FOR EACH ROW | FOR EACH STATEMENT: Determines if the trigger should be fired for each row the operation affects or just once per SQL statement.
EXECUTE FUNCTION trigger_function_name: The function that will be called every time the trigger event occurs.

Example:

Let's assume you have a table employees and you want to audit changes to the salary column. You could have a function audit_salary_changes() that logs these changes and then set up a trigger that fires this function every time an UPDATE operation modifies the salary column.

CREATE TRIGGER salary_audit_trigger
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_salary_changes();

Before you create this trigger, you'd need to have the audit_salary_changes function already defined in the database. This function will get executed each time an update to the salary column in the employees table occurs.

Remember to ensure that the function being called (audit_salary_changes in this case) is designed to handle the operations properly. It should take into account the OLD and NEW tables provided in the trigger environment to see the previous and new values of the rows affected.

Creating BEFORE and AFTER triggers in PostgreSQL:
- Description: Defines triggers that execute before or after a specified event.
- Code:
```
CREATE TRIGGER your_trigger
BEFORE/AFTER INSERT/UPDATE/DELETE ON your_table
FOR EACH ROW
-- Trigger function logic here
```
Defining triggers for INSERT, UPDATE, and DELETE actions in PostgreSQL:
- Description: Specifies the trigger action for INSERT, UPDATE, or DELETE events.
- Code:
```
CREATE TRIGGER your_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW
-- Trigger function logic here
```
Using FOR EACH ROW in CREATE TRIGGER in PostgreSQL:
- Description: Applies the trigger function to each affected row.
- Code:
```
CREATE TRIGGER your_trigger
AFTER INSERT ON your_table
FOR EACH ROW
-- Trigger function logic here
```

Accessing NEW and OLD values in trigger functions in PostgreSQL:

Description: Utilizes NEW and OLD to reference new and old values in trigger functions.

Code:

CREATE FUNCTION your_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    -- Access NEW and OLD values
    NEW.column_name;
    OLD.column_name;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER your_trigger
AFTER INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION your_trigger_function();

Enabling and disabling triggers in PostgreSQL:
- Description: Activates or deactivates triggers on a table.
- Code:
```
ALTER TABLE your_table ENABLE/DISABLE TRIGGER your_trigger_name;
```

Managing multiple triggers on the same table in PostgreSQL:

Description: Defines and coordinates the execution order of multiple triggers.

Code:

CREATE TRIGGER trigger1
BEFORE INSERT ON your_table
FOR EACH ROW
-- Trigger function logic

CREATE TRIGGER trigger2
BEFORE INSERT ON your_table
FOR EACH ROW
-- Trigger function logic

Setting trigger order of execution in PostgreSQL:

Description: Specifies the order in which triggers should execute.

Code:

CREATE TRIGGER trigger1
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION trigger1_function();

CREATE TRIGGER trigger2
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION trigger2_function() AFTER trigger1;

Dropping triggers with DROP TRIGGER in PostgreSQL:
- Description: Removes a trigger from the database.
- Code:
```
DROP TRIGGER your_trigger ON your_table;
```