Trigger in Sql

A trigger is a special kind of stored procedure, that is executed automatically when a specified event fired on database server.

There are three types of triggers DML trigger, DDL trigger and Logon trigger.
1. DML trigger - A DML trigger can be created on a table or view, its executes when a user tries to update data through DML operations (insert / update / delete), trigger fires regardless of whether or not data is updated.
DML triggers have two types of nature 'After' and 'Instead of'
After - Trigger with after fires after execution of specified event for that triger is created. We use keyword FOR or AFTER for it, both are same.
Instead Of - Trigger with instead of option fires instead of executing specified event. Trigger of this nature overrides specified event or triggering statement with statements defined in body of trigger.
Instead of option can't be used with DDL or Logon triggers.
Syntax - 

CREATE TRIGGER trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement }

Ex. This trigger enforces a business rule when user try to insert an employee record in employee table, that protect the table from inserting bonus out of the range that company has specified.

create trigger trgEmpSalary
on tbl_employee
for Insert
AS
begin
if exists (select * from tbl_employee where bonus between 1000 and 10000)
begin
raiserror('Bonus range in company is 1000 to 10000',10,1)
rollback transaction
end
end

LLD (Low level design document)

At start of LLD (Low level design document) mention customer name, project name, list of authors and name and signature of the person who approve the document. After that index and then according to below content

Revision History - Should be a table with fields version number, date of release, author, history of changes and approver.        

1.      Introduction - General description.

1.1              Brief system description - Brief description of system.                          

1.2              Glossary - Vocabulary list, describe terms used in document.

1.3              References - Mention references, given in document if any.

2.      Low Level Design

2.1              Physical Database Design - Describe database structure, with all objects, relations etc.

2.2              Database Engineering - Include data access methods, optimization techniques, query

   construction techniques.

2.3              User Interface Design - Describe UI of project.

2.4              Data Inflows & Outflows - Create end to end data flow diagram from login to last

   operations.

Stored Procedure in Sql

A stored procedure is a group of Transact-Sql statement compiled into a single execution plan. Its faster than ordinary sql statements because they will undergone in a sequence of steps to execute.
By default SPs stored in DB in compiled form, but if we need to create SP that compile each time it runs than we can add 'with recompile' at the time of creation.

Simple syntax -
create procedure procedureName
[@param1 datatype, @param2 datatype, [@param3 datatype out,]...]

[with RECOMPILE]

as
Begin
--Sql statements
End

A procedure can take multiple parameters, and can return -
1. Data like single int or string value.
2. Can return a local or global cursor.
3. Can return a result set for all select statement(and/or stored procedure) contained in SP.

Cursor in Sql

A cursor is a database object, having set of rows with a pointer that identify current row. So one can manipulate data row by row basis. Its like recordset in .net.

A cursor can be declared as forward only or scroll. It can be declared as read only.
A Static cursor has a separate copy of data in tempdb and serve all request from there so changes made to DB does not affect result of cursor, It does not allow modifications.
A Local cursor has scope in area where it is declared like in trigger or in stored procedure, while a Global cursor name have scope throughout the connection.
A Fast_forward cursor specifies forward_only, read_only cursor with performance optimization.

Ex.
declare @emp varchar(10), @sal money
declare myCursor cursor for
select empname, salary from tbl_employee

open mycursor
fetch next from mycursor into @emp, @sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
while @@FETCH_STATUS = 0
begin
fetch next from mycursor into @emp,@sal
print @emp + ' getting salary ' + cast(@sal as varchar(10))
end
close mycursor
deallocate mycursor

Using PIVOT and UNPIVOT in sql

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Ex. Table Pivot_Test has data given below

Sales Person	Product	Sales Amount
Bob	Pickles	100
Sue	Oranges	50
Bob	Pickles	25
Bob	Oranges	300
Sue	Oranges	500
Due	Mangoes	800

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM Pivot_Test ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

Output

Sales Person	Oranges	Pickles
Bob	300	125
Due	NULL	NULL
Sue	550	NULL

So how does this work?
There are three pieces that need to be understood in order to construct the query.
1. The SELECT statement
    SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
    This portion of the query selects the three columns for the final result set (SalesPerson, Oranges,      Pickles)
2. The query that pulls the raw data to be prepared
    (SELECT SalesPerson, Product, SalesAmount FROM Pivot_Test) ps
    This query pulls all the rows of data that we need to create the cross-tab results.  The (ps) after the                  query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
3. The PIVOT expression
    PIVOT (SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
    This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.