Trigger is Sql Server 2008

Trigger is Sql Server 2008

Trigger Definition

A trigger is a special type of stored procedure that automatically runs when a language event executes. SQL Server includes two general types of triggers:

data manipulation language (DML) and data definition language (DDL) triggers. DML triggers can be used when INSERT, UPDATE, or DELETE statements modify data

in a specified table or view. DDL triggers fire stored procedures in response to a variety of DDL statements, which are primarily statements that begin with

CREATE, ALTER, and DROP. DDL triggers can be used for administrative tasks, such as auditing and regulating database operations.

Types of DML Triggers

AFTER Triggers

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.

Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server.

AFTER triggers can be specified only on tables.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables,

where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines.

CLR Triggers

A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger.

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly

created in the .NET Framework and uploaded in SQL Server.

DDL Triggers

-DDL triggers execute in response to a variety of data definition language (DDL) events.

These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

- Unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of

Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain

system stored procedures that perform DDL-like operations can also fire DDL triggers

DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

Use DDL triggers when you want to do the following:

-You want to prevent certain changes to your database schema.

-You want something to occur in the database in response to a change in your database schema.

-You want to record changes or events in the database schema.

DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.

The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK ;

DML Triggers

DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view. A DML trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DML triggers are useful in these ways:

-They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential

integrity constraints.

-They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with

CHECK constraints.

-Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted

or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

-They can evaluate the state of a table before and after a data modification and take actions based on that difference.

Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to

the same modification statement.

Types of DML Triggers

You can program the following types of DML Triggers:

AFTER Triggers

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.

Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server.

AFTER triggers can be specified only on tables.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables,

where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines.

CLR Triggers

A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger. Instead of executing a Transact-SQL stored procedure,

a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

Reflection in C#

Reflection provides objects (of type Type) that encapsulate assemblies, modules and types. You can use reflection to dynamically create an instance of a type, bind the type to an existing object, or get the type from an existing object and invoke its methods or access its fields and properties. If you are using attributes in your code, Reflection enables you to access them.

// Using GetType to obtain type information: int i = 42;
 System.Type type = i.GetType(); System.Console.WriteLine(type); 

The output is:

System.Int32

// Using Reflection to get information from an Assembly:
System.Reflection.Assembly o = System.Reflection.Assembly.Load("mscorlib.dll");
System.Console.WriteLine(o.GetName());  

The output is:

mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089




Example for Array list in C# and Binary Search,Sort,Remove,Removeat

ArrayList Class

Implements the IList interface using an array whose size is dynamically increased as required.

The capacity of a ArrayList is the number of elements the ArrayList can hold. As elements are added to an ArrayList, the capacity is automatically increased as

required through reallocation.

//Example for Array list in C# and Binary Search,Sort,Remove,Removeat

using System;

using System.Collections;

public class SamplesArrayList

{

public static void Main()

{

ArrayList myAL = new ArrayList();

//Insert values in arraylist

for (int i = 0; i <= 10; i++)

myAL.Add(i * 2);

// Displays the ArrayList.

Console.WriteLine("The Int32 ArrayList contains the following:");

PrintValues(myAL);

// Locates a specific object that does not exist in the ArrayList.

Object myObject = 4;

FindMyObject(myAL, myObject);

// Locates an object that exists in the ArrayList.

Object myObjectSearch = 11;

FindMyObject(myAL, myObjectSearch);

// Removes the element containing "4".

myAL.Remove(4);

// Removes the element at index 5.

myAL.RemoveAt(5);

// Removes three elements starting at index 4.

myAL.RemoveRange(4, 3);

// Sorts the values of the ArrayList.

myAL.Sort();

//Print the Values

PrintValues(myAL);

}

///

/// Find the value in the array List

///

///

///

public static void FindMyObject(ArrayList myList, Object myObject)

{

//Store the index value of the search value

int myIndex = myList.BinarySearch(myObject);

//Check the index exist or not

if (myIndex < 0)

Console.WriteLine("The object to search for ({0}) is not found.", myObject);

else

Console.WriteLine("The object to search for ({0}) is at index {1}.", myObject, myIndex);

}

///

/// Print the array list

///

///

public static void PrintValues(IEnumerable myList)

{

foreach (Object obj in myList)

Console.Write(" {0}", obj);

Console.WriteLine();

}

}

Choose the right collection type for your needs in dotnet framework

Choose the right collection type for your needs,

-when you are not concerned about the order of the items in the collections, your first inclination should be to use a System.Collections.Generic.Dictionary

-The three basic operations (Add, Remove, and Contains) all operate quickly even if the collection contains millions of items.

-Using the new LinkedList collection can potentially help you improve performance in scenarios where you need to maintain order yet still achieve fast inserts.

-Unlike List, LinkedList is implemented as a chain of dynamically allocated objects.

-The downside of a linked list from a performance point of view is increased activity by the garbage collector as it has to traverse the entire list to make sure objects were not disposed of.

-Inserting an item into a LinkedList is much faster than doing so into a List

-Dictionary may be most appropriate. However, lookup is only fast in the average case, and certain datasets could cause that performance to degrade quickly.

-SortedDictionary, uses a balanced tree implementation as the underlying data store; this provides relatively fast lookups and maintains items in a sorted order but insertions will most likely be slower (and will vary based on the number of items in the collection)

-Alternatively, you can also use SortedList, which uses two separate arrays to maintain the keys and the values separately and to maintain them both in order (in the worse case you would have to shift all the keys and values).

Custom Collections

-In certain situations, you may not find that all of your requirements are implemented in any of the existing collection types in the Framework.

-It may be that you have a unique set of requirements or that you can still use an existing collection with minor changes.

If you decide you want to write a custom collection, you should first look into extending an existing collection type.

System.Collections.ObjectModel namespace.

These collections already implement the most needed interfaces and give you the baseline functionality you expect.

Difference between namespace and assembly

Difference between namespace and assembly

Assembly

An Assembly is a logical unit of functionality that runs under the control of the .NET common language runtime (CLR). An assembly physically exists as a .dll file or as an .exe file.

An Assembly can contains one or many namespaces

Namespace

Namespaces are a way of grouping type names and reducing the chance of name collisions.

Get all table name in Database in Sql Server 2008

Get all table name in Database in Sql Server 2008


SELECT * FROM INFORMATION_SCHEMA.TABLES order by TABLE_NAME

View in Sql Server 2008

View in Sql Server 2008

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.

For example, a view can be used for the following purposes:

-To focus, simplify, and customize the perception each user has of the database.

-As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.

-To provide a backward compatible interface to emulate a table whose schema has changed.

Example

--Partitioned view as defined on Server1

CREATE VIEW Customers

AS

--Select from local member table.

SELECT *

FROM CompanyData.dbo.Customers_33

UNION ALL

--Select from member table on Server2.

SELECT *

FROM Server2.CompanyData.dbo.Customers_66

UNION ALL

--Select from mmeber table on Server3.

SELECT *

FROM Server3.CompanyData.dbo.Customers_99