Wednesday, November 28, 2012

TRIGGER in SQL Server (T-SQL)

A trigger is a special kind of a store procedure that fire implicitly when certain event occurs in database server. We can’t invoke triggers explicitly. It can be used to perform administrative tasks in database such as regulating database operation.

Types of TRIGGER:
1. DDL TRIGGER:

DDL triggers execute in response to a variety of Data Definition Language (DDL) events such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS statements and certain system stored procedures that perform DDL like action. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.
  • DDL triggers do not support INSTEAD OF feature.
  • DDL triggers do not have feature of inserted and deleted tables like DML triggers.
Example :
CREATE TRIGGER PreventDropTable
ON DATABASE            
FOR DROP_TABLE  --DROP_TABLE is DDL Event See link DDL events 
          
AS
PRINT 'Drop table action not allowed.'
ROLLBACK;
GO

Droping table to test trigger action
DROP TABLE [EMPLOYEE]It will not drop table due to triggering action

We can disable or enable trigger
--Disabling database trigger
DISABLE TRIGGER PreventDropTable ON DATABASE;


--Enabling database trigger
ENABLE TRIGGER PreventDropTable ON DATABASE;

2. DML TRIGGER:
DML trigger is performed on DML operation (INSERT, UPDATE, DELETE) to enforce rule and regulation on table. Trigger can prevent incorrect, unauthorized, inconsistent changes to data. 

A). AFTER TRIGGER (FOR TRIGGER)
After triggers run after INSERT, UPDATE or DELETE operation on table. They do not supported for views. 
AFTER TRIGGERS can be classified further into three types as: 

     i). AFTER INSERT Trigger
This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

--Creating a sample table
CREATE TABLE empProfile
(id INT IDENTITY(1,1),
name VARCHAR(50),
gender VARCHAR(6),
create_date DATETIME,
update_date DATETIME
)



--Creating trigger to automatic update log
CREATE TRIGGER setCreateDate ON [dbo].[empProfile]
FOR  INSERT
AS
      --AUTOMATIC populate create_date
      UPDATE empProfile SET create_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)
      PRINT 'AFTER INSERT trigger fired.'
GO

Inserting value in database to test trigger action
INSERT INTO empProfile (name,gender)
SELECT 'MDF', 'MALE'
UNION ALL
SELECT 'ZTE', 'FEMALE'
UNION ALL
SELECT 'John', 'MALE'
UNION ALL
SELECT 'Abc', 'MALE'

SELECT * FROM empProfile

     ii). AFTER UPDATE Trigger
This trigger is fired after an update on the table.
Example:
--Creating trigger to automatic update log
CREATE TRIGGER setUpdateDate ON [dbo].[empProfile]
FOR  UPDATE
AS
--AUTOMATIC populate create_date
UPDATE empProfile SET update_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)
      PRINT 'AFTER Update trigger fired.'
GO
UPDATE empProfile SET name='XYZ' WHERE id=1
UPDATE empProfile SET name='MDF' WHERE id=3

     iii). AFTER DELETE Trigger
This trigger is fired after a delete on the table. 
Example:
CREATE TRIGGER onDeleteTest ON [dbo].[empProfile]
AFTER  DELETE
AS
      PRINT 'AFTER DELETE trigger fired.'
GO

Testing trigger action
DELETE FROM empProfile WHERE ID=2

A). Instead Of Triggers
Instead of trigger override default action of DML operation on table or view. After defining INSTEAD OF trigger if we want to delete a row then Delete operation trying to delete but actually row will not get deleted unless you issue another delete instruction with in trigger.

Advantage of INSTEAD OF trigger:
  • It can enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.
  • It can enable you to code logic that can reject DML operation according to condition.
  • It enable to ignoring parts of a batch.

Note: INSTEAD OF DELETE and INSTEAD OF UPDATE trigger cannot be defined on table if a table has foreign key defined by using DELETE or UPDATE cascading action then INSTEAD OF DELETE and INSTEAD OF UPDATE cannot be defined.

INSTEAD OF trigger can be classified further into three types: 

     i). INSTEAD OF INSERT Trigger. 
Example :
CREATE TRIGGER onInsertTest ON [dbo].[empProfile]
INSTEAD OF  INSERT
AS
IF ((SELECT gender FROM inserted)='FEMALE')
RAISERROR('Can not INSERT FEMALE employee',16,1);
ELSE
INSERT INTO empProfile (name, gender)
SELECT name, gender FROM inserted
GO

Testing trigger action
INSERT INTO empProfile (name, gender)
SELECT 'DEVA', 'FEMALE'

     ii). INSTEAD OF UPDATE Trigger. 
Example :
CREATE TRIGGER onUpdateTest ON [dbo].[empProfile]
INSTEAD OF  UPDATE
AS
IF ((SELECT gender FROM inserted)='FEMALE')
RAISERROR('Can not update FEMALE employee',16,1);
ELSE
UPDATE empProfile SET name = (SELECT name FROM inserted) WHERE ID =(SELECT id FROM inserted)
GO

Testing trigger action
UPDATE empProfile set NAME='INDRA' WHERE ID = 2

     iii). INSTEAD OF DELETE Trigger. 
Example :
CREATE TRIGGER onDeleteTest ON [dbo].[empProfile]
INSTEAD OF  DELETE
AS
IF EXISTS( SELECT 1 FROM deleted WHERE gender ='FEMALE')
RAISERROR('Can not delete FEMALE employee',16,1);
ELSE
DELETE FROM empProfile WHERE ID IN (SELECT D.ID FROM deleted D)
GO

Testing trigger action
DELETE FROM empProfile WHERE ID IN (3,4)

3. LOGON TRIGGER
 Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. 
Example :

CREATE TRIGGER logging_logon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logHistoryTbl (sys_user, loginTime)
SELECT SYSTEM_USER,GETDATE()
END
GO


Tuesday, September 4, 2012

Transaction in SQL Server


Transaction:
 A transaction is a logical, atomic unit of work that contains one or more SQL statements.
Transaction groups SQL statements so that they are either all committed (which means they are applied to the database), or all rolled back (which means they are undone from the database).

Property of Transaction:
ACID: (atomicity, consistency, isolation, durability)
In computer science, ACID is a set of properties that guarantee that database transactions are processed reliably

Atomicity: Modification on the data in the database either fails or succeeds. The beginning of such a modification starts with a transaction and ends when a transaction finishes (either by a commit or a rollback).

Consistency: The transaction takes the database from one consistent state to another consistent state.

Isolation: The effect of a transaction is not visible to other transactions until the transaction is committed. 

Durability: Changes made by committed transactions are permanent.


Type of Transaction:
1.    Auto commit mode : On any DML operation, commit fires implicitly.(e.g. SQL Server)
2.    Implicit mode: commit is must before logout. (e.g. Oracle 9i & 10g)
3.    Explicit Mode: User will apply commit as per his requirement.


Example 1:

BEGIN TRANSACTION;

BEGIN TRY
    DELETE FROM TB1                   --DELETED ALL ROW
    SELECT * FROM TB1                 --NO ROW HERE
    DELETE FROM TB1 WHERE ID = 'ABC'; --HERE IS ERROR ('ABC' is not integer value)
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; --ROLL BACK HERE
        SELECT * FROM TB1    --IT WILL GIVE ALL ROW
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Friday, August 24, 2012

Cursor in SQL Server (with example)


What is a Cursor?
Cursor is a variable in SQL Server Database which is used for row-by row operations. It indicates the current position in the result set so that it is known as Cursor.

Script for taking backup of all databases by using cursor.

DECLARE @dbName VARCHAR(50) --DATABASE NAME
DECLARE @path VARCHAR(500)  --BACKUP PATH
DECLARE @fileName VARCHAR(100) --FILE NAME FOR BACKUP
DECLARE @fileDate VARCHAR(100) --FILE DATE FOR NAME WITH FILE NAME

SET @path = 'C:\Backup\'       --BACKUP PATH
SELECT @fileDate = CONVERT(VARCHAR, GETDATE(),105)

DECLARE dbName_cursor CURSOR FOR   --DECLARATION AND INITILIZING CURSOR
SELECT [name] FROM [master].[dbo].[sysdatabases]
WHERE [name] NOT IN ('master','model','msdb','tempdb')

OPEN dbName_cursor        --OPEN CURSOR
FETCH NEXT FROM dbName_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fileName = @path+@dbName+'_'+@fileDate+'.bak'
    BACKUP DATABASE @dbName TO DISK = @fileName
   
    FETCH NEXT FROM dbName_cursor INTO @dbName
END
CLOSE dbName_cursor      --CLOSING CURSOR
DEALLOCATE dbName_cursor --DEALLOCATION MEMORY

Friday, August 17, 2012

JOIN in SQL SERVER

JOIN

Join enable to retrieve data from two or more tables based on logical relationships between the tables.

--TABLE JOIN CONCEPT--
INNER JOIN
SELECT T1.*, T2.*
FROM TB1 T1 INNER JOIN TB2 T2 ON T1.ID=T2.ID

LEFT OUTER JOIN
SELECT T1.*, T2.*
FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON T1.ID=T2.ID


RIGHT OUTER JOIN
SELECT T1.*, T2.*
FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON T1.ID=T2.ID


FULL OUTER JOIN
SELECT T1.*, T2.*
FROM TB1 T1 FULL OUTER JOIN TB2 T2 ON T1.ID=T2.ID


CROSS JOIN
<no picture>
SELECT T1.*, T2.*
FROM TB1 T1 CROSS JOIN TB2 T2
--(both are equivalent)--
SELECT T1.*, T2.*
FROM TB1 T1 INNER JOIN TB2 T2 ON 1=1


LEFT OUTER JOIN - WHERE IS NULL
SELECT T1.*, T2.* FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON T1.ID=T2.ID
WHERE T2.ID IS NULL


RIGHT OUTER JOIN - WHERE IS NULL
SELECT T1.*, T2.* FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON T1.ID=T2.ID
WHERE T1.ID IS NULL


FULL OUTER JOIN - WHERE IS NULL
SELECT T1.*, T2.* FROM TB1 T1 FULL OUTER JOIN TB2 T2 ON T1.ID=T2.ID
WHERE T1.ID IS NULL OR T2.ID IS NULL 




SELF JOIN - (A REAL EXAMPLE)
SELECT T1.id, T1.name, T2.name AS manager_name
FROM employee T1 left outer JOIN employee t2
ON T1.managerId=T2.id


Thursday, July 26, 2012

Common Table Expression (CTE) is MS SQL Server


Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and can be referenced multiple times in the same query unlike a derived table.
In Common Table Expression we can create a recursive query. Reference the resulting table multiple times in the same statement. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.



EXAMPLE : 1
UPDATE or SELECT single row from a table which has many realted duplicates row
WITH CTE (config_element, rowNum)
AS
(
SELECT config_element,
ROW_NUMBER() OVER(PARTITION BY config_element ORDER BY config_element) AS rowNum
FROM [myDb].[dbo].[temp_configuration]
)
--UPDATE CTE SET config_element='factoryShortKeys' WHERE rowNum=2
SELECT * from CTE WHERE rowNum = 2
GO

EXAMPLE : 2
Using multiple CTE in single select query
WITH CTE_test1 AS (SELECT 100 AS Col1),
CTE_test2 AS (SELECT COL1+1 AS Col2 FROM CTE_test1)
SELECT CTE_test1.Col1, CTE_test2.Col2 FROM CTE_test1 
CROSS JOIN CTE_test2
GO

EXAMPLE : 3
Using multiple CTE in single select query
WITH CTE_test1  AS (SELECT 100 AS Col1),
CTE_test2 AS (SELECT COL1+1 AS Col2 FROM CTE_test1)
SELECT CTE_test1.Col1, CTE_test2.Col2 FROM CTE_test1 
CROSS JOIN CTE_test2
GO


For further detail see link: