Triggers: - A trigger is a set of SQL statements stored in the database structure. A Trigger is a database object that is attached with the table in SQL.A SQL trigger is fired whenever an event or function associated with a table occurs e.g., after insert, after update or after delete. It means you will be executing some queries after an action query.
A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.
A trigger is simply a method that allows developers of to do additional work to take place every time a table is modified such as calling an event or manipulation of interrelated tables. For example, from a trigger on the Orders table, you can update the total sum of order sales for the salesperson in another table. Or more simply, if someone deletes a row of important data in one table, you can write a log of who, what and when to another table.
Also Read: Triggers in SQL
When & why to use Trigger?
If you wish to tell a user that whenever you (the user) try to update an existing table or try to insert something or try to delete something into or out of the table I wish to fire some events that tells the user about the restriction or access control you have specified over the table, then at that point you need to use trigger.
E.g. suppose there is an online survey or feedback about functionalities of Indian railway then whenever a new feedback is reported it is get stored in the SQL database, then there is a TRIGGER set which calls and event that automatically arranges all the feedback scraps according to their location or state wise.
2) Suppose there are two tables name ‘emp’ and ‘emp_backup’, I want some data should be inserted in the emp_backup also. So in this case we will be using a trigger to insert data in ‘emp_backup’ after insertion in ‘emp’.
Advantages of trigger:
1) SQL triggers are very useful to audit the changes of data in tables.
2) Triggers are used for calling stored procedures.
3) It provides an alternate way to check integrity and run scheduled tasks.
4) SQL triggers can catch errors in business logic in the database layer.
Disadvantages of trigger:
1) Triggers increase the complexity of a database. It is avoided by most of the Developers.
2) Due to the complex nature of the database programs it takes more time to execute.
3) Triggers run every time when the database fields are updated and it is overhead on system.
4) SQL triggers are invoked and executed invisible from the client applications, therefore, it is difficult to figure out what happen in the database layer.
Leave Comment