Search code examples
sqlsql-servertriggersinsert

Why do I keep getting this error message when I try to insert something into a table


Here is the question that I have to answer:

  1. Create a trigger named Products_INSERT that inserts the current date for the DateAdded column of the Products table if the value for that column is null. Test this trigger with an appropriate INSERT statement.

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


Solution

  • 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.