Triggers:

Trigger is a special kind of stored procedure that will be executed based on user events (Insert/Delete/Update operations)

Differences between Triggers and Stored procedures

Triggers	Stored Procedures
1) It will be executed automatically	1) SP to be called explicitly
2) Triggers doesn’t take any parameters	2) SP can take input/output parameters

Differences between Triggers and Constraint’s

Constraints	Triggers
1) Once we define some constraint in a table they will be stored along with table definition	1) It will be stored as separate object
2) When we are performing operations with in a table ,if constraints are violated a system defined message will be displayed	2) Triggers will be used to display user friendly message

Advantages of triggers:

Triggers will be used to display user friendly messages instead of displaying system defined messages

We have two types of triggers:           1) For/After triggers                2) Instead of triggers

Syntax: Create trigger <Trigger Name> on <table>

For/After [insert/Delete/Update]

or

Instead of [Insert/Delete/Update]

As

Begin

Operations

End

Differences between For After and Instead of:

For /After	Instead of
1) These triggers are executed after the constraints are checked	1) These triggers are executed before the constraint are checked
2) These triggers can be created only on tables	2) These triggers are created on both tables and views

Example for insert trigger:

create Trigger insertstudent on students

For insert

as

Begin

print 'insert trigger was fired'

End

insert students values(20,'rajesh','vizag')

Example for Update trigger:

create Trigger updatestudent1 on students

For update

as

Begin

print 'update trigger was fired'

End

Update students

Set location='chennai'

Where sid=20

Example for delete trigger:

create Trigger deletestudent on students

For delete

as

Begin

print 'delete trigger was fired'

End

delete students

Dropping trigger:

Sy: Drop Trigger <trigger name

Ex: Drop Trigger deletesudent

Creating view

create view student_view

as

select sid

from students

Creating instead trigger on a view

create Trigger insertstudent2 on student_view

Instead of Insert

as

Begin

print 'insert trigger was fired'

End

Test the instead of trigger

Insert into students values(50,'pranav','bombay')

Try to create For trigger one View

create Trigger insertstudent3 on student_view

For Insert

as

Begin

print 'insert trigger was fired'

End

Indexes

• Indexes in databases are similar to indexes in books.
• In a database, an index allows the database program to find data in a table without scanning the entire table.
• An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value.

There are two types of indexes:

1)      Clustered index

2)      Non clustered index

Clustered index will be when your table is having primary key on that column clustered index will be creating automatically. You can have only one clustered index on a table.

But u can have max 249 Non-clustered indexes on a table

Consider the following query on the Products table of. This query retrieves products in a specific price range.

SELECT ProductID, ProductName, UnitPrice

FROM Products WHERE (UnitPrice > 12.5) AND (UnitPrice < 14)

·         There is currently no index on the Product table to help this query, so the database engine performs a scan and examines each record to see if Unit Price falls between 12.5 and 14. In the diagram below, the database search touches a total of 77 records to find just three matches.

Here we are creating index on UnitPrice column

create table products(productID int PRIMARY KEY,productName varchar(20),unitPrice float)

CREATE INDEX [IDX_UnitPrice] ON Products(unitPrice)

            (unitPrice)

Ø  Much like the index in the back of a book helps us to find keywords quickly,

Ø  So the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of UnitPrice values stored in the index.

We have avoided a table scan to fetch the query results. Given this sketch of how indexes work, let’s examine some of the scenarios where indexes offer a benefit