How to use Triggers in Sql Server

Cursor- Sql Server
Cursor- Sql Server
0
(0)

How to use Triggers in Sql Server | What are different Type of Triggers

What is a Trigger?
Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. It can be executed automatically on insert, delete and update operations.

 

What are the types of Triggers?
===============================================================
===================================================================
DML Triggers in SQL Server
DML triggers in SQL Server are fired when a DML event occurs. i.e. when data is inserted/ updated/deleted in the table by a user. There are 4 types of DML triggers and they are:
Insert
Delete
Update Trigger
CREATE TRIGGER TR_UPD_Admissions
ON admissions 
FOR UPDATE 
NOT FOR REPLICATION 
AS 
BEGIN 
   INSERT INTO Enquries
   SELECT studentname,getdate() FROM inserted 
END

Instead of Trigger
These triggers are fired before the DML event and the actual data is not modified in the table.

Example:

A typical example of using an INSTEAD OF trigger is to override an insert, update, or delete operation on a view.

Suppose, an application needs to insert new admission into the admission table. However, the new admission should be stored in another table called admission_payments for checking whether payment actually received from student,  before creating a new tuple admission table.

CREATE TRIGGER TR_INS_Admissions
ON admissions
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO admission_payments ( 
        student_name, amount
    )
    SELECT
        i.student_name, amount
    FROM
        inserted i
    WHERE
        i.student_name NOT IN (
            SELECT 
                student_name
            FROM
                admissions
        );
END

 

List DML triggers in a database?

Select * from sys.objects where type='tr'

 

Where can we can see DML Triggers

 

Enabling and disabling DML triggers on a table

Navigate to triggers folder at the table level (as shown in image above), Right click on desired trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS.

OR

Enable / Disable through TSql

DISABLE TRIGGER TR_INS_Admissions on admissions
ENABLE TRIGGER TR_INS_Admissions on admissions

 

Delete DML triggers on a table

Navigate to triggers folder at the table level (as shown in image above), Right click on desired trigger and select delete using SSMS.

OR

Delete Trigger through TSql

DROP TRIGGER TR_INS_Admissions

===================================================================
===================================================================
DDL Triggers
DDL triggers are fired on DDL events like on create, alter and drop statements, etc. These triggers are created at the database level or server level based on the type of DDL event.

These triggers are useful in the below cases.

Prevent changes to the database schema
Audit database schema changes
To respond to a change in the database schema

ALTER TABLE Trigger
records all the alter statements against the table. You can write your custom code to track or audit the schema changes using EVENTDATA().

CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN 
  INSERT INTO TblSchemaChanges
  SELECT EVENTDATA(),GETDATE() 
END

 

LOGON triggers
These triggers in SQL Server fire in response to a LOGON event. LOGON triggers fire after successful authentication and before establishing the user session.

LOGON triggers are created at the server level and are useful below cases.
To audit login activity
To control the login activity

CREATE TRIGGER [track_logins] ON ALL SERVER
FOR LOGON AS
BEGIN
   INSERT INTO LoginActivity
   SELECT EVENTDATA(),GETDATE()
END

 

Direct recursion Trigger
Direct recursion is a case where the SQL Server trigger on the table is fired and performs an action which again triggers the same trigger.

CREATE TRIGGER TR_UPD_Admissions ON admission
FOR UPDATE
AS
BEGIN
Update admission set DateUpdated =GETDATE()
END

Where can we can see DDL Triggers
Expand the database and navigate to Programmability -> Database Triggers.

Enabling and disabling DDL triggers on a table

ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE

 

Delete DML triggers from database

DROP TRIGGER TR_DATABASEEVENTS ON DATABASE

 

Enabling and disabling LOGON triggers
Use below T-SQL syntax to disable or enable the LOGON trigger.

ENABLE TRIGGER track_logins ON ALL SERVER 
DISABLE TRIGGER track_logins ON ALL SERVER

 

Edit RECURSIVE TRIGGERS setting using SSMS
Right click on the database and select Properties. Click on Options and change the setting to the option you want.

===================================================================

 

 

 

 

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

1 Trackback / Pingback

  1. Sql Server Interview Questions - etechstuffs

Leave a Reply

Your email address will not be published.


*


12 − seven =