Advantages & Disadvantages of SQL Trigger

Advantages & Disadvantages of SQL Trigger

Introduction

Triggers are database objects which are needed to perform some predefined action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are helpful since it can help in maintaining the integrity of the data in database tables but they also hard to maintain as are difficult to find. Since they executed on every Insert/delete/update command on the table, they can slow down the performance. This article will discuss the advantages & disadvantages of SQL Trigger as it will help you in deciding whether to define the SQL Trigger or not.

If you want to study more about SQL Triggers you can read the article SQL Triggers – An Introduction

Advantages of SQL Triggers

1) It helps in maintaining the integrity constraints in the database tables, especially when the primary key and foreign key constrain are not defined.

2) It sometimes also helps in keeping the SQL codes short and simple as I show in the real-life example.

3) It helps in maintaining the track of all the changes (update, deletion and insertion) occurs in the tables through inserting the changes values in the audits tables.

4) Sometimes if the code is not well managed, then it can help in maintaining the database constraints defined on the tables on which the trigger is defined. For example, suppose if have a situation that there is an online learning system in which a user can register in the multiple course.

Disadvantages of Triggers

1) Hard to maintain since this may be a possibility that the new developer doesn’t able to know about the trigger defined in the database and wonder how data is inserted, deleted or updated automatically.

2) They are hard to debug since they are difficult to view as compared to stored procedures, views, functions, etc.

3) Excessive or over use of triggers can slow down the performance of the application since if we defined the triggers in many tables then they kept automatically executing every time data is inserted, deleted or updated in the tables (based on the trigger’s definition) and it makes the processing very slow.

4) If complex code is written in the triggers, then it will slow down the performance of the applications.

5) The cost of creation of triggers can be more on the tables on which frequency of DML (insert, delete and update)  operation like bulk insert is high.

Summary

This article tells about the various advantages and disadvantages of SQL Trigger. If you want more about SQL trigger , its various type & real world example of SQL trigger, you can read my article  SQL Triggers – An Introduction.

If you want to read more SQL Concepts, you can find the list of all SQL articles in page SQL Tutorial

If you want to read more Azure SQL Concepts, you can find the list of all Azure SQL articles in page Azure Tutorial

Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com.

Read More

Sql Server – How to write a Stored procedure in Sql server

SQL Triggers – An Introduction

SQL Script to find all the triggers defined on a database or on a single table

Advantages of SQL Stored Procedure

Difference between Stored Procedure and Trigger

 

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Server, SQL Triggers and tagged , . Bookmark the permalink.

Leave a Reply