Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Triggers | Some Additional Topics
course content

Course Content

Advanced Techniques in SQL

1. ACID

Course OverviewWhat is ACID?AtomicityChallenge: Transaction CreationConsistencyIsolation. Read Committed and Uncommitted LevelsIsolation. Repeatable Read and Serializable LevelsChallenge: Specifying Isolation Level for Transaction

2. Query optimization.Indexes

What are IndexesB-tree IndexingChallenge: Creating B-tree IndexHash IndexingData CachingGeneral Optimization StrategiesChallenge: Query Optimization

3. Some Additional Topics

TriggersChallenge: Creating After TriggerWindow FunctionsTypes of Window FunctionsChallenge: Using Window FunctionData Control LanguageViewsNoSQL

Advanced Techniques in SQL

TriggersTriggers

Triggers in databases are special types of stored procedures that are automatically executed or fired in response to certain events or actions occurring in the database.
These events can include insertions, updates, or data deletions in tables and changes to database schema objects like tables or views.

Note

Triggers cannot be invoked explicitly by users. Instead, they are automatically executed in response to the operations they are associated with.

DML triggers

A DML trigger, also known as a Data Manipulation Language trigger, is a database object that automatically executes a specified action when a certain event occurs on a table or view.
DML triggers are often used to enforce business rules, maintain data integrity, or perform auditing tasks.

DML trigger types

  • After Triggers: These triggers fire after the triggering action (insert, update, delete) has been executed on the table;
  • Before Triggers: These triggers fire before the triggering action. They allow you to intercept the original action and perform custom logic before deciding whether or not to proceed with the original action.

After trigger example

We've previously explored an example of using a transaction to add values to the BankAccounts table and the UserLogs table as a single logical unit in the first section of this course.
Now, instead of using a transaction, we can achieve this by creating an AFTER trigger on the INSERT operation within the BankAccounts table.
As a result, we will only be able to insert new accounts, and the corresponding log value will be automatically added by the trigger.

Query Description
  • Create an after insert trigger for user_logs:
    • Create a function named afterinserttriggerfunction().
    • The function is defined to return a trigger.
    • The function's body begins with a BEGIN block.
    • Inside the BEGIN block, an action is performed, which is inserting a record into userlogs table.
    • The inserted record contains values from the NEW row of the triggering event.
    • The values inserted include accountnumber and a concatenated string mentioning the action.
    • The function returns the NEW row.
    • The function is written in plpgsql language.
  • Attach the trigger to the bankaccounts table:
    • A trigger named afterinserttrigger is created.
    • The trigger is set to execute after an INSERT operation on the bankaccounts table.
    • The trigger is set to execute the afterinserttriggerfunction function for each affected row.

Note

In PostgreSQL triggers, NEW is a special record variable representing the new row inserted into or updated in the table. It allows you to access the values of columns in the new row within the trigger function.
If we work with deleted rows, we have to use OLD record variable instead of NEW.

Before trigger example

Assume we want to prevent adding values with a negative account balance. We can create a BEFORE trigger to achieve this. This trigger will check the balance before the operation, and if it is negative, the operation will not be performed.

Query Description
  • Create the trigger function:
    • Create or replace a function named before_insert_userlogs().
    • The function is defined to return a trigger.
    • The function's body begins with a BEGIN block.
    • Inside the BEGIN block, a condition checks if the account balance is less than 0.
    • If the balance is less than 0, a notice is raised and no action is logged. The function returns NULL to prevent the insertion.
    • If the balance is greater than or equal to 0, a notice is raised indicating the action is logged.
    • The function then inserts a record into the userlogs table, containing values from the NEW row.
    • The inserted values include accountnumber and a concatenated string indicating the action.
    • The function returns NEW to allow the insertion to proceed.
    • The function is written in plpgsql language.
  • Create the trigger:
    • A trigger named before_insert_userlogs_trigger is created.
    • The trigger is set to execute before an INSERT operation on the bankaccounts table.
    • The trigger is set to execute the before_insert_userlogs() function for each affected row.

Trigger creation pattern

We can observe the typical pattern for creating triggers: initially, we define a function to execute specific logic before or after an operation. Subsequently, we associate this function as a trigger to a designated table and action.

Please note that in the statement:

we have the flexibility to utilize UPDATE or DELETE, in place of INSERT operation.
But pay attention that we can't create SELECT triggers as the SELECT statement doesn't modify any rows in the table!

What is the primary purpose of using triggers in a database?

Select the correct answer

Everything was clear?

Section 3. Chapter 1
course content

Course Content

Advanced Techniques in SQL

1. ACID

Course OverviewWhat is ACID?AtomicityChallenge: Transaction CreationConsistencyIsolation. Read Committed and Uncommitted LevelsIsolation. Repeatable Read and Serializable LevelsChallenge: Specifying Isolation Level for Transaction

2. Query optimization.Indexes

What are IndexesB-tree IndexingChallenge: Creating B-tree IndexHash IndexingData CachingGeneral Optimization StrategiesChallenge: Query Optimization

3. Some Additional Topics

TriggersChallenge: Creating After TriggerWindow FunctionsTypes of Window FunctionsChallenge: Using Window FunctionData Control LanguageViewsNoSQL

Advanced Techniques in SQL

TriggersTriggers

Triggers in databases are special types of stored procedures that are automatically executed or fired in response to certain events or actions occurring in the database.
These events can include insertions, updates, or data deletions in tables and changes to database schema objects like tables or views.

Note

Triggers cannot be invoked explicitly by users. Instead, they are automatically executed in response to the operations they are associated with.

DML triggers

A DML trigger, also known as a Data Manipulation Language trigger, is a database object that automatically executes a specified action when a certain event occurs on a table or view.
DML triggers are often used to enforce business rules, maintain data integrity, or perform auditing tasks.

DML trigger types

  • After Triggers: These triggers fire after the triggering action (insert, update, delete) has been executed on the table;
  • Before Triggers: These triggers fire before the triggering action. They allow you to intercept the original action and perform custom logic before deciding whether or not to proceed with the original action.

After trigger example

We've previously explored an example of using a transaction to add values to the BankAccounts table and the UserLogs table as a single logical unit in the first section of this course.
Now, instead of using a transaction, we can achieve this by creating an AFTER trigger on the INSERT operation within the BankAccounts table.
As a result, we will only be able to insert new accounts, and the corresponding log value will be automatically added by the trigger.

Query Description
  • Create an after insert trigger for user_logs:
    • Create a function named afterinserttriggerfunction().
    • The function is defined to return a trigger.
    • The function's body begins with a BEGIN block.
    • Inside the BEGIN block, an action is performed, which is inserting a record into userlogs table.
    • The inserted record contains values from the NEW row of the triggering event.
    • The values inserted include accountnumber and a concatenated string mentioning the action.
    • The function returns the NEW row.
    • The function is written in plpgsql language.
  • Attach the trigger to the bankaccounts table:
    • A trigger named afterinserttrigger is created.
    • The trigger is set to execute after an INSERT operation on the bankaccounts table.
    • The trigger is set to execute the afterinserttriggerfunction function for each affected row.

Note

In PostgreSQL triggers, NEW is a special record variable representing the new row inserted into or updated in the table. It allows you to access the values of columns in the new row within the trigger function.
If we work with deleted rows, we have to use OLD record variable instead of NEW.

Before trigger example

Assume we want to prevent adding values with a negative account balance. We can create a BEFORE trigger to achieve this. This trigger will check the balance before the operation, and if it is negative, the operation will not be performed.

Query Description
  • Create the trigger function:
    • Create or replace a function named before_insert_userlogs().
    • The function is defined to return a trigger.
    • The function's body begins with a BEGIN block.
    • Inside the BEGIN block, a condition checks if the account balance is less than 0.
    • If the balance is less than 0, a notice is raised and no action is logged. The function returns NULL to prevent the insertion.
    • If the balance is greater than or equal to 0, a notice is raised indicating the action is logged.
    • The function then inserts a record into the userlogs table, containing values from the NEW row.
    • The inserted values include accountnumber and a concatenated string indicating the action.
    • The function returns NEW to allow the insertion to proceed.
    • The function is written in plpgsql language.
  • Create the trigger:
    • A trigger named before_insert_userlogs_trigger is created.
    • The trigger is set to execute before an INSERT operation on the bankaccounts table.
    • The trigger is set to execute the before_insert_userlogs() function for each affected row.

Trigger creation pattern

We can observe the typical pattern for creating triggers: initially, we define a function to execute specific logic before or after an operation. Subsequently, we associate this function as a trigger to a designated table and action.

Please note that in the statement:

we have the flexibility to utilize UPDATE or DELETE, in place of INSERT operation.
But pay attention that we can't create SELECT triggers as the SELECT statement doesn't modify any rows in the table!

What is the primary purpose of using triggers in a database?

Select the correct answer

Everything was clear?

Section 3. Chapter 1
some-alt