Koen Verbeeck (2/1/2012)

---

Great question. I was looking for a trap, but apparently there wasn't any

GO
CREATE TABLE dbo.Test
(
    ID              integer IDENTITY PRIMARY KEY,
    AuditDateTime   datetime NOT NULL
);
GO
CREATE TRIGGER [trg dbo.Test IOI AuditDateTime]
ON dbo.Test
INSTEAD OF INSERT
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;
    
    INSERT dbo.Test
        (AuditDateTime)
    SELECT
        GETUTCDATE()
    FROM INSERTED AS ins
END;

DECLARE @Output TABLE (ID integer, AuditDateTime datetime);

INSERT dbo.Test
OUTPUT INSERTED.*
INTO @Output
DEFAULT VALUES;

-- Zero and NULL!
SELECT * FROM @Output AS o;
GO
DROP TABLE Test;