Ülesanne transaktsioonid

Создание таблицы и внесение данных:

create table developers(
id int not null primary key,
name varchar (40),
specialty varchar(40),
experience int,
salary int);

insert into developers(id, name, specialty, experience, salary) 
values (1, 'Eugene Suleimanov', 'Java', 2, 2500);
insert into developers(id, name, specialty, experience, salary) 
values (2, 'Peter Romanenko', 'Java', 3, 3500);
insert into developers(id, name, specialty, experience, salary) 
values (3, 'Andrei Komarov', 'C++', 3, 2500);
insert into developers(id, name, specialty, experience, salary) 
values (4, 'Konstantin Geiko', 'C#', 2, 2000);
insert into developers(id, name, specialty, experience, salary) 
values (5, 'Asya Suleimanove', 'UI/UX', 2, 1800);
insert into developers(id, name, specialty, experience, salary) 
values (7, 'Ivan Ivanov', 'C#', 1, 900);
insert into developers(id, name, specialty, experience, salary) 
values (8, 'Ludmila Geiko', 'UI/UX', 2, 1800);

select * from developers

Удаление всех С++ разработчиков

begin Transaction;
DELETE FROM developers 
WHERE SPECIALTY = 'C++';
select * from developers;
COMMIT;

Выпознение команды ROLLBACK

ROLLBACK;
select * from developers

SAVEPOINT

begin Transaction;
SAVE TRANSACTION SP1;
select * from developers;
 DELETE FROM developers WHERE ID = 7;
 DELETE FROM developers WHERE ID = 6;
 DELETE FROM developers WHERE ID = 5;
 select * from developers;

Возращение к точке сохранения SP1

 ROLLBACK TRANSACTION SP1;
 select * from developers;

Изменение зарплаты где id=7

Begin Transaction 
Update developers set salary = 1200 where id = 7
select * from developers

Залание по видео

SQL

Транзакция с использованием процедуры

create procedure spUpdatedevelopers
as 
begin
begin try
begin Transaction 
update developers set specialty = 'Python'
where  id = 1
commit Transaction 
print 'Transaction Commited'
end try
begin catch 
rollback Transaction 
print 'Transaction Rolled Back'
end catch 
end

select * from developers

Изменение процедуры

alter procedure spUpdatedevelopers
as 
begin
begin try
begin Transaction 
update developers set specialty = 'Python-Java'
where  id = 1
commit Transaction 
print 'Transaction Commited'
end try
begin catch 
rollback Transaction 
print 'Transaction Rolled Back'
end catch 
end

select * from developers

XAMPP

CREATE PROCEDURE spUpdateDeveloper() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'Transaction Rolled Back' AS message; END; START TRANSACTION; UPDATE developers SET specialty = 'Python' WHERE id = 1; COMMIT; SELECT 'Transaction Committed' AS message; END;; 
call spUpdateDeveloper();