0

I have a script that uploads data into a TestResults table which has a foreign key connection to an entry in the TestInfo table.

TestResults: id (pk) | test_name (FK) | result | run_date | issues_snapshot | circles_snapshot | ...

TestInfo: test_name (PK) | issues | is_active | last_run_date | circles | ...

Two things need to happen when a new row is inserted into TestResults:

  1. The last_run_date in TestInfo needs to be updated with the run_date of the newly inserted row
  2. The newly inserted row's issues_snapshot and circles_snapshot needs to be set with the values of the connected test_info's issues and circles from the TestInfo table

I have this trigger but it's setting the values for issues_snapshot and circles_snapshot for EVERY row in TestResults, instead of just the recently inserted row. The last_run_date in TestInfo is properly updated.

CREATE TRIGGER [Test_Result_Trigger] ON dbo.TestResults
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON

    UPDATE t
    SET t.last_run_date = i.run_date
    FROM TestInfo AS t
    INNER JOIN inserted AS i
    ON t.test_name = i.test_name;

    UPDATE dbo.TestResults
    SET
        dbo.TestResults.circles_snapshot = t.circles_snapshot,
        dbo.TestResults.issues_snapshot = t.issues
        output inserted.test_name
    FROM dbo.TestInfo as t
    INNER JOIN inserted AS i
    ON i.test_name = t.test_name
    WHERE i.test_name = t.test_name
END

I also added an output inserted.test_name output block in the second update block in the trigger and it printed out every test_name within my sample data in TestResults, even those that don't match the recently inserted test_name that set off the trigger.

Another issue is that TestResults can have multiple of the same test_name, so I'd only want to update the one that is currently being inserted.

This is in SQL Server 12 and I'm testing within SQL Server Management Studio connected to an Azure SQL DB.

I am able to do this within a Python script that uploads the test results but thought a trigger would make sense.

1
  • 1
    so I'd only want to update the one that is currently being inserted. You join on the primary key. No point in guessing what that is - which is why posting DDL is highly recommended.
    – SMor
    Oct 16 at 2:48
1

I think you forgot the TestResults table in the FROM clause:

CREATE TRIGGER [Test_Result_Trigger] ON dbo.TestResults
AFTER INSERT
AS BEGIN
SET NOCOUNT ON

UPDATE t
SET t.last_run_date = i.run_date
FROM TestInfo AS t
INNER JOIN inserted AS i
ON t.test_name = i.test_name;

UPDATE tr
SET
    circles_snapshot = t.circles_snapshot,
    issues_snapshot = t.issues
OUTPUT inserted.test_name
FROM dbo.TestResults tr
    INNER JOIN inserted AS i ON tr.id=i.id
    INNER JOIN dbo.TestInfo as t ON tr.test_name=t.test_name

END
1
  • That was it. Thank you!
    – mmo
    Oct 18 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.