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

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.

 

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |