I am relatively new to databases and SQL, and I am not clear on how or whether transactions may relate to a problem that I am trying to solve. I want to be able to temporarily set a value in a database table, run some query, and then clear out the value that was set, and I don't want any operations outside of the transaction to be able to see or alter the temporary value that was set.
The reason I am doing this is so that I can create predefined views that query certain data depending on variables such as the current user's id. In order for the predefined view to have access to the current user's id, I would save the id into a special table just before querying the view, then delete the id immediately afterward. I don't want to worry about some other user overwriting the current user's id while the transaction is in process. Is this a proper use for a transaction?
I am using H2 if that makes a difference.
I think you want a Procedure or Function. Both can take a parameter as input.
ex.
CREATE PROCEDURE pr_emp
(
@input INT
)
AS
SELECT *
FROM myTable
WHERE emp_id = @input
ex.
CREATE FUNCTION v_empid (@input INT)
RETURNS TABLE
AS
RETURN
SELECT * FROM myTABLE WHERE emp_id = @input;
These could let you to access information for an empid. For example:
SELECT * FROM v_empid(32)