Обсуждение: write a sql block which will commit if both updates are successful else it will have to be rolled back
write a sql block which will commit if both updates are successful else it will have to be rolled back
От
arun chirappurath
Дата:
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
Re: write a sql block which will commit if both updates are successful else it will have to be rolled back
От
"David G. Johnston"
Дата:
On Thursday, December 7, 2023, arun chirappurath <arunsnmimt@gmail.com> wrote:
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.
As documented under DO if you make an explicit transaction then execute the DO it cannot have transaction control commands. If you allow it to “auto-commit’’ then it can. Though in that case writing either rollback or commit for such a simple linear procedure becomes pointless since a transaction will already commit or rollback appropriately depending on whether the procedure provokes an exception.
David J.