TRIGGER in SQL Server (T-SQL)

A trigger is a special kind of a store procedure that fire implicitly when certain event occurs in database server. We can’t invoke triggers explicitly. It can be used to perform administrative tasks in database such as regulating database operation.

Types of TRIGGER:

1. DDL TRIGGER:

DDL triggers execute in response to a variety of Data Definition Language (DDL) events such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS statements and certain system stored procedures that perform DDL like action. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

• DDL triggers do not support INSTEAD OF feature.
• DDL triggers do not have feature of inserted and deleted tables like DML triggers.

Example :

CREATE TRIGGER PreventDropTable

ON DATABASE            

FOR DROP_TABLE  --DROP_TABLE is DDL Event See link DDL events 
          

AS

PRINT 'Drop table action not allowed.'

ROLLBACK;

GO

Droping table to test trigger action
DROP TABLE [EMPLOYEE]; It will not drop table due to triggering action

We can disable or enable trigger
--Disabling database trigger
DISABLE TRIGGER PreventDropTable ON DATABASE;

--Enabling database trigger

ENABLE TRIGGER PreventDropTable ON DATABASE;

2. DML TRIGGER:

DML trigger is performed on DML operation (INSERT, UPDATE, DELETE) to enforce rule and regulation on table. Trigger can prevent incorrect, unauthorized, inconsistent changes to data. 

A). AFTER TRIGGER (FOR TRIGGER)

After triggers run after INSERT, UPDATE or DELETE operation on table. They do not supported for views. 
AFTER TRIGGERS can be classified further into three types as: 

     i). AFTER INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

--Creating a sample table

CREATE TABLE empProfile

(id INT IDENTITY(1,1),

name VARCHAR(50),

gender VARCHAR(6),

create_date DATETIME,

update_date DATETIME

)

--Creating trigger to automatic update log

CREATE TRIGGER setCreateDate ON [dbo].[empProfile]

FOR  INSERT

AS

      --AUTOMATIC populate create_date

      UPDATE empProfile SET create_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)

      PRINT 'AFTER INSERT trigger fired.'

GO

Inserting value in database to test trigger action

INSERT INTO empProfile (name,gender)

SELECT 'MDF', 'MALE'

UNION ALL

SELECT 'ZTE', 'FEMALE'

UNION ALL

SELECT 'John', 'MALE'

UNION ALL

SELECT 'Abc', 'MALE'

SELECT * FROM empProfile

     ii). AFTER UPDATE Trigger

This trigger is fired after an update on the table.

Example:

--Creating trigger to automatic update log

CREATE TRIGGER setUpdateDate ON [dbo].[empProfile]

FOR  UPDATE

AS

--AUTOMATIC populate create_date

UPDATE empProfile SET update_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)

      PRINT 'AFTER Update trigger fired.'

GO

UPDATE empProfile SET name='XYZ' WHERE id=1

UPDATE empProfile SET name='MDF' WHERE id=3

     iii). AFTER DELETE Trigger

This trigger is fired after a delete on the table. 

Example:

CREATE TRIGGER onDeleteTest ON [dbo].[empProfile]

AFTER  DELETE

AS

      PRINT 'AFTER DELETE trigger fired.'

GO

Testing trigger action

DELETE FROM empProfile WHERE ID=2

A). Instead Of Triggers

Instead of trigger override default action of DML operation on table or view. After defining INSTEAD OF trigger if we want to delete a row then Delete operation trying to delete but actually row will not get deleted unless you issue another delete instruction with in trigger.

Advantage of INSTEAD OF trigger:

• It can enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.
• It can enable you to code logic that can reject DML operation according to condition.
• It enable to ignoring parts of a batch.

Note: INSTEAD OF DELETE and INSTEAD OF UPDATE trigger cannot be defined on table if a table has foreign key defined by using DELETE or UPDATE cascading action then INSTEAD OF DELETE and INSTEAD OF UPDATE cannot be defined.

INSTEAD OF trigger can be classified further into three types: 

     i). INSTEAD OF INSERT Trigger. 
Example :

CREATE TRIGGER onInsertTest ON [dbo].[empProfile]

INSTEAD OF  INSERT

AS

IF ((SELECT gender FROM inserted)='FEMALE')

RAISERROR('Can not INSERT FEMALE employee',16,1);

ELSE

INSERT INTO empProfile (name, gender)

SELECT name, gender FROM inserted

GO

Testing trigger action

INSERT INTO empProfile (name, gender)

SELECT 'DEVA', 'FEMALE'

     ii). INSTEAD OF UPDATE Trigger. 

Example :

CREATE TRIGGER onUpdateTest ON [dbo].[empProfile]

INSTEAD OF  UPDATE

AS

IF ((SELECT gender FROM inserted)='FEMALE')

RAISERROR('Can not update FEMALE employee',16,1);

ELSE

UPDATE empProfile SET name = (SELECT name FROM inserted) WHERE ID =(SELECT id FROM inserted)

GO

Testing trigger action

UPDATE empProfile set NAME='INDRA' WHERE ID = 2

     iii). INSTEAD OF DELETE Trigger. 

Example :

CREATE TRIGGER onDeleteTest ON [dbo].[empProfile]

INSTEAD OF  DELETE

AS

IF EXISTS( SELECT 1 FROM deleted WHERE gender ='FEMALE')

RAISERROR('Can not delete FEMALE employee',16,1);

ELSE

DELETE FROM empProfile WHERE ID IN (SELECT D.ID FROM deleted D)

GO

Testing trigger action

DELETE FROM empProfile WHERE ID IN (3,4)

3. LOGON TRIGGER

 Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. 

Example :

CREATE TRIGGER logging_logon

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO logHistoryTbl (sys_user, loginTime)

SELECT SYSTEM_USER,GETDATE()

END

GO