Unanswered: Help with Performance on a Conditional Trigger
I have a pretty massive conditional trigger. If there is another way of going about this, please let me know. But I'm populating a temp table with records and based on many conditions, I am transforming this data to another table in a corrected format. These conditions I am using reference the final table in many ways, and this seems to become slower and slower as the final table grows larger.
There are some major issues with your trigger. The length and repetetiveness is just symptomatic.
When you evaluate statements like:
IF (SELECT RESV_TOTAL FROM inserted) = 0
...you need to keep in mind that the trigger executes once for every insert statement, not once for every inserted record. If you load 100 records into this table the trigger is still going to fire only once, and thus SELECT RESV_TOTAL FROM inserted will return more than one record and can't be compared to the scalar value "0".
IF (SELECT COUNT(*) FROM RESERVATION_MASTER RM, inserted
WHERE RM.BOOK_NO = inserted.BOOK_NO) >= @@ROWCOUNT
...I can't even be sure which last successful transaction @@ROWCOUNT refers to, and I'm not convinced you can either.
I suggest you back off, think about your application process a bit more, and try to write up what you are trying to accomplish as succinctly as possible. Having a clear picture of Point A and Point B often helps in devising the best route from Point A to Point B.
If it's not practically useful, then it's practically useless.
it certainly does appear that you're not quite consistent in the method of identifying your conditions. but i'd say that can be fixed once you follow blindman's advice. what cannot be fixed is the problem that you addressed us with in the first place, - "it seems to become slower and slower as the final table grows larger."
if you can revamp your insert process by referencing that final table through a lefty outer join (left that is.) i'd even go further, i'd create a view using that join, and bcp records out. then, i'd drop the trigger all together and just use bulk insert. after all, at that point i only have records that i need.