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!
Top comments (0)