Is there a best practice for creating index to reduce the IO for inserted scans that originate from a trigger? I have a table with roughly 2 million rows that has an insert and an update trigger that fires every time a new row is inserted or when an existing row is updated. From the estimated execution plan, the EstimatedIO for these scans is ~64. Currently, the table only has a clustered primary key index. Is there a specific way to approach index for inserted scans to reduce the overall IO of the process?
My only thought right now is to read the inserted row into a temp table, and then create a clustered index on the primary key of the temp table. The thinking is that the trigger is making subsequent calls for the inserted row, which causes a table scan to retrieve the data. Anything behind this logic?