January 18, 2015 at 11:39 pm
A table called "Employees" can have several triggers, assume there are 10 triggers created of all varieties insert, update and delete for the table that has 10 columns. It is said that logical tables inserted and deleted holds insert, updated and deleted records.
Q: If there is an update occurred on Employees, does it create an inserted table with 10 columns as well, having an exact column structure as the trigger's table, Employees?
Q: As mentioned in the above assume the table has 10 TRIGGERS, does SQL create a set of inerted and deleted logical tables per trigger, thus for 10 triggers on the same table, it should create 10 pairs of inserted and deleted logical tables?
thanks
January 19, 2015 at 12:21 am
Q: If there is an update occurred on Employees, does it create an inserted table with 10 columns as well, having an exact column structure as the trigger's table, Employees?
Ans: These two tables (inserted and deleted), if they contain anything, contain a row for every row of data affected and therefore should be treated as tables. The trigger only fires once per operation under normal circumstances. And since they should be treated as tables, use set-based operations on them whenever you can. Too many times we see cursors going row-by-row through these tables when a set based operation would have been better.
For more details refer to Use the inserted and deleted Tables
Q: As mentioned in the above assume the table has 10 TRIGGERS, does SQL create a set of inserted and deleted logical tables per trigger, thus for 10 triggers on the same table, it should create 10 pairs of inserted and deleted logical tables?
Ans: Run through this code and you will able to understand the working of inserted and deleted.
Create table tblCheck
(
ID int,
Name varchar(10)
)
GO
Create trigger trgCheckInstert on tblcheck
After Insert
AS
set nocount on;
update c
set c.Name = 'unknown'
from tblcheck c
join inserted i on c.id = i.id
where i.name is null
GO
Create trigger trgCheckInstert1 on tblcheck
After update
AS
Select i.Name as inserted_Name, d.Name as Deleted_Name
from inserted i
join deleted d on i.id = d.ID
GO
-------- Insert data into the tblcheck
insert into tblCheck
select 1, 'Jack'
insert into tblCheck
select 2, 'Jill'
insert into tblCheck
select 3, null
--- Cleanup
Drop trigger trgCheckInstert
Drop trigger trgCheckInstert1
drop table tblCheck
hope it helps. if any query do let us know.
January 19, 2015 at 12:46 am
Further on twin.devil's reply, the cardinality (#) of the two tables indicates the action, if #inserted > 0 and #deleted =0 then "insert", if #inserted > 0 and #deleted > 0 then "update" and if #inserted =0 and #deleted = 0 then "delete"
😎
January 19, 2015 at 12:53 am
Thanks, i forgot to mention that in my answer 🙂
January 19, 2015 at 4:29 am
Just to make it clear:
Q: If there is an update occurred on Employees, does it create an inserted table with 10 columns as well, having an exact column structure as the trigger's table, Employees?
Yes, "inserted" and "deleted" tables have exactly the same column's structure as the table subjected to data modification. (There are some limitation for accessing columns of text, ntext and image datatypes...)
Q: As mentioned in the above assume the table has 10 TRIGGERS, does SQL create a set of inerted and deleted logical tables per trigger, thus for 10 triggers on the same table, it should create 10 pairs of inserted and deleted logical tables?
Only one "inserted" and one "deleted" table will be there for one table for a single data modification action (actually, even if you will have no triggers at all, these two internal temporary in-memory tables will be there anyway...)
I guess BoL explains all well enough:
http://msdn.microsoft.com/en-GB/library/ms191300(v=sql.105).aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply