Table Trigger | SQL

Let’s continue to improve our sample database, now by adding a table trigger on one of our tables.

The triggers can be either INSERT, UPDATE or DELETE clause when we speak in terms of D ata M anipulation L anguage. Its execution part usually contains a store procedure or a batch.

For demonstration purposes, let’s add two new columns to our customer table on our sample Database. (If you want to follow the next step and you missed the table creation, please check the following post).

Current Customer’s Table schema

ALTER TABLE to add columns to Customer’s table

Now that we have two new columns it’s time to add our table trigger, it will be a simple one that will update LastModificationDate column every time an update is performed for this table.

CREATE TRIGGER statement – example

Object Explorer View

I’ll now import a few more Customers to my table by using the CSV importation already learned here in this post. (Please have a look if you missed that one!)

Data belonging to the CSV file to be imported

All the dataset from the Customer’s table

Now that we have these two new columns and our new DML trigger, let’s test it by changing something on one of our rows. I think that nowadays Portuguese president should be annoyed by my mistake on his last name and is looking forward to the correction from “Sousa” to “de Sousa”. ( Sorry Professor :D).

UPDATE customer

After running our column update, if we select all table rows we’ll be able to find that LastModificationDate is now updated for the update execution DateTime.

All the dataset from the Customer’s table

Let me know if any doubt remained and stay safe!