PostgreSQL - Trigger

In PostgreSQL, a trigger is a named database object that's associated with a table and activates when a specified event occurs for that table. These events could be INSERT, UPDATE, DELETE, or TRUNCATE operations.

A trigger is defined to activate either before or after an event, or even instead of the event (in the case of views). It can be set to activate once for every row affected by the event (row-level trigger) or once for the whole SQL statement (statement-level trigger).

Key Concepts:

Trigger Functions:
Before creating a trigger, you first need to define a trigger function. This function is what will be executed when the trigger fires. Trigger functions can be written in various languages, but PL/pgSQL is commonly used.
Example of a simple trigger function:
```
CREATE OR REPLACE FUNCTION log_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logs (table_name, old_value, new_value) 
    VALUES ('my_table', OLD.*, NEW.*);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```
Creating Triggers:
After the trigger function is defined, you can create a trigger, linking it to a table and specifying the event:
```
CREATE TRIGGER trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
EXECUTE FUNCTION log_update();
```
Timing:
- BEFORE: The trigger function will run before the event.
- AFTER: The trigger function will run after the event.
- INSTEAD OF: Used primarily with views, where the trigger runs as a replacement for the actual operation.
Event:
Triggers can be set to fire on the following events:
- INSERT
- UPDATE
- DELETE
- TRUNCATE
For UPDATE, you can also specify columns:
```
UPDATE OF column_name1, column_name2
```
Row-level vs. Statement-level:
- FOR EACH ROW: This is a row-level trigger, which means the trigger function will be invoked once for each row affected by the event.
- FOR EACH STATEMENT: This is a statement-level trigger, which means the trigger function will be invoked once for the whole SQL statement, regardless of the number of rows affected.

Enabling/Disabling Triggers:

Triggers can be temporarily disabled and later re-enabled:

ALTER TABLE table_name DISABLE TRIGGER trigger_name;
ALTER TABLE table_name ENABLE TRIGGER trigger_name;

Dropping Triggers:

DROP TRIGGER trigger_name ON table_name;

Usage:

Triggers can be useful for various tasks:

Auditing: Automatically logging changes to data.
Validation: Enforcing complex business rules or data integrity checks.
Modification: Automatically modifying data in a specific way before it's inserted or updated.
Cascading changes: For example, updating values in related tables.

While triggers offer powerful functionality, they can also introduce complexity and can affect performance. It's essential to use them judiciously and ensure that their behavior is well-documented and understood by the development team.

How to create a trigger in PostgreSQL: Create a trigger in PostgreSQL using the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function();

PostgreSQL trigger example: An example of a trigger that automatically updates a timestamp on row insertion:

CREATE TRIGGER update_timestamp
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_function();

Before and after triggers in PostgreSQL: BEFORE triggers are executed before the event, and AFTER triggers are executed after the event. For example:

CREATE TRIGGER before_trigger
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE FUNCTION before_trigger_function();

CREATE TRIGGER after_trigger
AFTER INSERT ON example_table
FOR EACH ROW
EXECUTE FUNCTION after_trigger_function();

Disable trigger in PostgreSQL: Disable a trigger using the DISABLE TRIGGER statement:
```
ALTER TABLE example_table
DISABLE TRIGGER trigger_name;
```

Update trigger in PostgreSQL: Example of a trigger that updates a column after an update operation:

CREATE TRIGGER update_column_trigger
AFTER UPDATE ON example_table
FOR EACH ROW
EXECUTE FUNCTION update_column_function();

Conditional triggers in PostgreSQL: Use the WHEN condition to specify when a trigger should be executed:

CREATE TRIGGER conditional_trigger
AFTER INSERT ON example_table
FOR EACH ROW
WHEN (NEW.column_name > 100)
EXECUTE FUNCTION conditional_trigger_function();

Managing triggers in PostgreSQL: View and manage triggers using the pg_trigger system catalog table or pg_triggers view:
```
SELECT * FROM pg_trigger WHERE tgrelid = 'example_table'::regclass;
```