SQL Table

phman1275 used Ask the Experts™
Hi, I need some help about trigger and calculation field. I am thinking set a Status field as a calculation field on the Components table. This field will show either "Ready", "Usable", "Not-Ready" depends on the score on the Inspection table. If greater 90 then consider "Ready", below 90 but above 75 then consider "Usable" otherwise "Not-Ready". I think I need the trigger to do that but I have no clue.

Here are the tables:

ComponentID, Version, OwnerID,  Status
1                        A11        100           Ready
2                        B23        200           Usable

ComponentID, Date,            Score
1                        1/3/2016     95
2                        2/13/2017   55
                          2/15/2017   65
                          2/17/2017   80

*** The third inspection result will reflect on Components table with the status "Usable".

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

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

    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'
        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'

Open 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]
      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)
                  SET @Status = 'Ready'
      ELSE IF (@Score BETWEEN 75 and 89)
                  SET @Status = 'Usable'
                  SET @Status = 'Not-Ready'

            INSERT INTO Components
            (ComponentID, ComponentName, Version, Size, Language, Owner, Status)
            SELECT @CompID, ComponentName, Version, Size, Language, Owner, @Status
            FROM inserted
Senior Developer
Did you test my trigger sample? T-SQL is the SQL dialect used by SQL Server...

Cause yours has some serious flaws:

1) A trigger in SQL Server is executed per statement, thus the virtual tables INSERTED and DELETE. It must be able to handle multiple rows. Therefore the EXISTS test in my sample using the INSERTED table. Your trigger will only handle one arbitrary row in a multiple row INSERT scenario.

2) The status according to your description depends on inspections, thus in a further inspection, it can degrade. Thus using MAX() makes no sense. You need to query the last inspections value.

3) You did not explain your model, but from the naming, components must exist before they can be inspected. Thus you don't need an INSERT in the trigger, but an UPDATE as in my sample.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial