SQL Server Tutorial : How DML triggers are used

Want to learn more? Take the full course at https://learn.datacamp.com/courses/bu... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---

Now 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.

When 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 where 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 power values, however, and they 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!


#DataCamp #SQLServerTutorial