Kitsune Hifu Kitsune Hifu - 1 month ago 12
SQL Question

SQL Server trigger can't insert

I beginning to learn how to write trigger with this basic database.

I'm also making my very 1st database.

Schema

Team:

TeamID int PK (TeamID int IDENTITY(0,1) CONSTRAINT TeamID_PK PRIMARY KEY)
TeamName nvarchar(100)


History:

HistoryID int PK (HistoryID int IDENTITY(0,1) CONSTRAINT HistoryID_PK PRIMARY KEY)
TeamID int FK REF Team(TeamID)
WinCount int
LoseCount int


My trigger: when a new team is inserted, it should insert a new history row with that team id

CREATE TRIGGER after_insert_Player
ON Team
FOR INSERT
AS
BEGIN
INSERT INTO History (TeamID, WinCount, LoseCount)
SELECT DISTINCT i.TeamID
FROM Inserted i
LEFT JOIN History h ON h.TeamID = i.TeamID
AND h.WinCount = 0 AND h.LoseCount = 0
END


Executed it returns


The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


Please help thank. I'm using SQL Server

Answer

The error text is the best guide, it is so clear ..

You try inserting one value from i.TeamID into three columns (TeamID,WinCount,LoseCount)

consider these WinCount and LoseCount while inserting.


Note: I Think the structure of History table need to revisit, you should select WinCount and LoseCount as Expressions not as actual columns.