Using DML Triggers to Capture Multiple Events

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.

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 |