SQL Transaction Management

Transaction Control

• Successful statements are committed, while failed statements are rolled back
• Implicit Transaction Mode: each DML statement is automatically committed or rolled back
• Explicit Transaction Mode: allows defining the beginning and end points of a transaction

Transaction Control Commands

• BEGIN TRANSACTION: indicates the start of a transaction
• COMMIT: saves changes permanently to the database
• ROLLBACK: cancels modifications and reverts to the previous state
• SAVEPOINT: creates a point in a transaction that can be rolled back to
• RELEASE SAVEPOINT: removes a savepoint
• SET TRANSACTION: gives a transaction a name and defines its properties

Auto Rollback Transaction

• If an error occurs during a transaction, no modifications are made to the database
• Remaining statements in the transaction are not executed

Triggers

• Special type of stored procedure that runs automatically in response to an event
• Types of triggers:
  • DDL triggers: respond to DDL events (CREATE, ALTER, DROP)
  • DML triggers: respond to DML events (INSERT, UPDATE, DELETE)
  • Logon triggers: respond to logon events

Advantages of Triggers

• Protect data integrity
• Enforce referential integrity
• Keep tables in sync
• Useful for event logging and auditing

Stored Procedures

• Combination of SQL statements that perform a specific task
• Advantages:
  • Code reusability
  • Faster execution
  • Reduces network traffic
  • Improves data security
  • Easy to maintain

Stored Procedure Syntax

• CREATE PROCEDURE procedure_name AS BEGIN sql_statement END
• Parameters are added as comma-separated arguments after the procedure name

Functions

• Database objects that contain a set of SQL statements to perform a specific task
• Functions always return a single value or a table
• Rules for creating functions:
  • Must have a name that doesn't start with a special character
  • Can only use SELECT statements
  • Must return a value
  • Can only use input parameters
  • Cannot use TRY and CATCH statements

Types of Functions

• System Functions: built-in functions supported by the server (e.g. AVG, COUNT, SUM, MIN, DATE)
• User-Defined Functions: created by the user to perform a specific task