Insert Trigger - COLUMNS_INSERTED ( ) function - by Mark Harvey1

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 241423 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 11/28/2006 12:11:16 PM
Duplicates 360685 Access Restriction Public


I would like SQL 2005 to provide the capability to know which columns were listed in an Insert statement from within an (instead of) insert trigger.  

In particular I require this information for an Instead of Insert Trigger created on a View.  

For Example;

Compare the two statements ;

   insert into vw_example(col1, col2, col3)
      values (1, null, null)

   insert into vw_example (col1, col2)
      values (1, null)

Both statements appear the same from within the instead-of insert trigger, however,  the second statement requires different processing to the first - in that it requires a default to be assigned to col3.  

From within the Trigger both sets of "inserted" data contain nulls for col3, and thus, in the second example, col3 (which physically allows nulls) cannot be distinguished and be defaulted to a value, as is the intention when it is omitted from the insert parameter list (in this case).
Sign in to post a comment.
Posted by Microsoft on 5/24/2007 at 3:18 PM

Thank you for your feedback. Your request for a function that indicates which columns were inserted when called inside the body of a trigger is very useful. We will take your request into consideration for a future release of SQL Server.

Thanks once again!

-- SQL Server Team