Hi All,
Can someone guide me to "write a sql block which will commit if both updates are successful else it will have to be rolled back"?
would like to explicitly specify both commit and rollback in code..
I would like to turn off the autocommit then execute the query.
Below is a just a starter ...it doesnt has COMMIT clause..
DO $$
DECLARE
emp_id1 INT := 1; -- Assuming employee ID for the first update
new_salary1 NUMERIC := 1; -- New salary for the first update
emp_id2 INT := 2; -- Assuming employee ID for the second update
new_salary2 NUMERIC := 3; -- New salary for the second update
BEGIN
-- Update Statement 1
UPDATE employees
SET salary = new_salary1
WHERE employee_id = emp_id1;
-- Update Statement 2
UPDATE employees
SET salary = new_salary2
WHERE employee_id = emp_id2;
EXCEPTION
WHEN OTHERS THEN
-- An error occurred during the update, log the error
RAISE NOTICE 'Error during updates: %', SQLERRM;
-- Roll back the transaction
ROLLBACK;
END $$;
select * from public.employees
Thanks,
Arun
