About us |  Help us improve
intelliVERB - Get a fair search of the Internet !
Visual studio .net
SQL Server
Oracle
Go back
 
 
 
 
 

DCL: Data Control Language

In Sql server DCL are mostly transaction related.
DCL are used to configure and control database objects.

COMMIT : make durable all changes done to the data.

ROLLBACK : restore data to its state prior to user changes

SAVEPOINT : set a point to which a ROLLBACK is possible.

SAVE TRANSACTION : Sets a savepoint within a transaction.


SqlCOMMITTop

Syntax:
COMMIT [TRANSACTION ] [TransactionName]:
Sql server accept TRAN here as option for TRANSACTION. TransactionName is the name of the user defined transaction at the BEGIN TRANSACTION statement. This command makes all changes made into the transaction section permanent.

COMMIT [WORK ]: does the same thing as COMMIT TRAN, without the option of specifying the transaction name. Sql Server accept COMMIT without the option WORK, it is compatible with SQL-92. 


SqlROLLBACKTop

Syntax:
ROLLBACK [TRANSACTION ] [TransactionName]:
Sql server accept TRAN here as option for TRANSACTION. TransactionName is the name of the user defined transaction at the BEGIN TRANSACTION statement. This command makes discards all changes made into the transaction section.

ROLLBACK [WORK ]: does the same thing as ROLLBACK TRAN, without the option of specifying the transaction name. Sql Server accept ROLLBACK without the option WORK, it is compatible with SQL-92.


SqlSAVE TRANSACTIONTop

Syntax:
SAVE [TRANSACTION ][SavepointID]:
Sql server accept TRAN here as option for TRANSACTION. SavepointID here is the user defined Savepoint it must be at most 32 characters: This command set a user define Savepoint within a transaction.

Ex:
BEGIN TRANSACTION CreateEmployee

INSERT INTO EMPLOYEE  (Empl_ID,  FirstName,  LastName, Department) VALUES (‘HR12054’,’Jeremie‘, ‘Yates’, ‘HR’)

SAVE TRANSACTION Benefits

INSERT INTO EMPL_Salaries (Empl_ID, Amount , Vacation ) VALUES (‘HR12054’,1750,3)
INSERT INTO EMPL_Parking (Empl_ID, Days , Amount ) VALUES (‘HR12054’,5,600)
INSERT INTO EMPL_Lunch (Empl_ID, Amount ) VALUES (‘HR12054’,450)

IF @@ERROR <> 0
BEGIN
 –Log error message
 ROLLBACK TRANSACTION Benefits
END
ELSE
BEGIN
 –Log success message
 COMMIT TRANSACTION
END

--END CreateEmployee

 
 
Make a secure donation now with PayPal!