16  Semana 6 (02-Nov-2026)

16.1 Transaction Control Language (TCL)

  • Usando a base de dados BikeStores

16.1.1 BEGIN/COMMIT TRANSACTION

BEGIN TRANSACTION
SET IDENTITY_INSERT production.categories ON
INSERT INTO production.categories(category_id,category_name) VALUES (90, 'Super-Hybrid Bikes (done with a commit transaction)')
SET IDENTITY_INSERT production.categories OFF
COMMIT TRANSACTION
8 records
category_id category_name
1 CHILDREN BICYCLES
2 COMFORT BICYCLES
3 CRUISERS BICYCLES
4 CYCLOCROSS BICYCLES
5 ELECTRIC BIKES
6 MOUNTAIN BIKES
7 ROAD BIKES
90 Super-Hybrid Bikes (done with a commit transaction)

16.1.2 ROLLBACK

BEGIN TRANSACTION 
SET IDENTITY_INSERT production.categories ON
INSERT INTO production.categories(category_id,category_name) VALUES (99, 'Hiper-Hybrid Bikes (done with a rollback transaction)')
SET IDENTITY_INSERT production.categories OFF
ROLLBACK TRANSACTION
8 records
category_id category_name
1 CHILDREN BICYCLES
2 COMFORT BICYCLES
3 CRUISERS BICYCLES
4 CYCLOCROSS BICYCLES
5 ELECTRIC BIKES
6 MOUNTAIN BIKES
7 ROAD BIKES
90 Super-Hybrid Bikes (done with a commit transaction)

16.1.3 Exercícios/Soluções

  • Usando a base de dados BikeStores

16.1.3.1 Exercício 1: Inserir registos com transacções

  • Utilize uma transacção para inserir 2 novos registos na tabela production.categories e termine com COMMIT.
  • Utilize uma transacção para inserir 2 novos registos na tabela production.brands e termine com ROLLBACK.
  • Faça um SELECT às duas tabelas e veja os resultados.

16.1.3.2 Exercício 2: Apagar registos com transacções

  • Utilize uma transacção para apagar os 2 novos registos inseridos na tabela production.categories e termine com ROLLBACK.
  • Faça um SELECT à tabela e veja os resultados.

16.1.3.3 Exercício 3: Controlo Transaccional

  • Utilize uma transacção para inserir 2 novos registos na tabela production.categories e também 2 novos registos na tabela production.brands.
  • Os registos na tabela production.brands devem ter o mesmo identificador.
  • Utilize TRY … CATCH para detectar o resultado da execução das inserções, e faça COMMIT se não houver erros e ROLLBACK se algo falhar.
  • Faça um SELECT às duas tabelas e veja os resultados. Interprete o que obteve.
BEGIN TRANSACTION
SET IDENTITY_INSERT production.categories ON
INSERT INTO production.categories(category_id,category_name) VALUES (88, 'Hybrid Bikes')
INSERT INTO production.categories(category_id,category_name) VALUES (99, 'Hybrid Bikes Turbo')
SET IDENTITY_INSERT production.categories OFF 
COMMIT TRANSACTION
BEGIN TRANSACTION
SET IDENTITY_INSERT production.brands ON 
INSERT INTO production.brands(brand_id,brand_name) VALUES (10, 'Hybrid Brand')
INSERT INTO production.brands(brand_id,brand_name) VALUES (11, 'Hybrid Brand Turbo')
SET IDENTITY_INSERT production.brands OFF;
ROLLBACK TRANSACTION
5 records
category_id category_name
6 MOUNTAIN BIKES
7 ROAD BIKES
88 Hybrid Bikes
90 Super-Hybrid Bikes (done with a commit transaction)
99 Hybrid Bikes Turbo
4 records
brand_id brand_name
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
BEGIN TRANSACTION
DELETE FROM production.categories WHERE category_id IN (88,99)
ROLLBACK TRANSACTION
5 records
category_id category_name
6 MOUNTAIN BIKES
7 ROAD BIKES
88 Hybrid Bikes
90 Super-Hybrid Bikes (done with a commit transaction)
99 Hybrid Bikes Turbo
BEGIN TRY
  BEGIN TRANSACTION
    SET IDENTITY_INSERT production.categories ON
    INSERT INTO production.categories(category_id,category_name) VALUES (20, 'Super-Hybrid Bikes')
    INSERT INTO production.categories(category_id,category_name) VALUES (30, 'Super-Hybrid Bikes Turbo')
    SET IDENTITY_INSERT production.categories OFF 
    SET IDENTITY_INSERT production.brands ON 
    INSERT INTO production.brands(brand_id,brand_name) VALUES (20, 'Super-Hybrid Brand')
    INSERT INTO production.brands(brand_id,brand_name) VALUES (20, 'Super-Hybrid Brand Turbo')
    SET IDENTITY_INSERT production.brands OFF
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH 
Displaying records 1 - 10
category_id category_name
1 CHILDREN BICYCLES
2 COMFORT BICYCLES
3 CRUISERS BICYCLES
4 CYCLOCROSS BICYCLES
5 ELECTRIC BIKES
6 MOUNTAIN BIKES
7 ROAD BIKES
88 Hybrid Bikes
90 Super-Hybrid Bikes (done with a commit transaction)
99 Hybrid Bikes Turbo
9 records
brand_id brand_name
1 Electra
2 Haro
3 Heller
4 Pure Cycles
5 Ritchey
6 Strider
7 Sun Bicycles
8 Surly
9 Trek