---
title: Database Systems Pre-Lab 6 Part II
---
<h1 style='border: none'><center>Database Systems Pre-Lab 6 Part II</center></h1>
<h1 style='border: none'><center>Triggers</center></h1>
<h5><center>The Islamic University of Gaza<br>Engineering Faculty<br>Department of Computer Engineering</center></h5>
<h6>Authors: Usama R. Al Zayan & Rasha E. Kahil<span style="float:right">2023/04/04</span></h6>
---
## Expecteed outcomes
* To be introduced to Database Triggers commands and concepts.
* To learn how to create and delete Triggers in PostgreSQL.
* To learn how to use Triggers in PostgreSQL.
## Lab 5: Time and Plan
| Tasks | Timing |
| -------- | -------- |
| Quiz 7 | 10 min |
| Task 1 | 40 min |
| Task 2 | 40 min |
# Triggers
<p style="text-align:justify">
Let's proceed to this powerful and dangerous đ thing in SQL, which is very beneficial in many cases, for example: When 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, and when you want to maintain complex data integrity rules which you cannot implement elsewhere except at the database level.
And here I should warn you, always remember your challenge when you are dealing with triggers, that you must know all the time that the trigger exists and precisely understand its logic to figure out the effects when data changes.
</p>
A PostgreSQL trigger is a ***function*** invoked **automatically** whenever an ***event*** associated with a ***table or view*** occurs. And even though PostgreSQL implements SQL standard, triggers in PostgreSQL have some specific features as follows:
1. 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.
* So, 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.
2. PostgreSQL allows you to define the **statement-level** trigger on views.
* PostgreSQL provides two main types of triggers: row and statement-level triggers. The differences between the two kinds are how many times the trigger is invoked and at what time. 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.
Now, we are ready to explore the basic anatomy of triggers in PostgreSQL:
## Create trigger
The trigger will be associated with the specified table, view, or foreign table and will execute the specified function ***function_name*** when certain operations are performed on that table. To create a trigger on a table, the user must have the `TRIGGER` privilege on the table. The user must also have `EXECUTE` privilege on the trigger function.
```sql
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
ON table_name
[FOR [EACH] {ROW | STATEMENT}]
EXECUTE FUNCTION trigger_function;
```
The event can be either `INSERT`, `UPDATE`, `DELETE` or `TRUNCATE`. You can define a trigger that fires `BEFORE` or `AFTER` event. The `INSTEAD OF` is used only for `INSERT`, `UPDATE`, or `DELETE` on a ***view***.
The row-level trigger can be specified by `FOR EACH ROW` clause and statement-level trigger by `FOR EACH STATEMENT` clause.
Again, 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.
## Alter trigger
To be allowed to change properties of an existing trigger, you must own the table on which the trigger acts.
```sql
ALTER TRIGGER name ON table_name RENAME TO new_name;
```
The `RENAME` clause changes the name of the given trigger without otherwise changing the trigger definition.
## Replace trigger
To change the specification of postgresql trigger, you can drop and create it again,
Postgresql **doesnât** have âcreate or replace syntax for triggersâ, but remember `CREATE OR REPLACE` ia an alternative to `DROP` > `CREATE` which is still always available.
## Enable/Disable trigger
When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the `DISABLE` and `ENABLE` clause with `ALTER TABLE`, **not** `ALTER TRIGGER`, `ALTER TABLE` has a convenient way to express the option of enabling or disabling all of a table's triggers at once.
```sql
ALTER TABLE name DISABLE TRIGGER [ trigger_name | ALL | USER ];
```
:::info
***trigger_name***: Name of a single trigger.
***ALL***: all triggers belonging to the table.
***USER***: all triggers belonging to the table.
:::
## Drop trigger
Finally, I guess we donât have to say that we can drop a trigger using drop trigger. Anyway, letâs include that syntax for the sake of completion.
```sql
DROP TRIGGER [ IF EXISTS ] name ON table [ CASCADE | RESTRICT ];
```
To execute this command, the current user **must be the owner** of the table for which the trigger is defined.
### Example 1:
Letâs take a look at an example of creating a new trigger. In this example, we want to create a function that inserts the old salary into the `salary_audits` table including instructor id, old salary and the time of change when the salary of an instructor changes. So first, let's create the table.
```sql=
drop table salary_audits;
CREATE TABLE salary_audits (
aud_id SERIAL PRIMARY KEY,
id varchar(5) NOT NULL,
salary VARCHAR(40) NOT NULL,
changed_on TIMESTAMP(6) NOT NULL);
```
Now, we create the function for the trigger, note that the defined function **must not** take any arguments and **must** return a trigger date type.
```sql=
CREATE OR REPLACE FUNCTION log_salary_changes()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.salary <> OLD.salary THEN
INSERT INTO salary_audits(id,salary,changed_on)
VALUES(OLD.id,OLD.salary,now());
END IF;
RETURN NEW;
END;
$BODY$
language 'plpgsql';
```
Then, we bind the trigger function to the `instructor` table. The trigger name is `salary_changes`. Before the value of the `salary` column is updated, the trigger function is automatically invoked to log the changes.
```sql=
CREATE TRIGGER salary_changes
BEFORE UPDATE
ON instructor
FOR EACH ROW
EXECUTE function log_salary_changes();
```
:::warning
Think, what will be changed if the trigger specified to be â`AFTER UPDATE`â?
:::
Finally, insert some sample data for testing, update some salaries, and then verify the contents of the `salary_audits` table.
### Example 2:
In this example, we want to create a trigger that inserts the names of departments with capitalizing the first letter of each word.
```sql=
CREATE OR REPLACE FUNCTION cap_names()
RETURNS trigger AS
$BODY$
BEGIN
NEW.dept_name = initcap( new.dept_name );
RETURN NEW;
END;
$BODY$
language 'plpgsql';
CREATE TRIGGER cap_instr_name
BEFORE INSERT
ON department
FOR EACH ROW
EXECUTE function cap_names();
```
:::warning
Discuss, what will be changed if the trigger specified to be â`BEFORE INSERT`â?
:::
## Refreshing materialized views automatically
Last lab, you were introduced to materialized views, you remember that we said âto load/refresh data into a materialized view, we use the refresh materialized view statementâ. Today, we are in the right position to discuss a way to automatically refresh these materialized views to keep them up-to-date.
We need not to say that there are other approaches to maintain automatic refreshes, which are more efficient than triggers in many situations, but for now, we are not in the right position to mention these approaches.
## Notes
* **Note 1:** Creating a trigger that will be executed once for a statement, is necessary here. Queries will modify multiple rows at once (if we will define this trigger as `FOR EACH ROW` the trigger would be executed after each row update which is actually not needed).
* **Note 2:** It is not possible to have a single trigger watch on multiple tables simultaneously using commas, we can use the same function for multiple triggers, but we have to `CREATE TRIGGER` for each table separately.
###### tags: `Database Systems` `Pre-Lab` `IUG` `Computer Engineering`
<center>End Of Pre-Lab 6 Part II</center>
Sign in with Wallet
Connect another wallet