What is trigger in database?
Microsoft Net Framework

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 INSERTUPDATE, 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 INSERTDELETE, 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

Share This with your friend by choosing any social account


Upcoming Articles
You may also read following recent Post
Copyright Future Minutes © 2015- 2024 All Rights Reserved.   Terms of Service  |   Privacy Policy |  Contact US|  Pages|  Whats new?
Update on: Dec 20 2023 05:10 PM