# Triggers

* A postgresql trigger is a functoin invoked automatically whenever 'an event' associated with a table occurs.
* An event could be any of the following;
  * INSERT
  * UDPATE
  * DELETE
  * TRUNCATE
* You can associate a trigger with a
  * Table
  * View
  * Foreign Table
* A trigger is a special 'user-defined function'
* A trigger is automatically invoked
* We can create a triger
  * BEFORE
    * Trigger is fired before an event is about to happen
  * AFTER
    * Trigger is fired after the event is completed
  * INSTEAD
    * In case the event fails, trigger is fired
* Cannot be fired manually
* Fired in alphbetically order
* DO Not change in primary key, foriegn key or unique key column
* DO Not update records in the table that you normally read during the transaction
* DO Not read data from a table that is updating during the same transaction
* DO Not aggregate/summarized over the table that you are updating

## Types of Triggers

* Row level
  * If row is marked for `FOR EACH ROW`, then trigger will be called for each row that is getting modfied by the event
* Statement level
  * The `FOR EACH STATEMENT` will call the trigger function only ONCE for each statement, regardless of the number of rows getting modified.

| When       | Event                | Row-level | Statement-level |
| ---------- | -------------------- | --------- | --------------- |
|            | INSERT/UDPATE/DELETE | Tables    | Tables and view |
| before     | Truncate             |           |                 |
| ---------- | -------------------- | --------- | --------------- |
|            | INSERT/UDPATE/DELETE | Tables    | Tables and view |
| AFTER      | Truncate             |           |                 |
| ---------- | -------------------- | --------- | --------------- |
|            | INSERT/UDPATE/DELETE | Views     |                 |
| INSTEAD OF | Truncate             |           |                 |

## Create your own Trigger in PostgreSQL

```sql
CREATE FUNCTION trigger_function( ) 
    RETURNS TRIGGER LANGUAGE PLPGSQL
AS $$
BEGIN
    -- TRIGGER LOGIC
END;
$$
```

## Syntax

```sql
CREATE TRIGGER trigger_name {BEFORE|AFTER} {EVENT} 
ON table_name 
    [FOR [EACH] {ROW | STATEMENT}]
    EXECUTE PROCEDURE trigger_function

-- FOR EACH [ROW|STATEMENT]
```

## Data Auditing with Triggers

### Setup Example tables

```sql
CREATE TABLE players (
	player_id SERIAL PRIMARY KEY,
	name VARCHAR(100)
);

CREATE TABLE player_audits (
	player_audit_id SERIAL PRIMARY KEY,
	player_id INT NOT NULL,
	name VARCHAR(100) NOT NULL,
	new_name VARCHAR(100) NOT NULL,
	edit_date TIMESTAMP NOT NULL
);
```

### Setup Function to TRIGGER

```sql
-- Function executed by TRIGGER

CREATE OR REPLACE FUNCTION fn_players_name_changes_log()
	RETURNS TRIGGER
	LANGUAGE PLPGSQL
	AS
$$
BEGIN

	IF NEW.name <> OLD.name THEN
		INSERT INTO player_audits
		( player_id, name, new_name, edit_date ) 
		values ( OLD.player_id, OLD.name, NEW.name ,NOW() );
	END IF;

	RETURN NEW;
END;
$$

-- TRIGGER Definition

CREATE TRIGGER trg_players_name_changes
	BEFORE UPDATE 
	ON players
	FOR EACH ROW
	EXECUTE PROCEDURE fn_players_name_changes_log();
```

## DML to fire above trigger

```sql
INSERT INTO players (name) VALUES ('UDAY'),('YADAV');

SELECT * FROM players;
select * from player_audits;

UPDATE players
SET name = 'UDAY 2'
WHERE player_id = 2;
```

## Another Trigger Example

```sql
-- create table for example

CREATE TABLE t_temperature_log (
	id_temperature SERIAL PRIMARY KEY,
	add_date TIMESTAMP,
	temperature NUMERIC
);

-- create function for trigger to execute

CREATE OR REPLACE FUNCTION fn_temperature_value_check_at_insert()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN

	IF NEW.temperature < -30 then
		NEW.temperature = 0;
	END IF;

	RETURN NEW;

END;
$$

-- creating trigger

CREATE TRIGGER trg_temperature_value_check_at_insert
BEFORE INSERT 
ON t_temperature_log
FOR EACH ROW
EXECUTE PROCEDURE fn_temperature_value_check_at_insert();

-- Queries

INSERT INTO t_temperature_log ( add_date, temperature )
values ( '2020-10-01' , 10 );

select * from t_temperature_log;

INSERT INTO t_temperature_log ( add_date, temperature )
values ( '2020-10-01' , -33 );

select * from t_temperature_log;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/functions/triggers.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.