Wednesday 15 February 2012

Sql Questions

Difference Between Delete and Truncate Command

1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.

Difference Between DataGrid and GridView

Introduction
The GridView and the DataGrid controls have a different logics.

There is a lot of differnce compatibility between DataGrid and GridView Codes
GridView can bulid pages on mobile devices
DataGrid in  1.x doesn't supports the themes however in 2.0 it perform themes.
The DataGrid in version 2.0 can also render adaptively,
GridView supports the classic bindingbased on the DataSource property.
GridView provides the option for paging ,sorting in easy click of a button however datagrid
required some coding
Grid View Features
· Richer design-time capabilities.
· Improved data source binding capabilities.
· Automatic handling of sorting, paging, updates, and deletes.
· Additional column types and design-time column operations.
· A Customized pager user interface (UI) with the PagerTemplate property.
· Different custom-paging support.
· Different event models.
For Example how the paging index differs
DataGrid
HTML
 <asp:DataGrid ID="DataGrid1" runat="server" OnPageIndexChanged="DataGrid1_PageIndexChanged"
 AllowPaging="true" PageSize="5" OnSelectedIndexChanged="d1_SelectedIndexChanged">
 </asp:DataGrid>   



Codebehind
protected void DataGrid1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
    {
      DataGrid1.CurrentPageIndex =  e.NewPageIndex  
      BindGrid();// Grid binding method
    } 
GridView
 <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="DataGrid1_PageIndexChanging"
  AllowPaging="true" PageSize="5" OnSelectedIndexChanged="d1_SelectedIndexChanged">
  </asp:GridView>   
Codebehind
protected void GridView1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
    {
      GridView1.PageIndex =  e.NewPageIndex  
      BindGrid();// Grid binding method
    }    

It allows for the mix both data bound and unbound, virtual columns in the GridView.
It has a special virtual mode allowing for the display of more than 100.000 rows without
a huge performance hit.
Individual columns, rows, cells or an entire data source can easily be styled.
private void InsertDataGridColumn()
{
DataSet dataset = new DataSet();
DataGridTableStyle TStyle = new DataGridTableStyle();
DataTable table = dataset.Tables["Student"];
TStyle .GridColumnStyles[0].MappingName ="Stid"
TStyle .GridColumnStyles[0].HeaderText = "Student ID";
TStyle .GridColumnStyles[0].Width = 150;
DataGrid1.TableStyles.Add(TStyle );
}
The above example is used for DataGrid TableStyle.
DataGridView control designing operations are done through the IDE design layout window via drag and drop.
We can extend the DataGridView control in a number of ways to build custom behaviors
into your applications .

Tuesday 14 February 2012

Using Session In Web Service

ASP.NET Web Services and Sessions
By default, Web Services are stateless. However, that doesn't mean that they cannot maintain state.Although you are often told that a statefull Web Service is a Bad Thing, it doesn't hurt to know that the possibility exists.In this article, Bob Swart shows how to add state management and session support to your web services, explaining what to do and what not to do, so you can decide for yourself if, where and when you want to use this ability.
Although there are many development environments that support ASP.NET web services, I want to focus on the issue at hand: adding and using session support in ASP.NET web services.And in order not to bind the steps to a particular IDE, I've decided to use notepad to enter plain C# code used in this article.
The Need For a Session?
The fact that a stateless Web Service is a good thing is explained by the fact that maintaining state requires effort: both in time and memory.Your Web Service will be slower and using more memory than a stateless Web Service.However, there are cases where you may feel you really need to add session support to your Web Services.As an example let's consider a simple guessing game Web Service, where the client can guess a number between 1 and 36, and the Web Service tells you if your guess is too high, too low or just right.This game can be implemented as a Web Service without the need to maintain state (I'll get back to that at the end of this article), but it's still a good example to illustrate how to maintain state and support sessions.
The initial Web Service definition of the GuessWebService consists of three methods: NewGame, Guess and Guesses.If you call the NewGame method, then a new random number is picker, and the guess count is reset to 0.The method Guess can be used to guess (pass) a number, returning 1 if the number is too high, -1 if the number is too low, or 0 if the number if right.Finally, the Guesses method returns the number of guesses made so far.The C# implementation of the game engine can be seen below:
<%@ WebService Language="C#" Class="Game.GuessWebService" %>

  using System;
  using System.Web.Services;

  namespace Game
  {
    [WebService(Namespace="http://www.eBob42.com")]
    public class GuessWebService: System.Web.Services.WebService
    {
      int SessionNumber;
      int SessionGuess;

      [WebMethod]
      public void NewGame()
      {
        SessionNumber = (new Random()).Next(0, 36);
        SessionGuess = 0;
      }

      [WebMethod]
      public int Guess(int Number)
      {
        SessionGuess++;
        if (Number < SessionNumber) return -1;
        else
          if (Number > SessionNumber) return 1;
          else
          {
            return 0;
          }
      }

      [WebMethod]
      public int Guesses()
      {
        return SessionGuess;
      }
    }
  }
The logic is all here: in the NewGame method you can assign values to the SessionNumber and SessionGuess fields, and you use them again in the Guess method.The only downside is that a Web Service is stateless, so no matter what you assign in the NewGame method, the values of SessionNumber and SessionGuess will be empty when you get into the Guess method.And as a side-effect, a guess of 0 for the number will always be right.Not much fun to play this game, if you ask me.
Enable Session
The crux of enabling sessions in Web Services is in the EnableSession property of the WebMethod attribute.You need to explicitly set EnableSession to true in the WebMethods that need session support.In my example, this means all three method (but in theory, there may be methods that are unrelated to the game at hand, like an instruction method or an about box, that do not need to have the EnableSession property set to true).Note that if you use the EnableSession property (which can be seen in the following listing), you also have to derive the Web Service class from the base class System.Web.Services.WebService.This is needed because the derived Web Service class needs access to the Application and Session objects.
Once you've set EnableSession property to true, session management still doesn't really work, since the values for SessionNumber and SessionGuess fields are not maintained between method call (you can test the Game.asmx file in a browser to experience this).Although you have enabled sessions for your Web Service, you haven't actually used the storage "container" to store the values of SessionNumber and SessionGuess (and anything else you want to store and retrieve at a later point in the session).The ASP.NET storage container is of course the Session object, which can contain values that consists of a name and a value, with the following easy syntax:
Session["Name"] = Value;
You can store just about anything in the session, although you have to remember that the session will be kept in memory at the server.One unique Session object per unique client session (containing all name/value pairs for that session), so this may not be the best (scaleable) solution if hundreds of users decide to play this game at the same time.Note that there's also an Application object, but that one is global for all sessions, and obviously not a good idea to use here, since each player wants to guess his/her own unique number (based on the individual session).
Anyway, removing the declarations for the SessionNumber and SessionGuess fields, and making sure to store and retrieve the Number and Guess from the session using the aforementioned syntax, results in the new source code for the GuessWebServices, as seen below:
<%@ WebService Language="C#" Class="Game.GuessWebService" %>

  using System;
  using System.Web.Services;

  namespace Game
  {
    [WebService(Namespace="http://www.eBob42.com")]
    public class GuessWebService: System.Web.Services.WebService
    {

      [WebMethod(EnableSession=true)]
      public void NewGame()
      {
        Session["Number"] = (new Random()).Next(0, 36);
        Session["Guess"] = 0;
      }

      [WebMethod(EnableSession=true)]
      public int Guess(int Number)
      {
        int SessionNumber = Convert.ToInt32(Session["Number"]);
        Session["Guess"] = Convert.ToInt32(Session["Guess"]) + 1;
        if (Number < SessionNumber) return -1;
        else
          if (Number > SessionNumber) return 1;
          else
          {
            return 0;
          }
      }

      [WebMethod(EnableSession=true)]
      public int Guesses()
      {
        return Convert.ToInt32(Session["Guess"]);
      }
    }
  }
You can now deploy the Game.asmx in a scripts directory (for example as http://www.eBob42.com/cgi-bin/Game.asmx) and import it in order to use it in a web service client application.
Importing Web Services
Using the WSDL command-line tool, you can import the Game web service and produce a GuessWebService.cs import unit with the GuessWebService proxy class.This import unit can be compiled to an assembly.You need to run the following two commands on the command-line to do this:
wsdl http://www.eBob42.com/cgi-bin/Game.asmx?WSDL
  csc /t:library GuessWebService.cs
This will produce the GuessWebService.dll assembly, containing the GuessWebService proxy class that you can use in your web service client applications.An example console client application can be seen below:
using System;

  namespace Game
  {
    class GuessGame
    {
      static void Main(string[] args)
      {
        GuessWebService MyGame = new GuessWebService();
        MyGame.NewGame();
        int Number = 0;
        int GuessResult = 0;
        do
        {
          Console.Write("New Guess (" + (MyGame.Guesses()+1).ToString() + "): ");
          Number = Convert.ToInt32(Console.ReadLine());
          GuessResult = MyGame.Guess(Number);
          switch (GuessResult)
          {
            case  1: Console.WriteLine("Too High!");  break;
            case -1: Console.WriteLine("Too Low!");   break;
            case  0: Console.WriteLine("Well Done!"); break;
          }
        }
        while (GuessResult != 0);
      }
    }
  }
You need to compile this client application with the /r:GuessWebService.dll command-line option, to specify that you need to compile the source code and link the executable with a reference to the GuessWebService.dll assembly. However, after all is said and done, the client executable still doesn't work right! You can easily see this, since it will be asking for the first guess over and over again.
Enable Sessions for the Client
Although you've enabled session support in the GuessWebService, that doesn't mean that any client will be able to work with sessions, too.The ASP.NET Web Services usually works with more than one client connection (each in its own unique session), so it must be able to know which incoming client request belongs to which Session object.The ASP.NET Web Service sends a cookie to the client with a unique session identifier (Session ID) that the client needs to use to identify itself when it wants to call subsequent methods - in the same session.The Session ID is unique for each session, and fortunately, the Session ID will also remain unchanged for the duration of the session.The cookies that are sent from the Web Service engine to the client are session cookies to be exact, so if you're using a browser they will not be stored on disk, but only kept in memory and returned automatically with new requests.Usually, however, the web service client is not a browser window, but a regular client executable, which doesn't automatically handle cookies.
To cut a long story short, in order to make sure the client executable returns the Session ID back to the Web Service, you need to receive and return the cookie with every request.Fortunately, this will be done automatically by the proxy class, after you've created the CookieContainer for this proxy class.In your example source code,.this means that you have to create a new CookieContainer and assign it to the CookieContainer property of the GuessWebService object (one additional line of code), as follows:
GuessWebService MyGame = new GuessWebService();
  MyGame.CookieContainer = new System.Net.CookieContainer(); // Session Cookie
After you've made sure the CookieContainer is assigned, then the Web Service client will work as expected.To verify that each session is unique, run two or more instances of the client executable to "test" that they all use a different Session ID and hence talk to a different Session object on the server, with a different number to guess.
Proxy Hack
Sometimes, there is a Web Service engine that I use in a number of my client applications.When this Web Service engine is statefull, it's a bit of a pain to remember to add the CookieContainer for every client application that I make.For those situations, I sometimes fall back to a little "hack" by editing the generated Web Service proxy class in the import file.In our example, that's the GuessWebService.cs file.Inside this file, you'll find the constructor of the proxy class, which typically consists of the following code for the constructor:
public GuessWebService() {
    this.Url = "http://www.eBob42.com/cgi-bin/Game.asmx";
  }
What you need to add, is a single line of code again, with a simple assignment to the CookieContainer in the constructor, again as follows:
public GuessWebService() {
    this.Url = "http://www.eBob42.com/cgi-bin/Game.asmx";
    this.CookieContainer = new System.Net.CookieContainer();
  }
This will remove the need for the explicit assignment of the CookieContainer property in the client applications (you can still add a CookieContainer - or do it twice if you want - but it's no longer necessary, since there already is one).
Note that the GuessWebService.cs is an autogenerated file and any changes made to this file will be lost if the code is regenerated (but you can avoid that by not unnecessarily regenerating the import file, of course).It works fine for me, but please use at your own risk.
Session Configuration
There are a number of session configuration settings you can consider when you really want to use session and state management in Web Services.The game that we've implemented is a nice statefull example, but when you exit a client game executable, the game may be over, but the session isn't.In fact, by default it will take another 20 minutes before the session - belonging to a game client that may not exist anymore - is released.During that time, the Session object takes up memory space (and also time, since more Session objects also means a slightly longer time to find the correct Session object based on the incoming Session ID).
In order to decrease the drain on the server, you can assign a new value - like 10 - to the Timeout property of the Session object, for example in the NewGame method.A value of 10 means 10 minutes.Every time the session is activated again (when the client calls another method with the EnableSession property set to true), the stopwatch resets.After 10 minutes of inactivity, the session is released.10 minutes may be good enough when the game starts, but when the user has guessed the right number, the Timeout can be set to something even lower.Like 1 minute (which gives the client application 1 minute to request the number of guesses - if needed - before the session is destroyed.If you try to be smart and set if to 0 minutes - or a negative number of minutes - you'll get an exception that explains that the argument to SetTimeout must be greater than 0.So, do you always have to wait at least one minute before a session is destroyed, you may ask? No, fortunately, you can also explicitly call the Abandon method of a Session, which will terminate it right away.You could call Abandon immediately after the right number has been guessed, but this has the sideeffect that the number of guesses (also stored in the Session object) will be gone as well.A better approach - not just for this example, but in general as well - is to use an explicit EndSession method (or in this case EndGame method) that will call the Session.Abandon method.
See the following code for the enhanced implementation of the GuessWebService using the Session.Timeout property and Session.Abandon method.
<%@ WebService Language="C#" Class="Game.GuessWebService" %>

  using System;
  using System.Web.Services;

  namespace Game
  {
    [WebService(Namespace="http://www.eBob42.com")]
    public class GuessWebService: System.Web.Services.WebService
    {

      [WebMethod(EnableSession=true)]
      public void NewGame()
      {
        Session["Number"] = (new Random()).Next(0, 36);
        Session["Guess"] = 0;
        Session.Timeout = 10;
      }

      [WebMethod(EnableSession=true)]
      public int Guess(int Number)
      {
        int SessionNumber = Convert.ToInt32(Session["Number"]);
        Session["Guess"] = Convert.ToInt32(Session["Guess"]) + 1;
        Session.Timeout = 1;
        if (Number < SessionNumber) return -1;
        else
          if (Number > SessionNumber) return 1;
          else
          {
            return 0;
          }
      }

      [WebMethod(EnableSession=true)]
      public int Guesses()
      {
        return Convert.ToInt32(Session["Guess"]);
      }

      [WebMethod(EnableSession=true)]
      public void EndGame()
      {
        Session.Abandon();
      }
    }
  }
If the game client forgets to call the EndGame method, then it will still only be one minute before the session is automatically destroyed.This at least ensures that the Web Service doesn't use more server resources than necessary, while still enabling a lot of people to play this little game at the same time.
Session Properties
Apart from the Session.Timeout property and Session.Abandon method, there are a few more helpful properties in the Session class (I won't cover them all, just the most useful in my view).The Count read-only property contains the number of items that are stored in the Session object (for the current session).Using the Keys property, you can retrieve all key values (the names) that are stored in the Session object - in case you don't know all names that have been stored in there.The IsNewSession read-only property is set to true if the session has just started with the current request (i.e.by calling this method), which can be helpful if you need to initialize the Session object.The SessionID read-only property contains the unique Session ID (this can be used as another proof that two different clients are using different sessions and are hence playing different instances of the game).
Finally, the special Mode property of the Session object is used to specify how the Session objects are managed by ASP.NET.When sessions are not used (EnableSession is set to false), then Mode has the value Off.If sessions are used, then the default value for Mode is InProc, meaning that the Session objects are maintained in memory on the web server machine.This is the best solution for statefull Web Services that run one a single web server machine.As a downside: if the ASP.NET worker needs to be terminated - even temporarily - then all Session objects and information will be terminated as well.
There are two alternative settings: SqlServer and StateServer.The latter means that the Session objects are maintained by the out-of-process running NT service state server, the aspnet_state.exe, which can even run on another machine (and will stay alive if the ASP.NET worker is terminated for some reason), and be shared by multiple Web Services on multiple machines, that can all point to the same state server on a special dedicated state machine.
The SqlServer setting means that the Session objects are actually stored in a SQL Server database, where session state data is placed into a blob field.This turns state management into a scaleable solution again (and is used for Web Services hosted in a Web farm for example), and is the only way where you can guarantee that session information is never lost.
Conclusion
Although Web Services are stateless by nature, it may sometimes be handy or necessary to add state management and session support to them.In this article, I've explained how they can be made statefull at the cost of being less scaleable.Apart from enabling sessions at the server side (in the Web Service engine), I've explained that you also need to enable cookies at the client or the proxy class in order to make sure the unique Session ID is received and returned for every call to the web service.
Finally, I've discussed some ways decrease the (negative) impact on your performance when using statefull Web Services

ASP.NET Life Cycle








The Two Step Process:
From 30,000 feet level ASP.NET request processing is a 2 step process as shown below.
Users Sends a requests to the IIS:-
 • ASP.NET creates an environment which can process the request. In other words it creates the application object, request, response and context objects to process the request.
 • Once the environment is created the request is processed through series of events which is processed by using modules, handlers and page objects. To keep it short lets name this step as MHPM (Module, handler, page and Module event), we will come to details later.
 
In the coming sections we will understand both these main steps in more details.


Step 1:- The user sends a request to IIS. IIS first checks which ISAPI extension can serve this request. Depending on file extension the request is processed. For instance if the page is an ‘.ASPX page’ then it will be passed to ‘aspnet_isapi.dll’ for processing.

Step 2:- If this the first request to the website then a class called as ‘ApplicationManager’ creates an application domain where the website can run. As we all know application domain creates isolation between two web applications hosted on the same IIS. So in case there is issue in one app domain it does not affect the other app domain.



Step 3:- The newly created application domain creates hosting environment i.e. the ‘HttpRuntime’ object. Once the hosting environment is created necessary core  ASP.NET objects like ‘HttpContext’ , ‘HttpRequest’ and ‘HttpResponse’ objects are created.

Step 4:- Once all the core ASP.NET objects are created ‘HttpApplication’ object is created to serve the request. In case you have a ‘global.asax’ file in your system then object of the ‘global.asax’ file will be created.

Please note ‘global.asax’ file inherits from ‘HttpApplication’ class.
Note: The first time an ASP.NET page is attached to an application, a new instance of ‘HttpApplication’ is created. Said and done to maximize performance, ‘HttpApplication’ instances might be reused for multiple requests.

Step 5:- The ‘HttpApplication’ object is then assigned to the core ASP.NET objects to process the page.

Step 6:- ‘HttpApplication’ then starts processing the request by http module events , handlers and page events. It fires the MHPM event for request processing.


Below image explains how the internal object model looks like for an ASP.NET request. At the top level is the ASP.NET runtime which has creates an ‘Appdomain’ which in turn has ‘HttpRuntime’ with ‘request’, ‘response’ and ‘context’ objects.


Once ‘HttpApplication’ is created it starts processing request it goes through 3 different sections ‘HttpModule’ , ‘Page’ and ‘HttpHandler’. As it moves through these sections it invokes different events which the developer can extend and add customize logic to the same. Before we move ahead lets understand what are ‘HttpModule’ and ‘HttpHandlers’. They help us to inject custom logic before and after the ASP.NET page is processed.
The main differences between both of them are:-
• If you want to inject logic based in file extensions like ‘.ASPX’ , ‘.HTML’ then you use ‘HttpHandler’. In other words ‘HttpHandler’ is an extension based processor.

 
• If you want to inject logic in the events of ASP.NET pipleline then you use ‘HttpModule’. ASP.NET . In other word ‘HttpModule’ is an event based processor.


Below is the logical flow of how the request is processed. There are 4 important steps MHPM as explained below :-
 Step 1(M? Http Module):- Client request processing starts. Before the ASP.NET engine goes and creates the ASP.NET ‘HttpModule’ emits events which can be used to inject customized logic. There are 6 important events which you can utilize before your page object is created ‘BeginRequest’,’AuthenticateRequest’,’AuthorizeRequest’,’ResolveRequestCache’,’AcquireRequestState’ and ‘PreRequestHandlerExecute’.

Step 2 (H? ‘Http Handler’ ) :- Once the above 6 events are fired , ASP.NET engine will invoke ‘ProcessRequest’ event if you have implemented ‘HttpHandler’ in your project.

Step 3 (P – ASP.NET page):- Once the ‘HttpHandler’ logic executes the ASP.NET page object is created. While the ASP.NET page object is created many events are fired which can help us to write our custom logic inside those page events. There are 6 important events which provides us placeholder to write logic inside ASP.NET pages ‘Init’ , ‘Load’ , ‘validate’ , ‘event’ , ‘render’ and ‘unload’. You can remember the word ‘SILVER’ to remember the events S – Start ( does not signify anything as such just forms the word ) , I – (Init) , L ( Load) , V ( Validate) , E ( Event) and R ( Render).

Step4 (M? HttpModule):- Once the page object is executed and unloaded from memory ‘HttpModule’ provides post page execution events which can be used to inject custom post-processing logic. There are 4 important post-processing events ‘PostRequestHandlerExecute’, ‘ReleaserequestState’, ‘UpdateRequestCache’ and ‘EndRequest’.                   

Below figure shows the same in a pictorial format.

         

Undestanding Trigger

QUOTION ASKED-----



  1. If we have multiple AFTER Triggers on table how can we define the sequence of the triggers     
  2. If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder.
  3. What are the different types of triggers in SQl SERVER?

 INTRODUCTION

TRIGGERS IN SQL SERVER

BACKGROUND

This article gives a brief introduction about Triggers in Sql Server 2000/2005.

What is a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)
(ii) Instead Of Triggers

(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:

(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this table, I will be attaching several triggers.

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another audit table. The main purpose of this audit table is to record the changes in the main table. This can be thought of as a generic audit trigger.

Now, create the audit table as:-
REATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

(a) AFTRE INSERT Trigger

This trigger is fired after an INSERT on the table. Let’s create the trigger as:-
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 set @audit_action='Inserted Record -- After Insert Trigger.';

 insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER INSERT trigger fired.'
GO
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same.
In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table.
To see the newly created trigger in action, lets insert a row into the main table as :
insert into Employee_Test values('Chris',1500);

Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to this table has inserted the record into the Employee_Test_Audit as:-
6   Chris  1500.00   Inserted Record -- After Insert Trigger. 2008-04-26 12:00:55.700

(b) AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let’s create the trigger as:-
CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test] 
FOR UPDATE
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 
 if update(Emp_Name)
  set @audit_action='Updated Record -- After Update Trigger.';
 if update(Emp_Sal)
  set @audit_action='Updated Record -- After Update Trigger.';

 insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER UPDATE Trigger fired.'
GO
The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table. There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from the update(column_name) function. In our trigger, we have used, if update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly checked the column Emp_Sal for an update.
Let’s update a record column and see what happens.
update Employee_Test set Emp_Sal=1550 where Emp_ID=6
This inserts the row into the audit table as:-
6  Chris  1550.00  Updated Record -- After Update Trigger.   2008-04-26 12:38:11.843 

(c) AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let’s create the trigger as:-
CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test] 
AFTER DELETE
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=d.Emp_ID from deleted d; 
 select @empname=d.Emp_Name from deleted d; 
 select @empsal=d.Emp_Sal from deleted d; 
 set @audit_action='Deleted -- After Delete Trigger.';

 insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER DELETE TRIGGER fired.'
GO
In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the audit table.
Let’s fire a delete on the main table.
A record has been inserted into the audit table as:-
6  Chris 1550.00  Deleted -- After Delete Trigger.  2008-04-26 12:52:13.867 
All the triggers can be enabled/disabled on the table using the statement
ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL 
Specific Triggers can be enabled or disabled as :-
ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

This disables the After Delete Trigger named trgAfterDelete on the specified table.

(ii) Instead Of Triggers

These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:-

(a) INSTEAD OF INSERT Trigger.
(b) INSTEAD OF UPDATE Trigger.
(c) INSTEAD OF DELETE Trigger.

(a) Let’s create an Instead Of Delete Trigger as:-
CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
 declare @emp_id int;
 declare @emp_name varchar(100);
 declare @emp_sal int;
 
 select @emp_id=d.Emp_ID from deleted d;
 select @emp_name=d.Emp_Name from deleted d;
 select @emp_sal=d.Emp_Sal from deleted d;

 BEGIN
  if(@emp_sal>1200)
  begin
   RAISERROR('Cannot delete where salary > 1200',16,1);
   ROLLBACK;
  end
  else
  begin
   delete from Employee_Test where Emp_ID=@emp_id;
   COMMIT;
   insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
   PRINT 'Record Deleted -- Instead Of Delete Trigger.'
  end
 END
GO
This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.
Now, let’s try to delete a record with the Emp_Sal >1200 as:-

delete from Employee_Test where Emp_ID=4
This will print an error message as defined in the RAISE ERROR statement as:-

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200 

And this record will not be deleted.
In a similar way, you can code Instead of Insert and Instead Of Update triggers on your tables.