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:
- The last_run_date in TestInfo needs to be updated with the run_date of the newly inserted row
- 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.