The Astonishing World of SQL Triggers

We will dive deeper into triggers in this article to learn what they are, why we use them, and how to play around with them. 🫡

🤨 What is triggers?

Triggers are unique stored procedures that are automatically run whenever a particular database activity takes place. On particular tables, triggers may be connected to INSERT, UPDATE, TRUNCATE, and DELETE actions (or any combination thereof).

Triggers are connected to specific tables, as opposed to stored procedures, which are merely stored SQL statements. Only when a row is added to the Customers table will a trigger related to INSERT actions on that table be activated. Similar to the Mobs table, a trigger on INSERT and UPDATE operations will only be activated when those particular operations take place on that table.

Triggers can be executed before or after a specified operation is performed.

Despite the fact that PostgreSQL implements the SQL standard, triggers in this database include the following unique features:

1. The [TRUNCATE] event trigger is fired by PostgreSQL.
2. You can create a statement-level trigger on views in PostgreSQL.
3. While the SQL standard permits you to use any SQL command as the trigger’s action, PostgreSQL requires you to write a user-defined function.

📝 Note: Constraints Are Faster Than Triggers

Use constraints instead of triggers wherever possible because they are typically processed more quickly.

⚔️ Types of Triggers

Row and statement-level triggers are the two basic categories of triggers offered by PostgreSQL. How frequently and when the trigger is triggered differs between the two types.

1. Statement Level Trigger: No matter how many rows are updated, it will only call the trigger function once for each statement.
2. Row Level Trigger: The trigger function will be called for each row that is modified by the event if the trigger.

👨🏻‍💻 Uses of Triggers

1. Performing extra validation and undoing any necessary data changes, such as checking to see whether a customer’s credit limit has been reached and stopping the insertion if it has.
2. Ensuring data consistency, such as during an INSERT or UPDATE transaction, changing all state names to uppercase.
3. keeping track of table transactions by recording event information..
4. Based on changes to a table, take action on other tables; for instance, every time a row is modified or removed, write an audit trail record to a log table.
5. Forcing Check Constraints.

🥸 Using Triggers

A trigger function must first be defined before it can be linked to a table in order to establish a new trigger. A trigger and a user-defined function differ in that a trigger is automatically called when a triggering event takes place.

You can see the code in this gist

The syntax of the CREATE TRIGGER is as follows:

• Name: the trigger’s given name. Any other trigger from the same table will not share this name.
• Before: shows that the function is being called before the event.
• After: shows that we are utilizing the function post-event.
• Instead Of: shows that we are using the function rather than the event to make the call.
• Event: The trigger will be fired by any event, including insert, update, delete, and truncate.
• Table name: Name of the table or view
• Referenced table name: This is the name of the other tables that the constraints relate to. Only constraints triggers can be defined with this option.
• DEFERRABLE, NOT DEFERRABLE, INITIALLY IMMEDIATE, and INITIALLY DEFERRED: This is the PostgreSQL trigger’s default time.
• For each row: will state that every row impacted by trigger events will cause a single firing of the trigger.
• For each statement: With this, it will be made clear that it will only ever be fired once for each statement impacted by a trigger event.
• Condition: Boolean expression that determines that trigger function will be executed.
• Function name: User-supplied function.
• Arguments: This is an optional comma-separated argument provided to the function, then the trigger was executing.
• The function is defined in the following compatible languages:
  1. PL/pgsql
  2. PL/Python
  3. Pl/ Java

For a more detailed description and available options, check the PostgreSQL documentation

📱 INSERT Trigger

The INSERT event trigger gets called when a new record is added to a table using the INSERT statement.

Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named “Customer” as follows:

In order to add an entry to the “Customer Division” table whenever a new customer record is added to the “Customer” table, we must first establish a trigger.

Insert trigger function and CREATE TRIGGER command:

🗑 DELETE Trigger

The DELETE event trigger, which can be added on transactions that DELETE the records.

🤖 UPDATE Trigger

The UPDATE event trigger gets called at the time of UPDATE statement execution.

🫳🏼 Dropping Trigger

DROP TRIGGER is used to remove a trigger.

🛒 Listing Triggers

You can list down all the triggers in the current database from the pg_trigger table as follows:

The above given PostgreSQL statement will list down all the triggers.

⚠️ Important Points To Remember

1. The user needs to have the TRIGGER privilege on the table and the EXECUTE privilege on the trigger function in order to establish a trigger on a table.
2. You may look up the database’s current trigger information by checking the system catalogue “pg trigger”.
3. Multiple triggers on the same object for the same event will fire in the order listed by name if you create them.
4. An automatic function called a PostgreSQL trigger is activated whenever a database event takes place on a database object, such as a table.
5. A trigger only exists during the lifetime of the database object for which it was created and if the database object is deleted, the trigger will also be deleted.

I hope you found this article useful and fun ❤️