Committing Data with TCL


  • when you use the language of COMMIT,
    • it commits that data to the users who are actively using the database,
    • while also letting you create other queries to validate the data before it’s saved to the database

Example

  • scenario: toy company’s inventory
  • start with BEGIN TRANSACTION
    • settings of our database require this to be explicitly stated
    • opens the transaction
  • use UPDATE to correct the spelling of the Toy Soldier product name
  • COMMIT to commit the changes
--Start the transaction because the settings of the database require this
to be explicitly stated
 
BEGIN TRANSACTION
 
--This is the update that is being performed
 
UPDATE  ToyProducts
 
SET       ProductName = N'Toy Soldier'
 
WHERE    (ProductName = 'Toy Soldeir')
 
COMMIT
  • could further add a SELECT statement
    • would allow us to verify that all the ProductName updates were performed as expected
--Start the transaction because the settings of the database require this
to be explicitly stated
 
BEGIN TRANSACTION
 
--This is the update that is being performed
 
UPDATE  ToyProducts
 
SET      ProductName = N'Toy Soldier'
 
WHERE   (ProductName = 'Toy Soldeir')
 
--Test to confirm change is as expected
 
SELECT ProductName
 
FROM Products
 
WHERE ProductName = N'Toy Soldier'
 
COMMIT
  • If you ran this statement by statement, you could do even more query validation before you run COMMIT