Stored procedure inserts 2 columns into a table with 3 columns that are not null, with no defaults on the table I struggled with how to ask this question. This is from a piece of software that we have used for well over a decade. For the life of me, I can't figure out why it works. There are 3 objects in question, a table, a view, and a stored procedure. Here is the definition of them:

Table:

CREATE TABLE [dbo].[AgentGroupsHistoryMap]  ( 
    [AgentGroupFK]  int NOT NULL,
    [UsersFK]       int NOT NULL,
    [Start]         datetime NOT NULL,
    [Stop]          datetime NULL,
    CONSTRAINT [PK_AgentGroupsHistoryMap] PRIMARY KEY CLUSTERED([AgentGroupFK],[UsersFK],[Start])
    WITH FILLFACTOR = 80 ON [PRIMARY])
ON [PRIMARY]
    WITH (
        DATA_COMPRESSION = NONE
    )
GO
CREATE NONCLUSTERED INDEX [IX_AgentGroup]
    ON [dbo].[AgentGroupsHistoryMap]([AgentGroupFK])
    WITH (
        FILLFACTOR = 80,    
        DATA_COMPRESSION = NONE
    )
    ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AgentGroupStop]
    ON [dbo].[AgentGroupsHistoryMap]([Stop])
    WITH (
        FILLFACTOR = 80,    
        DATA_COMPRESSION = NONE
    )
    ON [PRIMARY]
GO


View:

CREATE VIEW [dbo].[AgentGroupsMap] AS
    SELECT distinct AgentGroupFK, UsersFK from AgentGroupsHistoryMap where Stop is null

GO


Store Procedure:

CREATE PROCEDURE [dbo].[usp_AddAgentToGroup] @GroupKey int, @AgentKey int
AS
    SET NOCOUNT ON
    INSERT INTO dbo.AgentGroupsMap (dbo.AgentGroupsMap.AgentGroupFK, dbo.AgentGroupsMap.UsersFK)
    VALUES(@GroupKey, @AgentKey)
    RETURN
;

GO


This SQL was all written on a SQL 2000 system, but we've been able to keep it all up to date. When I started with it, we were on SQL 2005, and it went through 2008, and then we jumped to SQL 2014. 

That stored procedure, which seems like it shouldn't work; does. I just executed and watched the results.

This came up because in out test environment we started seeing an issue. Both production and test are on the same version of SQL. It works fine in production, but in test, we started getting this error:

&gt;[Error] Script lines: 1-1 --------------------------
 Id 4403, Level 16, State 1, Line 4
    Cannot update the view or function 'dbo.AgentGroupsMap' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
 Msg: 4403, Level: 16, State: 1, Procedure: usp_AddAgentToGroup, Line: 4


So I have 2 questions:


Why am I getting that error? I don't see any of the conditions that the error says are the case.
How in the world does this work? From what I can see, the sproc is able to insert into the "Start" field, which is not null and has no default, without specifying a value.
Someone else figured it out. In production, there is a trigger on the view that was not in place in testing.

2 of us didn't think to look for a trigger on a view. Mystery solved.