Home Dashboard Directory Help

Insert Trigger - COLUMNS_INSERTED ( ) function by Mark Harvey1


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Suggestion
ID: 241423
Opened: 11/28/2006 12:11:16 PM
Access Restriction: Public
Duplicates: 360685


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
Sign in to post a workaround.