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 TRANSACTION16 Semana 6 (02-Nov-2026)
16.1 Transaction Control Language (TCL)
- Usando a base de dados BikeStores
16.1.1 BEGIN/COMMIT TRANSACTION
| 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| 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 TRANSACTIONBEGIN 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| 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 |
| 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| 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 | 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 |
| brand_id | brand_name |
|---|---|
| 1 | Electra |
| 2 | Haro |
| 3 | Heller |
| 4 | Pure Cycles |
| 5 | Ritchey |
| 6 | Strider |
| 7 | Sun Bicycles |
| 8 | Surly |
| 9 | Trek |