Different Types of SQL Server Triggers

Triggers

Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

Types of Triggers

In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.

1. DDL Triggers

   In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.
   Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
   We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.
   DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.
   

2. DML Triggers

   In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types
   

   1. After Trigger (using FOR/AFTER CLAUSE)

      This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
      

   2. Instead of Trigger (using INSTEAD OF CLAUSE)

      This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
      

3. CLR Triggers

   CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
   We coded the objects(like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.
   

4. Logon Triggers

   Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.
   Synatx for Logon Trigger
   

   
   CREATE TRIGGER trigger_name
   ON ALL SERVER
   [WITH ENCRYPTION]
   {FOR|AFTER} LOGON
   AS
   sql_statement [1...n ] 
   
   

Syntax for Trigger

CREATE TRIGGER trigger_name
ON {table|view} 
[WITH ENCRYPTION|EXECUTE AS] 
{FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 
[NOT FOR REPLICATION] 
AS 
sql_statement [1...n ] 

1. trigger_name

   This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.

2. table|view

   This is the table/view on which the trigger is to be created.

3. ENCRYPTION

   This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted.

4. EXECUTE AS

   This option is optional. This option specifies, the security context under which the trigger is executed.

5. FOR/AFTER

   FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified.AFTER triggers cannot be defined on views.

6. INSTEAD OF

   INSTEAD OF specifies that the trigger is Instead Of Trigger.

7. CREATE|ALTER|DROP|INSERT|UPDATE|DELETE

   These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.

8. NOT FOR REPLICATION

   Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

9. AS

   After this we specifies the actions and condition that the trigger perform.

10. sql_statement

    These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements.

Note

1. The name of a trigger should follow the rules for identifiers.
2. DML trigger can be composed by any T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements.
3. You cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions.
4. If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server.
5. AFTER triggers cannot be defined on views.
6. AFTER is the default, if FOR is the only keyword specified.

Triggers in SQL

Triggers in SQL

Introduction

Triggers are a special kind of Stored Procedure that executes automatically when a user tries to modify a database. Triggers are built for SELECT, UPDATE, INSERT and DELETE statements; whenever a user tries to executes these queries to perform a transaction, triggers stop him and keep our database secure.

Sample Code for Triggers

use [Database_Name]
GO
Create Trigger Trigger_Name
on dbo.table_name
AFTER INSERT
AS
BEGIN
PRINT 'INSERTION IS NOT ALLOWED !! '
ROLLBACK TRANSACTION
end
Go

This code creates a trigger on a particular table; if a user tries to insert a value into this table, a trigger would be generated automatically which would prevent the user from any insertion.

A little bit of explanation about this code:

use [database] --the database which is in use
Create Trigger Trigger_Name --Creates a Trigger, specifying its name
on dbo.table_name --specifies the table where we want to set the Trigger
AFTER INSERT --Specify a keyword here "INSERT", "UPDATE", "DELETE" or "SELECT", after the keyword AFTER
ROLLBACK TRANSACTION -- Rolls a transaction back to a savepoint or beginning of transaction, in this case it will Rollback us to the original state of the table (no change will occur)

You can use the same code for other statements too.

For example if we don't want our user to update data in the database we would follow up with this trigger:

use [DatabaseName]
GO
create Trigger updateTrigger
on dbo.tableName
AFTER UPDATE
AS
BEGIN
PRINT 'UPDATION IS NOT ALLOWED'
ROLLBACK TRANSACTION
END
GO

To prevent deletion:

use [DatabaseName]
GO
create Trigger Delete_Trigger
on dbo.tableName
AFTER DELETE
AS
BEGIN
PRINT 'DELETION IS NOT ALLOWED'
ROLLBACK TRANSACTION
END
GO

To prevent selection:

use [DatabaseName]
GO
create Trigger Select_Trigger
on dbo.tableName
AFTER SELECT
AS
BEGIN
PRINT 'SELECTION IS NOT ALLOWED'
ROLLBACK TRANSACTION
END
GO

Conclusion

We can do many modifications with the same triggers. Use these and make your programming more efficient.

Creating and managing Triggers in SQL Server 2005/2008

In this article you will see how to Create and manage Triggers in SQL Server 2005/2008.

What are triggers:

Triggers are a special type of stored procedure which are executed automatically based on the occurrence of a database event. These events can be categorized as

1. Data Manipulation Language (DML) and
2. Data Definition Language (DDL) events.

The benefits derived from triggers is based in their events driven nature. Once created, the trigger automatically fires without user intervention based on an event in the database.

A) Using DML Triggers:

DML triggers are invoked when any DML commands like INSERT, DELETE, and UPDATE happen on the data of a table and or view.

Points to remember:

1. DML triggers are powerful objects for maintaining database integrity and consistency.
2. DML triggers evaluate data before it has been committed to the database.
3. During this evaluation following actions are performed.
• Compare before and after versions of data
• Roll back invalid modification
• Read from other tables ,those in other database
• Modify other tables, including those in other database.
• Execute local and remote stored procedures.

4. We cannot use following commands in DML trigger
• ALTER DATABASE
• CREATE DATABASE
• DISK DATABASE
• LOAD DATABASE
• RESTORE DATABASE

5. Using the sys.triggers catalog view is a good way to list all the triggers in a database. To use it, we simply open a new query editor window in SSMS and select all the rows from the view as shown below;
   select * from sys.triggers

So let us create DML trigger.

You can create and manage triggers in SQL Server Management Studio or directly via Transact-SQL (T-SQL) statements.

1) Using AFTER triggers:

• An AFTER trigger is the original mechanism that SQL Server created to provide an automated response to data modifications
• AFTER triggers fire after the data modification statement completes but before the statement's work is committed to the databases.
• The trigger has the capability to roll back its actions as well as the actions of the modification statement that invoked it.

For all examples shared below I have used Pubs database. You can download its msi file from here and then attach .mdf file in your SQL Sever 2008.

http://www.microsoft.com/downloads/en/details.aspx?
FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

CREATE TRIGGER tr_au_upd ON authors
AFTER UPDATE,INSERT,DELETE
AS
PRINT 'TRIGGER OUTPUT' +  CONVERT(VARCHAR(5),@@ROWCOUNT)
+ 'ROW UPDATED'
GO

UPDATE Statement

UPDATE authors
SET au_fname = au_fname
WHERE state ='UT'

Result:
----------------------------------------------------
TRIGGER OUTPUT2ROW UPDATED

(2 row(s) affected)

Point to remember:

1) If we have a constraint and trigger defined on the same column, any violations to the constraint abort the statement and the trigger execution does not occur. For example, if we have a foreign key constraint on a table that ensures referential integrity and a trigger that that does some validation on that same foreign key column then the trigger validation will only execute if the foreign key validation is successful.

Can we create more than one trigger on one table?

• We can create more than one trigger on a table for each data modification action. In other words, we can have multiple triggers responding to an INSERT, an UPDATE, or a DELETE command.
   
• The sp_settriggerorder procedure is the tool we use to set the trigger order. This procedure takes the trigger name, order value (FIRST, LAST, or NONE), and action (INSERT, UPDATE, or DELETE) as parameters.
  sp_settriggerorder tr_au_upd, FIRST, 'UPDATE'
   
• AFTER triggers can only be placed on tables, not on views.
   
• A single AFTER trigger cannot be placed on more than one table.
   
• The text, ntext, and image columns cannot be referenced in the AFTER trigger logic.

How to see inserted and deleted rows through Trigger:

• We can find rows modified in the inserted and deleted temporary tables.
• For AFTER trigger, these temporary memories –resident tables contains the rows modified by the statement.
• With the INSTEAD OF trigger, the inserted and deleted tables are actually temporary tables created on-the-fly.

Lets us try and see how this works;

a) Create a table titles_copy

SELECT *
INTO titles_copy
FROM titles
GO

b) Create a trigger on this table

CREATE TRIGGER tc_tr ON titles_copy
FOR INSERT , DELETE ,UPDATE
AS
PRINT 'Inserted'
SELECT title_id, type, price FROM inserted -- THIS IS TEMPORARY TABLE
PRINT 'Deleted'
SELECT title_id, type, price FROM deleted -- THIS IS TEMPORARY TABLE
--ROLLBACK TRANSACTION

c) Let us UPDATE rows. After which trigger will get fired.


We have written two statements in trigger, so these rows get printed. The inserted and deleted tables are available within the trigger after INSERT, UPDATE, and DELETE.

PRINT 'Inserted'
SELECT title_id, type, price FROM inserted -- THIS IS TEMPORARY TABLE
PRINT 'Deleted'
SELECT title_id, type, price FROM deleted -- THIS IS TEMPORARY TABLE

Result is based on below rule.

Statement   Contents of inserted      Contents of deleted
-----------------------------------------------------------------
INSERT         Rows added                     Empty
UPDATE        New rows                        Old rows
DELETE         Empty                             Rows deleted

2) INSTEAD OF Trigger:

1. Provides an alternative to the AFTER trigger that was heavily utilized in prior versions of SQL Server.
2. It performs its actions instead of the action that fired it.
3. This is much different from the AFTER trigger, which performs its actions after the statement that caused it to fire has completed. This means you can have an INSTEAD OF update trigger on a table that successfully completes but does not include the actual update to the table.
4. INSTEAD OF Triggers fire instead of the operation that fires the trigger, so if you define an INSTEAD OF trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in below example:

Let us create INSTEAD OF trigger.

if exists (select * from sysobjects
where id = object_id('dbo.cust_upd_orders')
and sysstat & 0xf = 8)
drop trigger dbo.cust_upd_orders
go

CREATE TRIGGER trI_au_upd ON authors
INSTEAD OF UPDATE
AS
PRINT 'TRIGGER OUTPUT: '
+CONVERT(VARCHAR(5), @@ROWCOUNT) + ' rows were updated.'
GO

Let us write an UPDATE statement now;

UPDATE authors
SET au_fname = 'Rachael'
WHERE state = 'UT'

-----------------------------------------------------
TRIGGER OUTPUT: 2 rows were updated.

(2 row(s) affected)

Let us see what has been updatded

SELECT au_fname, au_lname FROM authors
WHERE state = 'UT'

au_fname au_lname
----------------------
Anne Ringer
Albert Ringer


Lets see another example;

Create a Table

CREATE TABLE nayan (Name  varchar(32))
GO

Create trigger with INSTEAD.

CREATE TRIGGER tr_nayan ON nayan
INSTEAD OF DELETE
AS
    PRINT 'Sorry - you cannot delete this data'
GO

INSERT into nayan table

INSERT nayan
    SELECT 'Cannot' union
    SELECT 'Delete' union
    SELECT 'Me'
GO

Run the SQL DELETE statement.

DELETE nayan
GO

-------------------------------
Sorry - you cannot delete this data

(3 row(s) affected)

Run SELECT statement

SELECT * FROM nayan
GO

Result is below;

Name
-----------------
Cannot
Delete
Me

Points to remember:

1. As you can see from the results of the SELECT statement, the first name (au_fname) column is not updated to 'Rachael'. The UPDATE statement is correct, but the INSTEAD OF trigger logic does not apply the update from the statement as part of its INSTEAD OF action. The
   only action the trigger carries out is to print its message.
    
2. The important point to realize is that after you define an INSTEAD OF trigger on a table, you need to include all the logic in the trigger to perform the actual modification as well as any other actions that the trigger might need to carry out.
    
3. Triggering action-The INSTEAD OF trigger fires instead of the triggering action. As shown earlier, the actions of the INSTEAD OF trigger replace the actions of the original data modification that fired the trigger.
    
4. Constraint processing-Constraint processing-including CHECK constraints, UNIQUE constraints, and PRIMARY KEY constraints-happens after the INSTEAD OF trigger fires.
    
5. If you were to print out the contents of the inserted and deleted tables from inside an Instead Of trigger, you would see they behave in exactly the same way as normal. In this case, the deleted table holds the rows you were trying to delete, even though they will not get deleted.

Benefits of INSTEAD Triggers:

• We can define an INSTEAD OF trigger on a view (something that will not work with AFTER triggers) and this is the basis of the Distributed Partitioned Views that are used so split data across a cluster of SQL Servers.
• We can use INSTEAD OF triggers to simplify the process of updating multiple tables for application developers.
• Mixing Trigger Types.

B) Using DDL Triggers:

1. These triggers focus on changes to the definition of database objects as opposed to changes to the actual data.
2. This type of trigger is useful for controlling development and production database environments.

Let us create DDL trigger now;

Below is the syntax.

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }

CREATE TRIGGER tr_TableAudit
ON DATABASE
FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
      PRINT 'You must disable the TableAudit trigger in order
              to change any table in this database'
    ROLLBACK
GO

Other way of writing the same query in more optimized way is below;

IF EXISTS(SELECT * FROM sys.triggers
WHERE name = N'tr_TableAudit' AND parent_class=0)
DROP TRIGGER [tr_TableAudit] ON DATABASE
GO
CREATE TRIGGER tr_TableAudit ON DATABASE
FOR DDL_TABLE_EVENTS
AS
      PRINT 'You must disable the TableAudit trigger in
               order to change any table in this database'          
    ROLLBACK
GO

Let us try to run a DDL command. See the result in below figure

Now let us look at an example that applies to server-level events. Above example was scoped at database level.

Let us create a trigger which prevents changes to the server logins. When this trigger is installed, it displays a message and rolls back any login changes that are attempted.

CREATE TRIGGER tr_LoginAudit
ON ALL SERVER
FOR CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN
AS
      PRINT'You must disable the tr_LoginAudit trigger before making login changes'
      ROLLBACK
GO

C) Using CLR Trigger:

1. CLR triggers are trigger based on CLR.
2. CLR integration is new in SQL Server 2008. It allows for the database objects (such as trigger) to be coded in .NET.
3. Object that have heavy computation or require reference to object outside SQL are coded in the CLR.
4. We can code both DDL and DML triggers by using a supported CLR language like C#.

Let us follow below simple steps to create a CLR trigger;

Step 1: Create the CLR class. We code the CLR class module with reference to the namespace required to compile CLR database objects.

Add below reference;

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

So below is the complete code for class;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

namespace CLRTrigger
{
    public class CLRTrigger
    {
        public static void showinserted()
        {
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            SqlConnection conn = new SqlConnection(" context connection =true ");
            conn.Open();
            SqlCommand sqlComm = conn.CreateCommand();
            SqlPipe sqlP = SqlContext.Pipe;
            SqlDataReader dr;
            sqlComm.CommandText = "SELECT pub_id, pub_name from inserted";
            dr = sqlComm.ExecuteReader();
            while (dr.Read())
                sqlP.Send((string)dr[0] + "," + (string)dr[1]);
        }

    }
}

Step 2: Compile this class and in the BIN folder of project we will get CLRTrigger.dll generated. After compiling for CLRTrigger.dll, we need to load the assembly into SQL Server

Step 3: Now we will use T-SQL command to execute to create the assembly for CLRTrigger.dll. For that we will use CREATE ASSEMBLY in SQL Server.

CREATE ASSEMBLY   triggertest
FROM 'C:\CLRTrigger\CLRTrigger.dll'
WITH PERMISSION_SET = SAFE

Step 4: The final step is to create the trigger that references the assembly. Now we will write below T-SQL commands to add a trigger on the publishers table in the Pubs database.

CREATE TRIGGER tri_Publishes_clr
ON publishers
FOR INSERT
AS
      EXTERNAL NAME triggertest.CLRTrigger.showinserted

If you get some compatibility error message run the below command to set compatibility.

ALTER DATABASE pubs
SET COMPATIBILITY_LEVEL =  100

Step 5: Enable CLR Stored procedure on SQL Server. For this run the below code;

EXEC sp_configure 'show advanced options' , '1';
reconfigure;

EXEC sp_configure 'clr enabled' , '1' ;
reconfigure;

EXEC sp_configure 'show advanced options' , '0';
reconfigure;

Step 6: Now we will run INSERT statement to the publishers table that fires the newly created CLR trigger.

INSERT publishers
(pub_id, pub_name)
values ('9922','Vishal Nayan')

The trigger simply echoes the contents of the inserted table. The output from the trigger is based on the insertion above.

-----------------------------------------------------
9922,Vishal Nayan

(1 row(s) affected)


The line of code which is printing the query result is actually below code written in a managed environment.

while (dr.Read())
sqlP.Send((string)dr[0] + "," + (string)dr[1]);

Conclusion:

The tri_Publishes_clr trigger demonstrates the basic steps for creating a CLR trigger. The true power of CLR triggers lies in performing more complex calculations, string manipulations and things of this nature that the can be done much more efficiently with CLR programming languages than they can in T-SQL.

Insert, Update, Delete In Gridview Using Single Stored Procedure

Insert, Update, Delete In Gridview Using Single Stored Procedure

 

Background

Sometimes there is a need to insert, update and delete records in a GridView using a single Stored Procedure instead of creating separate Stored Procedures for each operation.

Suppose I have one .aspx web page in which I need a to insert, view, update and delete records. To do that, instead of creating four Stored Procedures to perform these tasks I will create a single Stored Procedure to satisfy my requirements and I will access it in code behind depending on the action performed by the end user on a button click.

I have written this article specially focusing on newcomers and anyone new wants to insert, update and delete records in a GridView using a Single Stored Procedure, so let us start with a basic introduction.

First create the the table named employee as:

 

 

I have set the primary key on the id column and I have set the Identity specification to Yes.

Now we have a table to perform these operations for. Now let us start to create the Stored Procedure.

The Stored Procedure is created using the keyword "Create Procedure" followed by the procedure name. Let us create the Stored Prcedure named "EmpEntry" as in the following:

create Procedure EmpEntry
(
 --variable  declareations

@Action Varchar (10),                             --to perform operation according to string passed to this varible such as Insert,update,delete,select    
@id int=null,                                   --id to perform specific task
@FnameVarchar (50)=null,                     -- for FirstName
@MName Varchar (50)=null,                    -- for MName
@Lname Varchar (50)=null                      -- for LastName

)

as
Begin
  SET NOCOUNT ON;

If @Action='Insert'   --used to insert records
Begin
Insert Into employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End  

else if @Action='Select'   --used to Select records
Begin
select *from employee
end

else if @Action='Update'  --used to update records
Begin
 update employeeset FirstName=@Fname,MName=@MName,LastName=@Lname where id=@id
 End
 Else If @Action='delete'  --used to delete records
 Begin
 delete from employeewhere id=@id
 end
 End

The comments in the Stored Procedure above clearly explain which block is used for which purpose, so I have briefly explained it again. I have used the @Action variable and assigned the string to them and according to the parameter passed to the Stored Procedure the specific block will be executed because I have kept these blocks or conditions in nested if else if conditional statements.

 "The most important thing is that I have assigned null to each variable to avoid the effect on the parameter passed to the Stored Procedure because we are passing a different number of parameters but not the same number of parameters to the Stored Procedure to perform these tasks."

Now create the one sample application "Empsys" as:

1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
2. "File" - "New Project" - "C#" - "Empty Web Application" (to avoid adding a master page).
3. Provide the web site a name such as  "Empsys" or another as you wish and specify the location.
4. Then right-click on Solution Explorer - "Add New Item" - "Default.aspx page".
5. Drag and drop one button, three textboxes, one GridView and one hidden field to pass the hidden value to the database and one label on the <form> section of the Default.aspx page.

Then switch to the design view; the <form> section of the Default aspx page source will look as in the following:

<form id="form1"runat="server">
    <div>
First Name  <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
Middle Name<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
Last Name <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        <asp:ButtonID="Button1"runat="server"Text="save"onclick="Button1_Click" />
    </div>
<asp:HiddenField ID="HiddenField1" runat="server"/>
 <asp:GridViewID="GridView1"runat="server" >
     </asp:GridView>
</form>

Now use the following GridView event properties to perform events such as update, delete, edit cancel and so on. Let us see what the properties are:

• DataKeyNames: This property I have used to the the row index of GridView  
• OnRowEditing: This property is used to handle the event when the user clicks on the edit button
• OnRowCancelingEdit: This property is used to handle the event when the user clicks on the Cancel button that exists after clicking on the edit button
• OnRowDeleting: This property is used to handle the event when the user clicks on the delete button that deletes the row of the GridView
• OnRowUpdating: This property is used to handle the event when the user clicks on the update button that updates the Grid Record 

Now my grid will look such as the following:

<asp:GridViewID="GridView1" runat="server" DataKeyNames ="id"OnRowEditing ="Edit"              
        OnRowCancelingEdit ="canceledit"    OnRowDeleting ="delete"    OnRowUpdating = "Update" >

     </asp:GridView>

On the preceding GridView properties I have assigned the method name to be called for particular operations.

Method to Insert Data in Database  

Right-click from the design page and view the code and then write the following code in the default.aspx.cs page to save the inserted records in the database:

protected void empsave(object sender, EventArgs e)
{
      connection();
      query =  "studentEntryView";          //Stored Procedure name
      SqlCommand com = new SqlCommand(query, con);  //creating  SqlCommand  object
      com.CommandType = CommandType.StoredProcedure;  //here we declaring command type as stored Procedure

       /* adding paramerters to  SqlCommand below *\
      com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();//for passing hidden value to preform insert operation
       com.Parameters.AddWithValue("@FName",TextBox1.Text.ToString());        //first Name
       com.Parameters.AddWithValue("@Mname ", TextBox2.Text.ToString());     //middle Name
       com.Parameters.AddWithValue("@LName ",TextBox3.Text.ToString());       //Last Name
       com.ExecuteNonQuery();                     //executing the sqlcommand
       Label1.Visible = true;
       Label1.Text = "Records are Submitted Successfully";
}

Now create the mehtod to view the records in the GridView:

public void viewdata()
{
    connection();
    query = "studentEntryView";
    SqlCommand com = new SqlCommand(query, con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.AddWithValue("@Action", HiddenField2.Value).ToString();
    DataSet ds =new DataSet();
    SqlDataAdapter da =  new SqlDataAdapter(com);
    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
}

The following is method for the "OnRowEditing" Event:

        protected void edit(objectsender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex= e.NewEditIndex;
            gedata();
     
        }

The following is method for the "OnRowCancelingEdit" Event:

protected void  canceledit(object sender, GridViewCancelEditEventArgs e)
        {

            GridView1.EditIndex = -1;
            gedata();
        }

The following is method for the "OnRowDeleting" Event:

 protected void delete(object sender, GridViewDeleteEventArgs e)
        {
            connection();
            int id =  int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
            HiddenField1.Value = "Delete";
            query = "EmpEntry";
            com = new SqlCommand(query, con);
            com.CommandType =CommandType .StoredProcedure;
            com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
            com.Parameters.AddWithValue("id", SqlDbType.Int).Value = id;
            com.ExecuteNonQuery();
            con.Close();
            gedata();
                 
        }

  The following is method for the "OnRowUpdating" Event:

 protected void update(object sender, GridViewUpdateEventArgs e)
        {
            connection();
            int id=int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
            HiddenField1.Value = "update";
            query = "EmpEntry";
            com = new SqlCommand(query, con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
            com.Parameters.AddWithValue("@FName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.ToString());
            com.Parameters.AddWithValue("@MName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text.ToString());
            com.Parameters.AddWithValue("@LName", ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text.ToString());
            com.Parameters.AddWithValue("@id", SqlDbType.int ).Value = id;
            com.ExecuteNonQuery();
            con.Close();
            GridView1.EditIndex = -1;
            gedata();
       
        }

Brief introduction to the code

In the sample code above I have used the two string queries for giving the Stored Procedure name and the constr for storing the connection from the web.config file and another thing is that I have used a hidden field by which I am passing the action values that are required to our Stored Procedure.

Now our application is ready to use, press F5 or other as you know, then enter the some values to TextBox and press the "Save" button.



Now after clicking on the "Save" button, the hidden field value takes the value "Insert" and passes it to the Stored Procedure as the action and because of this the Stored Procedure will execute a particular type of block.

Now at page load I have called the method, so after that the grid will fill as in:



Now click on the Edit button that calls the edit method as shown in the following grid:



If you click on the "Cancel" button then the editcancel method will be called and edit mode will be cancelled. Now enter some values into the grid TextBox and click on an update button that calls the update method and then the records in the GridView will be updated as in:



Now click on the delete button that calls the delete method and deletes the records from the GridView
 

Note:

• For detailed code please download the zip file attached above.
• Don't forget to update the Web.config file for your server location. 

Summary
 

From all the examples above we see how to reduce the code required to perform these tasks. In the next article I will explain how to Implement a 2-tier architecture which makes my code much simpler and reusable, I hope this article is useful for all students and beginners. If you have any suggestion related to this article then please contact me.