Triggers in SQL Server

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. You can’t explicitly invoke triggers. The only way to do this is by performing the required action that they are assigned to. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement and you can specify the priority of execution of the trigger.

Types of Triggers

a) DML Trigger

b) DDL Trigger

c) Logon Trigger

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

Types of DML Triggers

a) AFTER trigger

AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed. AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.

Example

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT

AS

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

GO

b) INSTEAD OF trigger

INSTEAD OF triggers override the standard actions of the triggering statement. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows. For example, when the value being updated in an hourly wage column in a payroll table exceeds a specified value, a trigger can be defined to either produce an error message and roll back the transaction, or insert a new record into an audit trail before inserting the record into the payroll table. The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. For example, 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. Another advantage of INSTEAD OF triggers is that they enable you to code logic that can reject parts of a batch while letting other parts of a batch to succeed.

Example

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]

INSTEAD OF DELETE

AS

declare @emp_id int;

declare @emp_name varchar(100);

declare @emp_sal int;

select @emp_id=d.Emp_ID from deleted d;

select @emp_name=d.Emp_Name from deleted d;

select @emp_sal=d.Emp_Sal from deleted d;

BEGIN

if(@emp_sal>1200)

begin

RAISERROR('Cannot delete where salary > 1200',16,1);

ROLLBACK;

end

else

begin

delete from Employee_Test where Emp_ID=@emp_id;

COMMIT;

insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());

PRINT 'Record Deleted -- Instead Of Delete Trigger.'

end

END

GO

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. DDL trigger is only after trigger.

Example

IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = 'ddl_trig_database')

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

CREATE TRIGGER ddl_trig_database 

ON ALL SERVER 

FOR CREATE_DATABASE 

AS 

    PRINT 'Database Created.'

    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

GO

DROP TRIGGER ddl_trig_database

ON ALL SERVER;

GO

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

Example

USE master;

GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,

    CHECK_EXPIRATION = ON;

GO

GRANT VIEW SERVER STATE TO login_test;

GO

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'login_test'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'login_test' AND

    (SELECT COUNT(*) FROM sys.dm_exec_sessions

            WHERE is_user_process = 1 AND

                original_login_name = 'login_test') > 3

    ROLLBACK;

END;

Fixed Error: Operand data type bit is invalid for sum operator

While I am trying to add BIT filed in the SUM aggregation function and I got following error.

Error Received:

Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for sum operator.

For resolving this issue, we need to first cast BIT column value to INT and then pass it in SUM aggregation function. See the below example in details.

Reproduction of the error:

CREATE TABLE tbl_tmp (ID INT, Flag BIT)
GO
INSERT INTO tbl_tmp (ID, Flag)
SELECT 1, 0
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 0
UNION ALL
SELECT 4, 1
GO
SELECT *
FROM tbl_tmp
GO

Following script will work fine:
-- This will work fine
SELECT SUM(ID)
FROM tbl_tmp
GO

However following generate error:
-- This will generate error
SELECT SUM(Flag)
FROM tbl_tmp
GO

The workaround is to convert or cast the BIT to INT:
-- Workaround of error
SELECT SUM(CONVERT(INT, Flag))
FROM tbl_tmp
GO