Ülesanne Transaktsioonid

SQL

1.Ülesanne

Tehtud “developers” tabel

create database developers;
use developers;

create table developers(
ID int primary key identity(1,1),
Name varchar(100),
Specialty varchar(50),
Experience int,
Salary int);
select * from developers;

insert into developers
values('Eugene Suleimanov','Java',2,2500);
insert into developers
values('Peter Romanenko','Java',3,3500);
insert into developers
values('Andrei Komarov','C++',3,2500);
insert into developers
values('Konstantin Geiko','C#',2,2000);
insert into developers
values('Asya Suleimanova','UI/UX',2,1800);
insert into developers
values('Ivan Ivanov','C#',1,900);
insert into developers
values('Ludmila Geiko','UI/UX',2,1800);
select * from developers;

Eemaldame kõik C++ arendajad ja teostame ROLLBACKi käsu:

SET IMPLICIT_TRANSACTIONS OFF
begin transaction;
DELETE FROM developers 
WHERE SPECIALTY = 'C++';
select * from developers;
rollback;
select * from developers;

Tehtud SAVE TRANSACTION

begin transaction;
save transaction SP1;

begin transaction;
DELETE FROM developers WHERE ID = 7;
DELETE FROM developers WHERE ID = 6;
DELETE FROM developers WHERE ID = 5;
select * from developers;
rollback transaction SP1;
select * from developers;

Ülesanne 2

lõi Product tabeli

create database Transactions;
use Transactions;

create table Product(
ProductID int primary key identity(1,1),
Name varchar(100),
UnitPrice int,
QtyAvailable int);

insert into Product(name,UnitPrice,QtyAvailable)
values ('Laptops',2340,100);
insert into Product(name,UnitPrice,QtyAvailable)
values ('Desktops',3467,20);

select * from Product;

begin transaction
Update Product set QtyAvailable = 200 where ProductID = 1;
rollback;

Ei näe Product tabeli sisu, kui loon uue päringu

peale seda saame lugeda komplitseerimata andmeid

select * from Product;

set transaction isolation level read uncommitted;

Kui me tahame muuta tabelit püsivaks, siis peame kirjutama “commit” peale tehingut või “Rollback”, et muudatused olematuks muuta

begin transaction
Update Product set QtyAvailable = 200 where ProductID = 1;

commit;

rollback;

Tehtud uus tabelid

create table tblPhysicalAddress(
AddressID int primary key identity(1,1),
EmployeeNumber int,
HouseNumber varchar(10),
StreetAddress varchar(50),
City varchar(50),
PostalCode varchar(50));

create table tblMailingAddress(
AddressID int primary key identity(1,1),
EmployeeNumber int,
HouseNumber varchar(10),
StreetAddress varchar(50),
City varchar(50),
PostalCode varchar(50));

insert into tblPhysicalAddress(EmployeeNumber,HouseNumber,StreetAddress,City,PostalCode)
values (101,'#10','King Street','LONDOON','CR27DW');
insert into tblMailingAddress(EmployeeNumber,HouseNumber,StreetAddress,City,PostalCode)
values (101,'#10','King Street','LONDOON','CR27DW');

Loonud Protseduur spUpdateAddress

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
	print 'Transaction Committed'
	end try
	begin catch
		rollback transaction
		print 'Transaction Rolled Back'
	end catch
end

“City” veerus olevad andmed olid valed, nii et ma lõin korra õigekirja parandamiseks Tehingu abil ja kui on viga, teeb protseduur “Rollback”

Nüüd teen vea tahtlikuks

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 LONDON LONDON LONDON LONDON LONDON LONDON LONDOL'
		where AddressID = 1 and EmployeeNumber = 101
	commit
	print 'Transaction Committed'
	end try
	begin catch
		rollback transaction
		print 'Transaction Rolled Back'
	end catch
end

Mul on linna veerg määratud varcharile (50) nii et ma pidin palju londonit kirjutama, et see vea tabaks

kõik toimingud veeretati tagasi, sest üks värskenduslause ei olnud korrektselt kirjutatud