SQL Server – Using DML Triggers to Capture Multiple Events

AS DBAs we often need to develop DML triggers to capture multiple entries which are either INSERTED, UPDATED or DELETED from a table. A few months back, I came across a situation where the Application Development Team asked me to write a logic  to notify the DBA  if any records were either INSERTED,UPDATED or DELETED from a particular table. The requirement was  that the trigger should capture multiple entries instead of just one single entry. Based on the requirements from the team, I finally decided to implement the required logic.

Whenever we talk about DML triggers,  we often forget the concept of MAGIC tables, i.e. INSERTED and DELETED tables. A DML TRIGGER cannot be written without using the MAGIC tables. Let’s take a a look at exactly what the MAGIC tables holds.

  ACTIVITY                   INSERTED                 DELETED
     DATA IS INSERTED      Holds The New Record.
     DATA IS UPDATED      Holds The New Record.  Holds The Old Record.
     DATA IS DELETED Holds The Deleted Records.

From the above table, we can see that whenever a new record is INSERTED into the main table. Whenever the record gets updated, the INSERTED table holds the new record whereas the DELETED table holds the old record. Whenever the record is deleted from the table, the DELETED table holds the deleted records. The INSERTED and DELETED table gets created only after DML operations i.e. INSERT, UPDATE or DELETE operations are performed on a table. The INSERTED and DELETED tables are an exact replica of the main table (i.e. the table on which the trigger is to be created).

In order to understand DML Triggers, let us consider a scenario where we have a table named student which has two columns named student_id and student_name. We need to notify whenever a new student is inserted, an existing student is updated or deleted from the table.

Create a table named student:

create table student
 
(

student_id int,
 
student_name varchar(100)
 
)

Logic to capture newly inserted records:

IF EXISTS(SELECT INS.STUDENT_NAME FROM INSERTED INS
	 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME)
	 
BEGIN
	 
create table #inserted
 
(
 
row_no int,
 
id int,
 
name varchar(20)
 
)
 
DECLARE @i INT
 
insert #inserted
 
SELECT ROW_NUMBER() OVER(ORDER BY INS.STUDENT_ID),INS.STUDENT_ID,INS.STUDENT_NAME FROM INSERTED INS
 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME
 
SET @i = 1
 
SELECT @row_no = MAX(row_no) from #inserted
 
WHILE(@i<= @row_no)
 
BEGIN
 
SELECT @STUDENT_NAME = NAME FROM #inserted where row_no = @i
 
PRINT ‘A new student named’+space(1)+CONVERT(VARCHAR(100),@STUDENT_NAME)+’ has been inserted.’
 
SET @i = @i+1
 
END
 
drop table #inserted
 
END

First of all, we need to check which new records are inserted into the table:

IF EXISTS(SELECT INS.STUDENT_NAME FROM INSERTED INS

INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME)

This is achieved by performing an INNER JOIN with the INSERTED and Student table. We all know that whenever a new record is inserted into the table, the INSERTED table holds the same, so if we perform a join with the INSERTED and the Student table, we will be able to capture the newly inserted records.

Create a temporary table named #inserted which holds the newly inserted rows:

create table #inserted
 
(
 
row_no int,
 
id int,
	 
name varchar(20)
 
)

This also contains a column named row_no, which we will be using to fetch the records row by row.

Newly inserted records are then placed in the temporary tables named #inserted:

insert #inserted
 
SELECT ROW_NUMBER() OVER(ORDER BY INS.STUDENT_ID),INS.STUDENT_ID,INS.STUDENT_NAME FROM INSERTED INS
	 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME
While loop continues to execute until the condition is satisfied:
 
WHILE(@i<= @row_no)
 
BEGIN
 
SELECT @STUDENT_NAME = NAME FROM #inserted where row_no = @i
 
PRINT ‘A new student named’+space(1)+CONVERT(VARCHAR(100),@STUDENT_NAME)+’ has been inserted.’
 
SET @i = @i+1
 
END

drop table #inserted
 
 END

1_SQL_Server_Using_DML_Triggers_to_Capture_Multiple_Events

Logic to capture updated records:

IF EXISTS(SELECT INS.STUDENT_NAME FROM INSERTED INS INNER JOIN DELETED DEL ON 

INS.STUDENT_NAME!=DEL.STUDENT_NAME
 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME)
 
BEGIN
 
DECLARE @j INT
 
DECLARE @row_no INT
 
SET @j =1
 
create table #updated
 
(
 
row_no int,
 
id int,
 
name varchar(20)
 
)
 
insert #updated
 
SELECT ROW_NUMBER() OVER(ORDER BY INS.STUDENT_ID),INS.STUDENT_ID,INS.STUDENT_NAME FROM INSERTED INS INNER JOIN DELETED DEL ON
	 
INS.STUDENT_NAME!=DEL.STUDENT_NAME
 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME
 
SELECT @row_no = MAX(row_no) from #updated
 
WHILE(@j<= @row_no)
 
BEGIN
 
SELECT @STUDENT_NAME = NAME FROM #updated where row_no = @j
 
SELECT
 
@OLDSTUDENT_NAME = DEL.STUDENT_NAME
 
FROM DELETED DEL
 
INNER JOIN INSERTED INS ON DEL.STUDENT_NAME!=INS.STUDENT_NAME
 
SELECT @STUDENT_NAME = NAME
 
FROM #updated where row_no = @j
 
PRINT ‘The following records have been updated:’
 
PRINT ‘OLD STUDENT NAME:’+CONVERT(VARCHAR(100),@OLDSTUDENT_NAME)+”
 
PRINT ‘NEW STUDENT NAME:’+CONVERT(VARCHAR(100),@STUDENT_NAME)+”
 
SET @j = @j+1
 
END
 
drop table #updated
 
END

First, we check whether any records are updated in the table:

   
IF EXISTS(SELECT INS.STUDENT_NAME FROM INSERTED INS INNER JOIN DELETED DEL ON
 
INS.STUDENT_NAME!=DEL.STUDENT_NAME
 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME)

Next we create a temporary table named #updated which holds the updated rows:

create table #updated

(

row_no int,

id int,

name varchar(20)

)

It also contains a column named row_no, which we will be using to fetch the records row by row.

Updated records are then placed in the temporary tables named #updated:

insert #updated
 
SELECT ROW_NUMBER() OVER(ORDER BY INS.STUDENT_ID),INS.STUDENT_ID,INS.STUDENT_NAME FROM INSERTED INS INNER JOIN DELETED DEL ON

INS.STUDENT_NAME!=DEL.STUDENT_NAME
 
INNER JOIN STUDENT S ON INS.STUDENT_NAME=S.STUDENT_NAME
While loop continues to execute until the condition is satisfied:
 
WHILE(@j<= @row_no)
 
BEGIN
 
SELECT @STUDENT_NAME = NAME FROM #updated where row_no = @j
 
SELECT
 
@OLDSTUDENT_NAME = DEL.STUDENT_NAME
 
FROM DELETED DEL
 
INNER JOIN INSERTED INS ON DEL.STUDENT_NAME!=INS.STUDENT_NAME
 
SELECT @STUDENT_NAME = NAME
 
FROM #updated where row_no = @j
 
PRINT ‘The following records have been updated:’
 
PRINT ‘OLD STUDENT NAME:’+CONVERT(VARCHAR(100),@OLDSTUDENT_NAME)+”
 
PRINT ‘NEW STUDENT NAME:’+CONVERT(VARCHAR(100),@STUDENT_NAME)+”
 
SET @j = @j+1
 
END
 
drop table #updated
 
END

When existing records are updated in the table, we get a notification message as shown in the screen capture below:

2_SQL_Server_Using_DML_Triggers_to_Capture_Multiple_Events

Logic to capture deleted records

IF EXISTS(SELECT STUDENT_NAME FROM DELETED)
 
BEGIN
 
DECLARE @k INT
 
create table #deleted

(
 
row_no int,
 
id int,
 
name varchar(20)
 
)
 
INSERT #deleted
 
SELECT ROW_NUMBER() OVER(ORDER BY STUDENT_ID),STUDENT_ID,STUDENT_NAME FROM DELETED
 
SET @k = 1
 
SELECT @row_no = MAX(row_no) from #deleted
	 
WHILE(@k<= @row_no)
 
BEGIN
 
SELECT @STUDENT_NAME = NAME FROM #deleted WHERE row_no = @k
 
PRINT ‘Student named’+space(1)+CONVERT(VARCHAR(100),@STUDENT_NAME)+space(1)+’has been deleted’
	 
SET @k = @k+1
 
END
 
DROP TABLE #deleted
 
END

First of all, we check whether any records are deleted from the table:

IF EXISTS(SELECT STUDENT_NAME FROM DELETED)

It also contains a column named row_no, which we will be using to fetch the records row by row.

Deleted records are then placed in the temporary tables named #deleted:

INSERT #deleted
 
SELECT ROW_NUMBER() OVER(ORDER BY STUDENT_ID),STUDENT_ID,STUDENT_NAME FROM DELETED
	 
WHILE(@k<= @row_no)
 
BEGIN
 
SELECT @STUDENT_NAME = NAME FROM #deleted WHERE row_no = @k
	 
PRINT ‘Student named’+space(1)+CONVERT(VARCHAR(100),@STUDENT_NAME)+space(1)+’has been deleted’
	 
SET @k = @k+1
 
END
	 
DROP TABLE #deleted
 
END

Now, whenever any record is deleted from a table, we get notification message as shown in the screen capture below:

3_SQL_Server_Using_DML_Triggers_to_Capture_Multiple_Events

Conclusion:

Thus we have seen that using DML Triggers, we can capture entries which are either INSERTED, UPDATED or DELETED from the table. Future enhancements could be modifying the above logic to notify users via email for any DML activity done on the table.

Many Thanks to all the viewers for giving their valuable time in reading the article. Sincere Thanks to the editor Amit Bansal and all the members of the Community because of whom i get an oppurtunity to learn more and more and excel in my career.

 

Regards

Satnam Singh

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook

   

Leave a Reply

Your email address will not be published.