Trigger:

When you create trigger for insert and delete we will get two reference table as “inserted” and “deleted” respectively now what if we create a trigger for “update” on trigger, your answer might be  we will get reference table “updated” 🙂 …. NO their is no updated table in trigger.

Here when we create a trigger for “update” on any table it will use two reference tables “deleted” and “inserted” to make the update.

Replication:

In the same way when we make any update on publisher it will go to subscriber as two statement Delete that records and insert that records.

As I already explain it here and also discuss about the issue we get due to this here.

We come across situation that when we have update on primary key. our replication may fail as update will be delete and insert to subscriber.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_xxxx'. The conflict occurred in database 'DBnamexxx', table 'tablexxxxx', column 'column_pk'.

I explain the resolution what I do here.

Update on table:

When we update the data from any column and that data size fits into the page that will be ok. but when the data is not fit into the page they in that case update will delete that records from that page and insert into new page…

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx.

Also update on huge data will also impact the performance. will try to blog more detail about Update in future.

Let me know your thought on this.

Thanx.

Vinay

@thakurvinay

http://www.eggheadcafe.com/software/aspnet/29906548/how-does-update-statement.aspx

.