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

User Defined Function to Convert Text String to Title Case – Proper Case

Most of the time we need a function to convert our string in Title Case (Proper Case) and we don’t have system defined function. Now following function will convert any string to Title Case.

Function as Below.

CREATE FUNCTION fnTitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/','&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index+ 1, 1)))
END
SET @Index = @Index + 1
END
RETURN ISNULL(@OutputString,'')
END

Run below T-SQL statement in query analyzer:

SELECT dbo.fnTitleCase('This function will convert in proper case.')

Output:

------------

This Function Will Convert In Proper Case.

Introduction to SIGN Function

SIGN Function is very basic function which is used to return value 1, -1 or 0. If value is negative it will return you negative -1 and if it is positive it will return you positive +1 and if value is 0 (zero) return 0(zero) value. Lets take the example as below

DECLARE @IntValue1 INT, @IntValue2 INT,@IntValue3 INT
DECLARE @DecValue1 DECIMAL(4,2), @DecValue2 DECIMAL(4,2),@DecValue3 DECIMAL(4,2)
SET @IntValue1 = 9; SET @IntValue2 = -9; SET @IntValue3 = 0;
SET @DecValue1 = 9.0; SET @DecValue2 = -9.0; SET @DecValue3 = 0.0;
SELECT SIGN(@IntValue1) IntValue1,SIGN(@IntValue2) IntValue2,SIGN(@IntValue3)IntValue3
SELECT SIGN(@DecValue1) DecValue1,SIGN(@DecValue2) DecValue2,SIGN(@DecValue3)DecValue3

The above function will give us following result set.

Output

IntValue1	IntValue2	IntValue3
1	-1	0

DecValue1	DecValue2	DecValue3
1	-1	0.00

You will notice that when there is positive value the function return positive values and if the values are negative it will return negative values. As the above result set you can also see that if the data type is INT the return value is INT and when the value passed to the function is Decimal the result also matches it. This is not true for every data type. This function is compatible with as below data type.

PassingDatatype --> ReturnDatatype

-------------------------------------------------------------------------------------

· bigint à bigint

· int/smallint/tinyint à int

· money/smallmoney à money

· numeric/decimal à numeric/decimal

· everything else à float

Now the question is that What will be the best example of the usage of this function. Answer is that you will not have to use the CASE Statement. Below is example of CASE Statement usage and the same replaced with SIGN function.

CREATE TABLE TestTable (Date1 SMALLDATETIME, Date2 SMALLDATETIME)
INSERT INTO TestTable (Date1, Date2)
SELECT '2012-06-23 17:15', '2012-06-20 17:15'
UNION ALL
SELECT '2012-06-24 17:15', '2012-06-23 17:15'
UNION ALL
SELECT '2012-06-23 17:15', '2012-06-23 17:15'
GO
-- Using Case Statement
SELECT CASE
WHEN DATEDIFF(d,Date1,Date2) > 0 THEN 1
WHEN DATEDIFF(d,Date1,Date2) < 0 THEN -1
ELSE 0 END AS Col
FROM TestTable
GO

The same output you will get from below query using Sign function

SELECT SIGN(DATEDIFF(d,Date1,Date2)) AS Col
FROM TestTable
GO
DROP TABLE TestTable
GO

This is a very small function, but useful in the above manner.