Here is the question that I have to answer:
Here is the code that I have:
CREATE TRIGGER Products_INSERT
ON Products
AFTER INSERT
AS
UPDATE Products
SET DateAdded = GETDATE()
WHERE DateAdded IS NULL OR
DateAdded IN (SELECT DateAdded FROM inserted);
Here is my insert statement:
INSERT INTO Products
VALUES (4, 'LK-5300', 'Likeable Keyboard 5300',
'This keyboard is so cool, you just might flip!',
699.99, 30.00, NULL)
And here is the error I keep getting:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Products__Catego__145C0A3F". The conflict occurred in database "MyGuitarShop", table "dbo.Categories", column 'CategoryID'.
The statement has been terminated.
I know that the error has something to do with the foreign key but I'm not entirely sure. Any help would be appreciated.
EDIT:
CREATE TABLE Products (
ProductID INT PRIMARY KEY IDENTITY,
CategoryID INT REFERENCES Categories (CategoryID),
ProductCode VARCHAR(10) NOT NULL UNIQUE,
ProductName VARCHAR(255) NOT NULL,
Description TEXT NOT NULL,
ListPrice MONEY NOT NULL,
DiscountPercent MONEY NOT NULL DEFAULT 0.00,
DateAdded DATETIME DEFAULT NULL
);
Here is the Products table
Your INSERT
should be:
INSERT INTO Products
( CategoryID,
ProductCode,
ProductName,
Description,
ListPrice,
DiscountPercent,
DateAdded
)
VALUES (4, 'LK-5300', 'Likeable Keyboard 5300',
'This keyboard is so cool, you just might flip!',
699.99, 30.00, NULL)
Your INSERT was missing a value to go into the ProductID
column, since it is auto-generated because it is defined as IDENTITY but to get that to work, you need to name the columns, leaving out ProductID
.