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
Next, 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)
This is achieved by performing an INNER JOIN with the INSERTED, DELETED and Student table. We all know that whenever any record is updated in a table, the INSERTED table holds the updated record whereas the DELETED table holds the original record. So if we perform an INNER JOIN with the INSERTED, DELETED and the main table, we will be able to capture the updated records.