SQL Table

phman1275
phman1275 used Ask the Experts™
on
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:

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

Insepctions
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".

Thanks
Comment
Watch Question

Do more with

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

Commented:
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;

Open in new window

Author

Commented:
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
Senior Developer
Commented:
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