The Purpose and Benefits of Triggers in Database Management

Developers and database administrators can create and use triggers for a multitude of purposes. The main reason for using triggers is to initiate actions when manipulating data, inserting, modifying, or deleting information. Sometimes, the manipulation of data needs to be prevented, and this can also be done with the use of triggers.

Another use case often seen in practice is using triggers for tracking data changes and even database object changes. Database administrators also use triggers to track user actions and to secure the database by protecting it from unwanted changes. Understanding the difference between the after and instead of trigger types is crucial so that you know when to use each.

From a trigger definition perspective, the two types of triggers look almost the same but in practice, they will have very different outcomes when fired. The outcome of the trigger execution is highly dependent on the keyword used: "after" or "instead of". The after trigger adds new statements to the initial one, while the instead of trigger prevents the initial statement from executing.

This difference will influence the use cases for each type of trigger. For example, an after trigger can be used in a large insert operation into a sales table where data needs to be cleansed after insertion. Once the data gets inserted, a cleansing step should run to remove or repair any unwanted information. The cleansing step will be started by the trigger and the cleansing step finishes with a report that includes the results. This report should be analyzed by a database administrator, and the trigger will then send an email to the responsible people as part of the trigger definition.

On the other hand, an instead of trigger can be used in more complex situations where updates need to be initiated but certain conditions need to be met before they are executed. For instance, imagine having a table containing light bulb information and stock details for a sales platform. The power column values have changed for some models, and an update is initiated to change the information in the table. However, there are still some bulb models in stock that have old part values, and it shouldn't be updated.

An instead of trigger can help deal with this more complex situation by updating the characteristics only for the models that don't have the old version in stock. The new models need to be in the table too but as new rows instead of updated ones. This approach ensures that the correct information is updated while preventing any unwanted changes. The use cases of DML triggers are virtually limitless, and it doesn't mean triggers are the answer to all of your problems, which will be discussed in a subsequent lesson.

Recap of Triggers

Now that you have a basic understanding of what triggers are, let's recap what has been learned so far. Triggers can be used for a multitude of purposes, including tracking data changes and securing the database by protecting it from unwanted actions. It is essential to understand the difference between after and instead of trigger types to determine when to use each.

In summary, triggers offer numerous benefits in database management, including preventing unwanted data manipulation, tracking changes, and securing the database. By understanding the purpose and use cases of triggers, developers and database administrators can effectively utilize them to improve their database operations and performance.

"WEBVTTKind: captionsLanguage: ennow that you have a basic understanding of what triggers are let's find out more about the reasons for using them developers and database administrators can create and use triggers for a multitude of purposes the main reason for using triggers is to initiate actions when manipulating data inserting modifying or deleting information sometimes the manipulation of data needs to be prevented and this can also be done with the use of triggers another use case often seen in practice is using triggers for tracking data changes and even database object changes database admins also use triggers to track user actions and to secure the database by protecting it from unwanted changes as mentioned earlier it's important to understand the difference between the after and instead of trigger types so you know when to use each from a trigger definition perspective the two types of triggers look almost the same but in practice they will have very different outcomes when fired the outcome of the trigger execution is highly dependent on the keyword you choose after or instead of the after trigger adds new statements to the initial one while the instead of trigger prevents the initial statement from executing this difference will influence the use cases for each type of trigger one example of using an after trigger is for a large insert of data into a sales table once that data gets inserted a cleansing step should run to remove or repair any unwanted information the cleansing step will be started by the trigger and the cleansing step finishes a report with the results will be generated this report should be analyzed by a database administrator so the trigger will then send an email to the responsible people this is all part of the trigger definition now let's look at an example we're using an instead of trigger is appropriate imagine you have a table containing light bulb information and stock details for a sales platform the power column values have changed for some models and an update is initiated to change the information in the table there are still some bulb models in stock that have the old part values however and it shouldn't be updated an instead of trigger can help you deal with this more complex situation the correct approach is to update the characteristics only for the models that don't have the old version in stock the new models need to be in the table too but as new rows instead of updated ones the only limit on the use cases of DML triggers is your imagination this doesn't mean triggers are the answer to all of your problems but that's a topic we'll discuss in the next lesson for now let's recap what you've learned with a couple of questionsnow that you have a basic understanding of what triggers are let's find out more about the reasons for using them developers and database administrators can create and use triggers for a multitude of purposes the main reason for using triggers is to initiate actions when manipulating data inserting modifying or deleting information sometimes the manipulation of data needs to be prevented and this can also be done with the use of triggers another use case often seen in practice is using triggers for tracking data changes and even database object changes database admins also use triggers to track user actions and to secure the database by protecting it from unwanted changes as mentioned earlier it's important to understand the difference between the after and instead of trigger types so you know when to use each from a trigger definition perspective the two types of triggers look almost the same but in practice they will have very different outcomes when fired the outcome of the trigger execution is highly dependent on the keyword you choose after or instead of the after trigger adds new statements to the initial one while the instead of trigger prevents the initial statement from executing this difference will influence the use cases for each type of trigger one example of using an after trigger is for a large insert of data into a sales table once that data gets inserted a cleansing step should run to remove or repair any unwanted information the cleansing step will be started by the trigger and the cleansing step finishes a report with the results will be generated this report should be analyzed by a database administrator so the trigger will then send an email to the responsible people this is all part of the trigger definition now let's look at an example we're using an instead of trigger is appropriate imagine you have a table containing light bulb information and stock details for a sales platform the power column values have changed for some models and an update is initiated to change the information in the table there are still some bulb models in stock that have the old part values however and it shouldn't be updated an instead of trigger can help you deal with this more complex situation the correct approach is to update the characteristics only for the models that don't have the old version in stock the new models need to be in the table too but as new rows instead of updated ones the only limit on the use cases of DML triggers is your imagination this doesn't mean triggers are the answer to all of your problems but that's a topic we'll discuss in the next lesson for now let's recap what you've learned with a couple of questions\n"