INSERT TRIGGERS and the UPDATE() function

Question

I have created an INSTEAD OF INSERT trigger on a view in my database. I want to know which columns are included in the column list of the INSERT statement on the view.

If you read the MSDN documentation for triggers the UPDATE() and COLUMNS_UPDATED() functions should satisfy this requirement. However, during my testing I found that regardless of what columns are in the INSERT column list the UPDATE() and COLUMNS_UPDATED() functions always return all columns from the view.

CREATE VIEW dbo.MyView (BatchId, [Status], OrderNumber, WhenClosed) AS
    SELECT bth.BatchId, bth.[Status], bth.OrderNumber,
            Private.ufxAdjustDateTime(bth.WhenClosed, bth.WhenClosedUtcOffset)
        FROM Private.Batch AS bth
GO
CREATE TRIGGER dbo.[MyView-Insert] ON dbo.MyView INSTEAD OF INSERT AS
BEGIN
    SET NOCOUNT ON

    DECLARE @batchIdIsSet BIT

    SELECT @batchIdIsSet = 0
    IF UPDATE(BatchId)
        SELECT @batchIdIsSet = 1

    INSERT INTO Private.Batch
        (BatchId, [Status], OrderNumber)
        SELECT CASE @batchIdSet
                   WHEN 1 THEN ins.BatchId
                   ELSE NEWID()
               END, ins.[Status], ins.OrderNumber
            FROM inserted AS ins
END

The reason I want to do this is that I need to modify an existing table and I have loads of legacy code that relies on it. So what I've done is created a new table, changed the old table to a view and created triggers, on the view, to allow INSERT, UPDATE and DELETE statements.

Now, the old table had defaults for certain columns, if the insert into the view uses the default I want to use a default in my insert into the new table. To do this I have to be able to figure out which columns had values [explicitly] supplied for the INSERT.

Checking to see if the column has NULL is not enough because the INSERT statement can explicitly set the field value to NULL and this is perfectly acceptable.

Hmmm, I hope this is clear. Kep.

Answer 1

On an INSERT statement, every column is affected. It either gets NULL, or the value you're specifying.

Checking for NULL would be the best option, but as you can't do that, I'm thinking you might be a bit stuck. Can you work out scenarios which might need to handle NULL explicitly?

Answer 2

For INSERT, everything is a change because it's a new row.

If you had an AFTER trigger, you could test to see if the inserted value is the default value. If the default is NULL (eg nullable and no default), then how can you distinguish if NULL is explicitly inserted in any trigger?

In a BEFORE trigger, I don't know if you can trap the default. Of course, if the default is NEWID() this still won't help you.

On the face of it, this can't be done in a trigger.