Next we create a temporary table named #inserted 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:
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)
This is achieved by just performing a simple check on the DELETED table. Whenever any record is deleted from the table, the DELETED table holds that entry.
Then we create a temporary table named #deleted which holds the deleted rows:
create table #deleted
(
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.
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:
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.
]]>