SQL Table

Normally you would not calculate it. Just query it in a view.

What RDBMS do you use? In T-SQL a trigger would look like e.g.

CREATE TRIGGER tr_Inspections_I
ON dbo.Inspections
AFTER INSERT
AS
    SET NOCOUNT ON;

    WITH LastInspections AS
        (
            SELECT  *,
                    ROW_NUMBER() OVER ( PARTITION BY Ins.ComponentID ORDER BY Ins.Date DESC ) AS RN
            FROM    dbo.Inspections Ins
            WHERE   EXISTS ( SELECT * FROM INSERTED I ON I.ComponentID = Ins.ComponentID )
        )
        UPDATE  C
        SET     C.Status = CASE WHEN LI.Score > 90 THEN 'Ready'
                                WHEN LI.Score <= 90 AND LI.Score > 75 THEN 'Ready'
                                ELSE 'Not-Ready'
                            END
        FROM    Components C
            INNER JOIN LastInspections LI ON LI.ComponentID = C.ComponentID
        WHERE   LI.RN = 1
            AND C.Status != CASE WHEN LI.Score > 90 THEN 'Ready'
                                 WHEN LI.Score <= 90 AND LI.Score > 75 THEN 'Ready'
                                 ELSE 'Not-Ready'
                            END;

Select allOpen in new window

I am using SQL Server. I got cannot add duplicate error because the primary key. The following is my code.

create trigger [dbo].[ComponentInspection]
      ON [dbo].[Components]
      FOR INSERT
AS
BEGIN
      DECLARE @CompID int
      DECLARE @Status varchar(10)
      DECLARE @Score int

      SELECT @CompID = ComponentID FROM Inserted;

      SET @Score = (select max(Score) as Score from Inspections where componentid = @CompID)

      IF (@Score >= 90)
            BEGIN
                  SET @Status = 'Ready'
            END
      ELSE IF (@Score BETWEEN 75 and 89)
            BEGIN
                  SET @Status = 'Usable'
            END
      ELSE
            BEGIN
                  SET @Status = 'Not-Ready'
            END

            INSERT INTO Components
            (ComponentID, ComponentName, Version, Size, Language, Owner, Status)
            SELECT @CompID, ComponentName, Version, Size, Language, Owner, @Status
            FROM inserted
END