I have several tables that i need to add a trigger to. One such table is "Clusters". This table contains a column called ClusterFKey which should be the same value as the AutoID column in the Serverdata table (same db) and should be populated every time a record is added to the Clusters table. The value should be joined on the servername column's value in both the Serverdata and Clusters table.
So, when a record is added to Clusters, the ClusterFkey should equal the AutoID key in Serverdata where Clusters.servername = Serverdata.server and Clusters.ClusterFkey is null.
Yeah, so i got the idea, but that is as far as i am currently.
CREATE TRIGGER dbo.ServerDataTrigger
SET c.ClusterFKey = sd.AutoID
FROM Clusters c
INNER JOIN INSERTED i
ON i.PK = c.PK
INNER JOIN Serverdata sd
ON sd.server = c.servername
WHERE c.ClusterFkey is null
PK is the primary key of Clusters table in above code
i'm confused as to what the Inner Join Inserted line is actually doing. Can you explain?
It will make sure only the records that were a part of recent INSERT operation are picked for update operation
Internally SQL server makes use of two temporary tables (INSERTED & DELETED) for performing INSERT,UPDATE & DELETE operations INSERT - In this case INSERTED table will contain new rows that were inserted & DELETED table unused UPDATE - In this case INSERTED table will contain updated (modified) values and DELETED table will contain old (replaced) values DELETE - In this case DELETED tables will contain rows which were deleted and INSERTED table is unused.
These tables assumes the structure of main table on which DML operations take place (in this case same as table Clusters)