is there anyway to cache data that can be used in a SQL server db trigger

Question

i have an orders table that has a userID column

i have a user table that has id, name,

i would like to have a database trigger that shows the insert, update or delete by name.

so i wind up having to do this join between these two tables on every single db trigger. I would think it would be better if i can one query upfront to map users to Ids and then reuse that "lookup " on my triggers . . is this possible?

DECLARE @oldId int
DECLARE @newId int
DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)

SELECT @oldId = (SELECT user_id FROM Deleted)
SELECT @newId = (SELECT user_id FROM Inserted)

SELECT @oldName = (SELECT name FROM users where id = @oldId)
SELECT @newName = (SELECT name FROM users where id = @newId)

  INSERT INTO History(id,  . . . 

Answer 1

How about you turn on Change Data Capture, and then get rid of all this code?

Edited to add the rest:

Actually, if you're considering the possibility of a scalar function to fetch the username, then don't. That's really bad because of the problems of scalar functions being procedural. You'd be better off with something like:

INSERT dbo.History (id, ...)
SELECT i.id, ...
FROM inserted i 
JOIN deleted d    ON d.id = i.id
JOIN dbo.users u  ON u.user_id = i.user_id;

As user_id is unique, and you have a FK whenever it's used, it shouldn't be a major problem. But yes, you need to repeat this logic in every trigger. If you don't want to repeat the logic, then use Change Data Capture in SQL 2008.

Answer 2

Good news, you are already are using a cache! Your SELECT name FROM users WHERE id = @id is going to fetch the name for the buffer pool cached pages. Believe you me, you won't be able to construct a better tuned, higher scale and faster cache than that.

Result caching may make sense in the client, where one can avoid the roundtrip to the database altogether. Or it may be valuable to cache some complex and long running query result. But inside a stored proc/trigger there is absolutely no value in caching a simple index lookup result.