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
Whenever new entries are inserted in the student table, we get the notification message as shown in the screen capture below:
