Conteúdo do Curso
Advanced Techniques in SQL
Advanced Techniques in SQL
Triggers
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 intouserlogs
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.
- Create a function named
-
Attach the trigger to the bankaccounts table:
- A trigger named
afterinserttrigger
is created. - The trigger is set to execute after an
INSERT
operation on thebankaccounts
table. - The trigger is set to execute the
afterinserttriggerfunction
function for each affected row.
- A trigger named
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 useOLD
record variable instead ofNEW
.
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 theNEW
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 or replace a function named
-
Create the trigger:
- A trigger named
before_insert_userlogs_trigger
is created. - The trigger is set to execute before an
INSERT
operation on thebankaccounts
table. - The trigger is set to execute the
before_insert_userlogs()
function for each affected row.
- A trigger named
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!
Tudo estava claro?
Conteúdo do Curso
Advanced Techniques in SQL
Advanced Techniques in SQL
Triggers
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 intouserlogs
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.
- Create a function named
-
Attach the trigger to the bankaccounts table:
- A trigger named
afterinserttrigger
is created. - The trigger is set to execute after an
INSERT
operation on thebankaccounts
table. - The trigger is set to execute the
afterinserttriggerfunction
function for each affected row.
- A trigger named
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 useOLD
record variable instead ofNEW
.
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 theNEW
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 or replace a function named
-
Create the trigger:
- A trigger named
before_insert_userlogs_trigger
is created. - The trigger is set to execute before an
INSERT
operation on thebankaccounts
table. - The trigger is set to execute the
before_insert_userlogs()
function for each affected row.
- A trigger named
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!
Tudo estava claro?