Rules and Defaults

CREATING DEFAULT: Default is one of the database objects used to declare default values globally.

Syntax:

CREATE DEFAULT DEFAULTNAME AS NUMERICVALUES/STRING VALUE

Example: CREATE DEFAULT D1 AS 'UNKNOWN'

Binding the default for columns:

Syntax:

SP_BINDDEFAULT DEFAULTNAME,'TABLENAME.COLUMN'

Example:

SP_BINDDEFAULT D1,'DEPT.LOC'

SP_BINDDEFAULT D1,'EMP.ENAME'

INSERT INTO DEPT (DEPTNO,DNAME) VALUES (10,'ACCOUNTING')

SELECT * FROM DEPT WHERE DEPTNO=10

DEPTNO = 10

DNAME = ACCOUNTING

LOC = UNKNOWN

INSERT INTO EMP(EMPNO,SAL,DEPTNO)VALUES(100,5000,20)

SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE EMPNO=100

EMPNO = 100

ENAME = UNKNOWN

DEPTNO = 20

UNBINDING THE DEFAULT FROM THE BINDING COLUMN

Syntax:

SP_UNBINDEFAULT 'TABLEENAME.COLUMN'

SP_UNBINDEFAULT 'DEPT.LOC'

SP_UNBINDEFAULT 'EMP.ENAME'

DROPPING THE DEFAULT:

Syntax:

DROP DEFAULT DEFAULTNAME

DROP DEFAULT D1

CREATING RULES: Rule is just check constraint but it is placed on column globally.

Syntax:

CREATE RULE RULENAME AS EXPRESSION

Example:

CREATE RULE R1 AS @ X>=10 AND @ X<=60

BINDING THE RULE:

SP_BINDEFAULT RULENAME,'TABLE.COLUMN'

SP_BINDEFAULT R1,'DEPT.DEPTNO'

INSERT INTO DEPT (DEPTNO) VALUES(70)

ERROR


UNBINDING THE RULES:

SP_UNBIND RULE 'DEPT.DEPTNO'

DROPPING THE RULES:

DROP RULE RULENAME

DROP RULE R1

Rules and Defaults

Transactions Control Language

Transaction is nothing but a unit of work. We can control these transactions using the following statements.

1. ROLLBACK Statement

2. COMMIT Statement

3. SAVE TRAN [SACTIONS]

ROLLBACK Statement: This statement is used to cancel a particular performed transaction. To perform this statement in Sql Server we have to follow any one of the below 2 approaches.

Approach 1: SET IMPLICIT_TRANSACTIONS ON: This approach is only to cancel a single recently performed operation.

Example:

SET IMPLICIT_TRANSACTIONS ON
SELECT * FROM EMP
DELETE * FROM EMP
SELECT * FROM EMP
ROLLBACK
SELECT * FROM EMP

Approach 2: Explicit Transactions: To approach is to cancel recently performed multiple operations.

Syntax:

BEGIN TRAN
----------
GO
---------
GO
--------

ROLLBACK TRAN

GO is query separator

Example:

BEGIN TRAN
INSERT INTO DEPT VALUES (50,'TRA','AUS')
GO
UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11
GO
DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

ROLLBACK TRAN

The ROLLBACK TRAN statement cancels INSERT on Dept, UPDATE on EMP and DELETE on student tables.

COMMIT Statement: This statement makes a transaction permanent. It is not possible to rollback the committed transaction.

Example:

SELECT * FROM EMP
DELETE * FROM EMP
SELECT * FROM EMP
COMMIT
SELECT * FROM EMP

The COMMIT statement deletes the data from EMP permanently. It is not possible to ROLLBACK the delete operation.

Example:

BEGIN TRAN
INSERT INTO DEPT VALUES (50,'TRA','AUS')
GO
UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11
GO
DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

COMMIT TRAN

The above COMMIT TRAN makes all three transactions permanent. We cannot ROLLBACK the transactions.

SAVE TRANS Statement: This statement is used to COMMIT/ROLLBACK a particular performed transaction from the set of transactions. It is associated with alphabets in order to save the transaction.

BEGIN TRAN
SAVE TRAN A
INSERT INTO DEPT VALUES (50,'TRA','AUS')
SAVE TRAN B
UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11
SAVE TRAN C
DELETE FROM STUDENT WHERE SNO=101

ROLLBACK TRAN C (The delete operation will be cancelled)

COMMIT TRAN B (The update operation performed permanently we cannot rollback)

Transactions Control Language

Data Control Language

It is the 3rd sub language available in T-SQL.It is used to control the data between different user accounts. It includes the following statements.

1. GRANT Statement

2. REVOKE Statement

1. Grant Statement: This statement is used to grant the permissions (INSERT,SELECT,UPDATE,DELETE) on a specific table to different user accounts.

Syntax:

GRANT {ALL/SPECIFIC PERMISSIONS} ON TABLENAME TO USER ACCOUNT (S)[WITH GRANT OPTION]

WITH GRANT OPTION: When any user got the permission on a specific table from other user with this option, then that user can grant the permission on that same table to another user account. At that time sub user acts as owner.

Example:

GRANT ALL ON EMP TO BABBU WITH GRANT OPTION

From the above statement BABBU user account got all permission on EMP table from SA user account.Mean time BABBU can give the permission on EMP to another user account because he got the permission WITH GRANT OPTION.

Example: GRANT INSERT,SELECT ON EMP TO JOHNSON

Now JOHNSON can perform select and insert operations on EMP table. But JOHNSON cannot perform update and delete operations on EMP table because he does not have the corresponding permissions.

REVOKE Statement: This statement is used to revoke the permission (INSERT,SELECT,UPDATE,DELETE) on a specific table from different user accounts.

Syntax:

REVOKE {ALL/SPECIFIC PERMISSIONS} ON TABLENAME FROM USER ACCOUNT (S) [CASCADE]

CASCADE: Using this option we can destroy the communication link between user account more over from the main user it self we can revoke the permission from all sub users.

Example: REVOKE ALL ON EMP FROM JOHNSON CASCADE

The above statement revokes the permissions on EMP table from BABBU and JOHNSON. Now BABBU and JOHNSON users cannot access EMP Table.

Data Control Language