SQL – Triggers

Triggers are extremely powerful and useful feature of SQL because they are completely Global and Stealthy. They are good and necessary for data integrity as well as complete the DBMS-specific activity under the control of the database where it belongs. They are basically used for audit logging.

“Triggers are stored programs, which are automatically executed or fired when some events occur on the table like insertion, deletion or updation of data. It is a database object which could be defined on the table, view, schema, or database with which the event is associated”.

Benefits of Triggers

There is any number of scenarios where a trigger is the best solution to a problem where you are not in immediate control of both the code and the database by applying a patch to your database immediately then you can put a trigger on a table to perform some additional functionality.

Triggers can be written for the following purposes:

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

As we know that triggers are necessary to good database design and they play a precious role during refactoring of database sachems, while renaming a column, or splitting a column into two columns or vice-versa and assisting the transition.

Disadvantages of Triggers

Triggers have a valid use but far too often as a retro-hack that ultimately makes things worse such as-

1. Trigger execution cannot be seen by the client application.
2. Trigger logic can be difficult to track.
3. The inheritors of your code might not realize triggers have been implemented.
4. Triggers are known for inhibiting performance because they have a small overhead over having the logic in the stored procedure.
5. Triggers work over tempdb, so extensive use of them needs to be taken into account when thinking about tempdb.
6. Sometimes it takes time to remember you have a trigger when something weird happens after data is updated / inserted to the table.

Type of Triggers

SQL have the following types of triggers as given below:

• DML Triggers - A DML trigger is an action programmed to execute when a Data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view.

1. A) Instead of Trigger: Instead of trigger are fired in place of the triggering action such as an insert, update, or delete
2. B) AfterTrigger: After trigger execute following the triggering action, such as an insert, update, or delete.

• CLR Triggers - CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.

• DDLTrigger – A DDL Trigger is a special kind of trigger that fires in response to Data Definition Language (DDL) statements like Drop Table, Create Table, Or Alter Table. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

• Logon trigger - This type of trigger is fired against a LOGON event before a user session is established to the SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.

Difference between Trigger and Stored Procedures

Contents	Triggers	Stored Procedures
Implementation	Triggers can only be implemented on tables or views. A trigger cannot be written within a stored procedure.	Stored Procedure in independent code that can be specific to database. A stored procedure can be written within a trigger
Event and action	Triggers are event and action based to perform some specific tasks and they are free to run automatically whenever the event is occurred on the table on which the trigger is defined	They are not based on the event and action. They are not able to run automatically and depend on the other manual process.
Execution Mode	They execute implicitly.	A stored procedure must be called explicitly.
Schedule Mode	This is not possible for triggers because they are event and action based.	Stored procedures can be easily scheduled through a job to execute on a predefined time.
Transactions	Transactions are not allowed in Triggers.	Transaction statements like begin transaction, commit transaction, and rollback can be used easily inside a stored procedure.
Parameters	This feature is not applicable in triggers.	Stored Procedures may or may not have one or more input/ output parameters to perform some tasks.
Front end applications	This feature is not applicable for triggers.	Stored Procedures can be easily called from the front end (.asp files, .aspx files, .ascx files, .rdl etc.)
Return Value specific	A trigger cannot return a value.	Stored procedures can return values.
Nesting	Directly you cannot call another trigger within a trigger.	A stored procedure can be called from inside another stored procedure.
Task Specific	The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.	Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks.

Now, we will learn and understand the functionality of each trigger and how they play in SQL. 
Click on DML Triggers to know more.
Click on Instead of Trigger to know more.
Click on After Trigger to know more.
Click on DDL Triggers to know more.
Click on Logon Triggers to know more.

Learn more on another features of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL - Stored Procedure Vs Ad-Hoc (In-Line)
8. SQL - Stored Procedure Vs Functions
9. SQL - Merge Statement
10. SQL - Functions
11. SQL - Cursors