Chapter 07 - Transactions

Transaksionet

sintaksa e pergjithshme

BEGIN TRANSACTION <emertransaksion>

...........

...........

...........

COMMIT TRANSACTION <emertransaksion>

Versione te komandes e sakteBEGIN TRAN <emertransaksion>

...........

...........

...........

COMMIT TRAN <emertransaksion>

--- e sakte

BEGIN TRAN

...........

...........

...........

COMMIT TRAN

--- e sakte

BEGIN TRANSACTION

...........

...........

...........

COMMIT TRANSACTION

Kontrolli i transaksionit

-- si e kontrollojme qe transaksioni eshte i sakte

-- perdorim

ROLLBACK TRANSACTION <emer>

ROLLBACK TRAN <emer>

ROLLBACK TRANSACTION

ROLLBACK TRAN

Si te gjejme ne ka gabim?

variabli @@ERROR

    • eshte i barabarte me 0 kur nuk ka patur gabim

    • eshte i ndryshem nga 0 kur ka ndodhur nje gabim

SHEMBULL

TE BEJME NJE TRANSAKSION QE BEN DALJE PRODUKTE

-- te bejme dalje produktet qe kane 3 njesi gjendje

-- te bejem dalje produktet qe kane 0 njesi gjendje

-- shembulli 1 pa transaksion

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=0

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=3

E para dha gabim , bie ndesh me kufizimin (UnitsInstock>0)

e dyta shkoi me sukses

-- shembull 2 me transaksion

BEGIN TRANSACTION Dalje

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=2

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=0

IF (@@ERROR<>0)

BEGIN

ROLLBACK TRANSACTION Dalje

print 'Transaksioni i daljes deshtoi'

END

ELSE

BEGIN

COMMIT TRANSACTION Dalje

print 'Transaksini shkoi me sukses'

END

-- i gjithe transaksioni eshte kthyer mbrapsht

-- shembull 3 me transaksion, gabimi ne fillim

BEGIN TRANSACTION Dalje

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=0


update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=2

IF (@@ERROR<>0)

BEGIN

ROLLBACK TRANSACTION Dalje

print 'Transaksioni i daljes deshtoi'

END

ELSE

BEGIN

COMMIT TRANSACTION Dalje

print 'Transaksini shkoi me sukses'

END


-- transaksioni shkoi me sukses

--????????????????????????????

-- @@ERROR ruan statusin e gabimit te transaksionit

-- me te fundit

-- veprimi i fundit pa gabim zeron @@error

Kontroll me i mire i transaksionit

-- kontroll me mire behet

-- me TRY CATCH

--Sintaksa

BEGIN TRY

.... RRESHTA KODI

.... RRESHTA KODI

.... RRESHTA KODI

.... RRESHTA KODI

.... RRESHTA KODI

END TRY

BEGIN CATCH

.... RESHTA KODI QE EKEZEKUTOHEN

....VETEM NESE KA GABIM

END CATCH

-- KONTROLLI I TRANSAKSIONIT

-- E VENDOSIM TRANSAKIONIN BRENDA BLLOKUR TRY

-- VENDOSIM KODIN QE TRAJTON

--GABIMET TEK BLLOKU CATCH

BEGIN TRY

BEGIN TRANSACTION Dalje

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=0

update Products

set

UnitsInStock=UnitsInStock-1

where UnitsInStock=1

COMMIT TRANSACTION Dalje

PRINT 'Transaksioni shkoi me sukses'

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION Dalje

print 'Transaksioni deshtoi me sukses'

END CATCH

USHTRIME

Ushtrim 1

Ndertoni nje transaksion i cili :

1) te gjithe punonjesit qe kane realizuar me pak se 50 porosi i ben me Title='Pushuar nga puna'

2) Fshin te gjithe porosite qe ata kane realizuar

Zgjidhje

BEGIN TRY

BEGIN TRAN

UPDATE Employees

SET Title='Pushuar nga puna'

where

EmployeeID IN

(

select EmployeeID

from Orders

group by EmployeeID

having COUNT(*)<50

)

DELETE Orders

WHERE EmployeeID IN

(

select EmployeeID

from Orders

group by EmployeeID

having COUNT(*)<50

)

COMMIT TRAN

print 'transaksioni shkoi me sukses'

END TRY

BEGIN CATCH

ROLLBACK TRAN

Print 'transaksioni deshtoi'

END CATCH

Ushtrim 2

Ndertoni nje transaksion qe shton nje porosi per produktin me kodin 5, per klientin me kodin ALFKI dhe punonjesin me kodin 1.

Sasia e porositur do te jete 10 ndersa cmimi i barabarte me cmimin e produktit

Nese nuk ka gjendje transaksioni duhet te kthehet mbrapsht

begin try

BEGIN TRANSACTION

Declare @oid INT

Declare @price money

select @price=UnitPrice from products where ProductID=5

print 'Price of product 5 is: '+convert(varchar,@price);

insert into orders(EmployeeID,CustomerID) values (1,'ALFKI');

set @oid=@@IDENTITY

print 'A new order is added with orderid: '+convert(varchar,@oid);

insert into [Order Details](OrderID,ProductID,UnitPrice,Quantity)

values (@oid,5,@price,10);

print 'An order for product 5 is entered.';

print 'Updating product stock';

update products

set UnitsInStock=UnitsInStock-10

where productId=5;

print 'Product Stock is updated';

COMMIT TRANSACTION

end try

begin catch

ROLLBACK TRANSACTION

PRINT 'Transaksioni deshtoi'

end catch