- MS Sql Server
- 874
- March-22-2018
- by Arsal Ahmed
What is a Trigger?
A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBAs (Data Base Administrators) to create additional relationships between separate databases.
In other ways, a trigger can be defined to execute before or after an INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.
Triggers can be assigned to tables or views. However, although there are two types of triggers, INSTEAD OF and AFTER, only one type of trigger can be assigned to views.
An INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. An AFTER trigger fires after a modification action has occurred.
From a performance viewpoint, the fewer the triggers, the better, as we will invoke less processes. Therefore, do not think that having one trigger per action to make things modular will not incur performance degradation.
A trigger's main overhead is referencing either two specialist tables that exist in triggers – deleted and inserted or other tables for business rules. Modularizing triggers to make the whole process easier to understand will incur multiple references to these tables, and hence a greater overhead.
Types of Triggers:
There are three main types of triggers that fire on INSERT, DELETE, or UPDATE actions. Like stored procedures, these can also be encrypted for extra security.
Example:
Syntax of a Trigger
Hide Copy Code
CREATE TRIGGER name ON table [WITH ENCRYPTION] [FOR/AFTER/INSTEAD OF] [INSERT, UPDATE, DELETE] [NOT FOR REPLICATION] AS BEGIN --SQL statements ... END
Sample Example:
Let’s take an example. We have a table with some columns. Our goal is to create a TRIGGER which will be fired on every modification of data in each column and track the number of modifications of that column. The sample example is given below:
Hide Copy Code
-- ==========================================================
-- Author: Md. Marufuzzaman
-- Create date:
-- Description: Alter count for any modification
-- ===========================================================
CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @TransID VARCHAR(36)
SELECT @TransID = TransactionID FROM INSERTED
UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + 1
,LastUpdate = GETDATE()
WHERE TransactionID = @TransID
END
