Ülesanne SQL transaktsioonid - Martin Kemppi õpimapp / portfoolio / RUS

create table developers(
id int not null primary key,
NAME varchar(100),
SPECIALITY varchar(50),
EXPERIANCE smallint,
SALARY int);

SET autocommit=0;

start TRANSACTION;
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (1, 'Eugene Suleimanov', 'JAVA', 2, 2500);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (2, 'Peter Romanenko', 'JAVA', 3, 3500);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (3, 'Andrei Komarov', 'C++', 3, 2500);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (4, 'Konstantin Geiko', 'C#', 2, 2000);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (5, 'Asya Suleimanova', 'UI/UX', 2, 1800);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (7, 'Ivan Ivanov', 'C#', 1, 900);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (8, 'Ludmila Geiko ', 'UI/UX', 2, 1800);
commit;

loome tabelit ja sisestame andmed tabelise developers

start TRANSACTION;
select * from developers;
DELETE FROM developers WHERE SPECIALITY = 'C++'; 
select * from developers;
rollback;
select * from developers;

Ülesanne SQL transaktsioonid osa 3

Create Table tblMailingAddress
(
   AddressId int NOT NULL primary key,
   EmployeeNumber int,
   HouseNumber nvarchar(50),
   StreetAddress nvarchar(50),
   City nvarchar(10),
   PostalCode nvarchar(50)
)

Insert into tblMailingAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')

sisestame andmed tabelise tblMailingAddress

Create Table tblPhysicalAddress
(
 AddressId int NOT NULL primary key,
 EmployeeNumber int,
 HouseNumber nvarchar(50),
 StreetAddress nvarchar(50),
 City nvarchar(10),
 PostalCode nvarchar(50)
)

Insert into tblPhysicalAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')

lisame andmed tabelise tblPhysicalAddress

create table tblProducts(
ProductId int primary key,
Name varchar(50),
UnitPrice int,
QtyAvailable int);

insert into tblProducts(ProductId, Name, UnitPrice, QtyAvailable)
values(1, 'Laptops', 2340, 200);
insert into tblProducts(ProductId, Name, UnitPrice, QtyAvailable)
values(2, 'Desktops', 3467, 20);

update tblProducts set QtyAvailable = 250 where ProductId = 1

begin transaction
update tblProducts set QtyAvailable = 300 where ProductId = 1

1. update mitte transaktsioonita ei saa tagastada selle numbri mis oli ilma transaktsioonita
2. teeme transaktsiooni ja muudame QtyAvailable = 300 ja siis tagastame rollback -> 250
3. QtyAvailable ei saa olla 200 kuna ta pole tehtud transaktsioonis ja meil pole sellest infot ehk savepoint või transaktsiooni

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

rollback transaction

begin transaction
update tblProducts set QtyAvailable = 300 where ProductId = 1
commit transaction

create procedure spUpdateAddress
as
begin
	begin try
		begin transaction
			update tblMailingAddress set City = 'LONDON'
			where AddressId = 1 and EmployeeNumber = 101

			Update tblPhysicalAddress set City = 'LONDON'
			where AddressId = 1 and EmployeeNumber = 101
		Commit transaction
		Print 'Transaction Commited'
	end try
	begin catch
		rollback transaction
	end catch
end

select * from tblMailingAddress;
select * from tblPhysicalAddress;

alguses meil oli tabelid tblMailingAddress ja tblMailingAddress kus city oli Londoon ja protseduuriga me oleme teinud transaktsiooni kus me muudame nime Londoon -> LONDON -ks ja kui see õnnestus teha (protseduuri) siis ta näitab, et Transaction Commited ehk transaktsioon on tehtud ja salvestatud

alter procedure spUpdateAddress
as
begin
	begin try
		begin transaction
			update tblMailingAddress set City = 'LONDON1'
			where AddressId = 1 and EmployeeNumber = 101

			Update tblPhysicalAddress set City = 'LONDON LONDON'
			where AddressId = 1 and EmployeeNumber = 101
		Commit transaction
		Print 'Transaction Commited'
	end try
	begin catch
		rollback transaction
		print 'transaction rolled back'
	end catch
end