Friday, August 31, 2007

Storing and Retrieving doc/pdf/xls files in SQL Server

         I have explained how to store and retrieve image file in SQL Server in my previous post. I received many comments mentioning that how can we store and retrieve doc or pdf or excel (or any type of file) in SQL Server. Few friends (developers) have also posted comment that they receive only 13 byte when they retrieve stored image or doc or xls or pdf or rtf file. So I thought let be write another blog for them. Sorry friends I am bit late in writing this article and mean while you also have solved your issues. However this may help some new friends.

         In this example I have used a table which has four fields. Below is the script for table,

CREATE TABLE [TestTable]
(
         [ID] [int] IDENTITY(1,1) NOT NULL,
         [FileName] [nvarchar](15) NOT NULL,
         [Extension] [nvarchar](5) NOT NULL,
         [Content] [image] NULL
)

Fig - (1) Scrpit for Table

         In my demo project I have used one file Upload control (to upload the file), one Textbox (where user can enter ID for uploaded file to retrieve it) and 2 buttons (one for uploading file and other for retrieving).

         When user select the file and click on Upload button the code stores the selected file in database. Below is the code for that,

    using (SqlConnection cnn = new SqlConnection("Connection
                                                      String"
))
    {
            cnn.Open();
            SqlCommand cmd =
                                new SqlCommand("InsertFile", cnn);
            cmd.CommandType =
                               CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter
                
("@FileName", "Name of  Uploaded File"));
                    
            cmd.Parameters.Add(new SqlParameter
              
("@Extension", "Extension of Uploaded File"));
                    
            cmd.Parameters.Add(new SqlParameter
               
("@Content", "byte array
               (byte[]) of uploaded file"
));

            cnn.Close()
     }

   Fig - (2) Code for inserting selected file in database.    

          Now when user enter FileID for uploaded file in textbox and click on retrieve button we will get the Content and extension field from database for that file id. You can use FillDataSet method to retrieve the byte array. Below code shows how to send retrieved file to user depending on the extension.

       string strExtenstion = "extension of retrieved file";
       byte[] bytFile =  "Byte array retrieved from database";

       Response.Clear();
       Response.Buffer = true;


       if (strExtenstion == ".doc" || strExtenstion == ".docx")
       {
            Response.ContentType = "application/vnd.ms-word";
            Response.AddHeader("content-disposition"
                                       "attachment;filename=Tr.doc");
       }
       else if (strExtenstion == ".xls" || strExtenstion == ".xlsx")
       {
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition"
                                       "attachment;filename=Tr.xls");
       }
       else if (strExtenstion == ".pdf")
       {
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-disposition"
                                       "attachment;filename=Tr.pdf");
       }

       Response.Charset = "";
       Response.Cache.SetCacheability(HttpCacheability.NoCache);

       // If you write,
         
// Response.Write(bytFile1);
         
// then you will get only 13 byte in bytFile.
       Response.BinaryWrite(bytFile);

       Response.End();

Fig - (3) Code to retrieve the file from database.

Happy Programming !!!

 

 

Thursday, August 30, 2007

Great tool for LINQ Query

       Joseph Albahari has created a great tool for LINQ query expression. You can dwnload it from here.

Happy Programmig !!

Saturday, August 25, 2007

How to use transaction in LINQ using C#

I installed VS 2008 beta 2 before few days and started exploring it. I looked in to the LINQ and found very interesting. LINQ generates DataContext class which provides classes and methods which is used in OR-Mapping. You can also use your own stored procedures and views with LINQ. You may require to use transaction with your own SPs for Insert, Delete or Update operations.

System.Data.Common.DbTransaction class provides the Transaction object. I have used Northwind database in this example. Lets start with new project, you can select new project from Start -> All Programs -> Microsoft Visual Studio 2008 Beta 2 and click on Microsoft Visual Studio 2008 Beta 2. Create new Asp.net website. Right click on website from solution explorer and select LINQ to SQL classes from Add New Item.

This will generate dbml file in App_Code folder. Select the tables, views, stored procedures and function from server explorer and drag it on dbml file. DataContext class generates methods for each SPs, functions and views.

I have used Category and Product tables in this example. I have created two SPs InsertCategory and InsertProduct for inserting records in appropriate tables. You can see your SPs when you create the object of DataContext class.

I will first insert the category and then insert product for newly created category. If you have used some parameters as OUT parameters in your SP, you need to pass these parameters as Ref in calling method. In my SPs I have used CategoryID and ProductID as OUT parameter in SP.

Now, lets look in to the Transaction. I want that either category and product both will be added in database or none of them will be inserted. Below is the code for that,

System.Data.Common.DbTransaction trans = null;
DataClassesDataContext objDataClass = new DataClassesDataContext
(ConfigurationManager.ConnectionStrings
[Constants.ConnectionString].ConnectionString);
try
{

// Nullable data type as the methods generated for
//SP will use Nullable type
int? intCategoryID =0;
int? intProductID =0;

// Open the connection
objDataClass.Connection.Open();

// Begin the transaction
trans = objDataClass.Connection.BeginTransaction();

// Assign transaction to context class
// All the database operation perform by this object
//will now use transaction

objDataClass.Transaction = trans;

// Insert Category
// I have to use Ref keyword CategoryID of newly
//added category will be assign to this variable

objDataClass.InsertCategory
(
ref intCategoryID,
txtName.Text.Trim().Replace("'", "''"),
txtDescription.Text.Trim().Replace("'", "''"),
new byte[0]
);

// Insert Product
// I have to use Ref keyword as ProductID of newly
// generated product will be assign to this variable

objDataClass.InsertProduct
(
ref intProductID,
txtProductName.Text.Trim().Replace("'","''"),
null,
intCategoryID,
txtQuantityPerUnit.Text.Trim().Replace("'", "''"),
Convert.ToDecimal(
txtUnitPrice.Text.Trim().Replace("'", "''")
),
null,
null,
null,
0
);

// Commit transaction
trans.Commit();


}
catch (Exception ex)
{

// Rollback transaction
if (trans != null)
trans.Rollback();
}
finally
{

// Close the connection
if (objDataClass.Connection.State ==
ConnectionState
.Open)
objDataClass.Connection.Close();
}

Fig - (3) Code for Transaction in LINQ using C#

Happy Programming !!

Thursday, August 23, 2007

Raise event from user control to main page / Event delegation from user control to aspx page in ASP.NET,C#

          "What is delegate?" we all have faced this question in one or more interview. :) and the most common answer is "Function pointer". Here I am showing a simple example of delegate. I have one user control and one aspx page. The user control contains one button. When user click on this button I will call a method on main page using delegate. Here is my user control,

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="WebUserControl.ascx.cs" Inherits="Dalegate_WebUserControl" %>

<
asp:Button ID="btnTest" runat="server" Text="I am Inside User Control" OnClick="btnTest_Click" />

Fig - (1) WebUserControl.ascx

            On WebUserControl.ascx.cs I have written simple delegate and event handler as shown below,

public partial class Dalegate_WebUserControl : System.Web.UI.UserControl
{

    // Delegate declaration
    public delegate void OnButtonClick(string strValue);

    // Event declaration
    public event OnButtonClick btnHandler;
   
    // Page load
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }

    protected void btnTest_Click(object sender, EventArgs e)
    {
           // Check if event is null
           if (btnHandler != null)
               btnHandler(string.Empty);

           // Write some text to output
           Response.Write("User Control's Button Click <BR/>");
    }
}

Fig - (2) WebUserControl.ascx.cs

          Above code first check whether btnHandler is not null and than raise the event by passing argument. You can pass any number of argument in event. You need to change public delegate void OnButtonClick(string strValue) and btnHandler(string.Empty) lines for changing number of arguments. Now take a look at aspx page,

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Dalegate_Default" %>

<%@
Register Src="WebUserControl.ascx" TagName="WebUserControl" TagPrefix="uc1" %>

<!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<
html xmlns="http://www.w3.org/1999/xhtml" >
<
head runat="server">
    <title>Untitled Page</title>
</
head>
<
body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblText" Text="I am On Main Page : "
                   runat
="server"></asp:Label>
        <asp:DropDownList ID="ddlTemp" runat="server">
            <asp:ListItem>Chirag</asp:ListItem>
            <asp:ListItem>Dipak</asp:ListItem>
            <asp:ListItem>Shailesh</asp:ListItem>
        </asp:DropDownList>
        <br />
        <br />
        <uc1:WebUserControl ID="WebUserControl1"
           runat="server" />    
    </div>
    </form>
</
body>
</
html>

Fig - (3) Default.aspx

          Default.aspx has one drop down list and a user control as shown in above code. Lets look at cs file,

public partial class Dalegate_Default : System.Web.UI.Page
{
      protected void Page_Load(object sender, EventArgs e)
      {
             // Declare and Define Event of User Control. When User
             // Clicks on button (which is inside UserControl)
             // below event is raised as I have called raised that
             // event on Button Click
             WebUserControl1.btnHandler += new 
             Dalegate_WebUserControl
.OnButtonClick
            
(WebUserControl1_btnHandler);
       
      }

      void WebUserControl1_btnHandler(string strValue)
      {
             Response.Write("Main Page Event<BR/>Selected
                  Value: "
+ ddlTemp.SelectedItem.Text + "<BR/>");
      }   
}

 Fig - (4) Default.aspx.cs

            Now when you run the application and clicks on button you can see that when user click on button the user control raise the click event and calls the WebUserControl1_btnHandler(string strValue) method on main page.

Happy Programming !!!

Tuesday, August 21, 2007

Application failed to initialize properly(0xcC000142). Press OK to terminate the application

Hi,

            I am having a small issue. I am calling a console application from ASP.NET 2.0 website. Below is my code,

string strPassword = Convert.ToString("Password");

System.Security.SecureString ssPassword = new System.Security.SecureString();

foreach (char chr in strPassword)
{
          ssPassword.AppendChar(chr);
}

System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo("Path of EXE");
info.UseShellExecute = false;
info.Arguments = "ARGUMENTS";
info.UserName = Convert.ToString("UserName");
info.Password = ssPassword;
info.Domain = Convert.ToString("Domain");

System.Diagnostics.Process.Start(info);

Fig - (1) code for calling exe from asp.net

            When I run (by pressing F5 from VS) it works fine and console application loads. However once I have deployed the application console application does not load and gives error "Application failed to initialize properly(0xcC000142). Press OK to terminate the application".
                   I have used proper credential(Machine's Admin User Name and Password on which web application is deployed. Console application is on same machine).

Early response is highly appreciated.   :)

Hide console window in console application

          As you all know when you execute an console application it will load command prompt and shows out put. There are amny situation in which you do not want this console window to be dislayed to user while your application runing. You can do that by calling some unmanaged functions. I have used following method to do that which is posted by Brendan Grant on MSDN.

using System.Runtime.InteropServices;

[DllImport("user32.dll")]
public static extern IntPtr
FindWindow(string lpClassName, string lpWindowName);

[DllImport("user32.dll")]
static extern bool ShowWindow(IntPtr hWnd, int nCmdShow);

IntPtr hWnd = FindWindow(null, title);

 if (hWnd != IntPtr.Zero)
 {
       if (!visible)
          //Hide the window                    
          ShowWindow(hWnd, 0); // 0 = SW_HIDE               
       else
          //Show window again                   
          ShowWindow(hWnd, 1); //1 = SW_SHOWNORMA          
}

Fig - (1) Code to hide console window in console application

Happy Programming !!!

Thursday, August 16, 2007

Viewpoint content is not displayed OR Mime type for Viewpoint content

        We are using Viewpoint 3-D content in our LMS (Learning Management System). The content is working fine in my local machine. However when I uploaded the LMS on deployment server the contect stopped working. The content loads perfectly. User can listen the sound and can change the step and steps, however 3-D content is not visible. After doing dome google search I found about Mime type settings in IIS. Viewpoint content requires Mime type added in IIS. Once I added required Mime type content it start working.

        Viewpoint require ".mts", ".mtx", ".mtz" and ".mzv" mime types. You have to add these mime types in IIS to make viewpoint content working. You can add Mime type by,

        1. Click on Start Menu
        2. Select Run.
        3. Type inetmgr and press enter.
        4. Select perticular Website or Virtual directory to add mime
            type for that website/Virtual directory. If you want to
            add for all the sites select Default Website and click on
            Property.
        5. Click on HTTP Headers and select Mime type.
        6. In the Extension field, type .mts.
        7. In the Content Type field, type application/metastream, 
           and then click OK.
        8. Repeat for .mtx, .mtz, and .mzv files.

Happy Programming !!

Wednesday, August 08, 2007

Generate thumbnail image in dotnet

          If you have created any e-commerce site, you must have used thumbnail image to display product. You may have seen many sites which display thumbnail image and product description and when user click on image it shows large image of the product.  You can do this by uploading two different images for each product OR by uploading a single image and runtime generate thumbnail image from that. You can easily do that in dotnet using "GetThumbnailImage" method. Below is the code for that,

function GenerateThumbnailImage()
{

        System.Drawing.Image imgThumbImage;
        System.Drawing.Image imgOriginalImage = 
                 new Bitmap(Server.MapPath(".")
                       + "\\Images\\bike1.jpg");

        using (imgThumbImage = 
            imgOriginalImage.GetThumbnailImage(50, 50, 
            new System.Drawing.Image.GetThumbnailImageAbort 
            (Abort), IntPtr.Zero))
        {
                imgThumbImage.Save(Response.OutputStream, 
                System.Drawing.Imaging.ImageFormat.Jpeg);
        }
}

private bool Abort()
{
        return false;
}

Fig - (1)  Generate thumbnail image

Happy Programming !!

Get height and width of image / Generate image object from byte array

          We are storing image in database in our recent project. We do not store height and width for that image in database.  After few months of deployment the client wants to display the height and width of each image while we display image.  As we do not store height and width in database we need to find that runtime when we load the image from database. Below is the code that we used to solve our issue,

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
            try
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand("select
                             [Image] from   tableName where ID = 1"
);
                cmd.Connection = cnn;
                Byte[] bytImage = (Byte[])cmd.ExecuteScalar();

               // Generate Image from Byte array
                System.Drawing.Image img =
                            System.Drawing.Image.FromStream
                            (new System.IO.MemoryStream(bytImage));

                // If you are storing image in specific folder instead
                // of database then you can use following code to
                // generate image

                System.Drawing.Image img = 
                            System.Drawing.Image.FromFile
                            (@"D:\ExampleWeb Projects\Learn2K5
                            \ms.gif"
);

                //  Display Height and Width
                Response.Write("Height : " + img.Height.ToString()
                             + "   Width : " + img.Width.ToString());
            }
            finally
            {
                cnn.Close();
            }
}

Fig - (1) Generate image object from byte array and get height and width

Happy Programming !!!

Wednesday, August 01, 2007

Using the inserted and deleted Tables in SQL Sever

          DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

         In DML triggers, the inserted and deleted tables are primarily used to perform the following:

            1. Extend referential integrity between tables.
            2. Insert or update data in base tables underlying a view.
            3. Test for errors and take action based on the error.
            4. Find the difference between the state of a table
                before and after a data modification and 
                take actions based on that difference.

          The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

          The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

          An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

          When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table when testing an INSERT or the inserted table when testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

           SQL Server 2005 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. For more information, see CREATE TRIGGER (Transact-SQL).

An Example of Using the inserted Table in a Trigger to Enforce Business Rules

           Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

           The following example creates a DML trigger. This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. If the credit rating is too low, a message is displayed and the insertion does not execute.

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
   @vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i ON p.PurchaseOrderID =
   i.PurchaseOrderID JOIN Purchasing.Vendor v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
   RAISERROR ('This vendor''s credit rating is too low to accept new
      purchase orders.
', 16, 1)
ROLLBACK TRANSACTION
END

 

Using the inserted and deleted Tables in INSTEAD OF Triggers

         The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to a column in the base table.

         The following rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

      1. Values cannot be specified for computed columns or
          columns with a timestamp data type.
      2. Values cannot be specified for columns with an IDENTITY
          property, unless IDENTITY_INSERT is ON for that table.
          When IDENTITY_INSERT is ON, INSERT statements must
          supply a value.
      3. INSERT statements must supply values for all NOT NULL
          columns that do not have DEFAULT constraints.
      4. For any columns except computed, identity, or
            timestamp
columns, values are optional for any
          column that allows nulls, or any NOT NULL column that 
          has a DEFAULT definition.

         When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the SQL Server 2005 Database Engine calls the trigger instead of taking any direct action against any table. The trigger must use the information presented in the inserted and deleted tables to build any statements required to implement the requested action in the base tables, even when the format of the information in the inserted and deleted tables built

         The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:

 

CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID

         The result set for this view has three columns: an int column and two nvarchar columns. The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named EmployeeID, an nvarchar column named LName, and an nvarchar column named FName.

The select list of a view can also contain expressions that do not directly map to a single base-table column. Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. An INSTEAD OF trigger on the view must handle these types of expressions. For more information, see Expressions and Computed Columns in INSTEAD OF Triggers.

 

NOTE:
         This article is copied from MSDN for my personal learning.

Happy Learning !!!

Triggres in SQL Sever

           A trigger is single or multiple SQL statements that fires as an actionwhen event (like INSERT, DELETE,UPDATE, CREATE, ALTER, DROP) occures. You can devide tringgers in to two parts. DDL Triggers and DML Triggers.

DDL (Data Defenition Language)  Triggers

            DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. 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. They can be used to perform administrative tasks in the database 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 ;
Fig - (1) Example of DDL Trigger

DML (Data Manipulation Language) 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 Trigger

                   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 Trigger


                    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. For more information, see Programming CLR Triggers.  

DML Trigger Execution

            AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.

             INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.

             If an INSTEAD OF trigger defined on a table executes a statement against the table that would usually fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a DML trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

             If an INSTEAD OF trigger defined on a view executes a statement against the view that would usually fire the INSTEAD OF trigger again, it is not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all of the restrictions for an updatable view. For a definition of updatable views, see Modifying Data Through a View. For example, if a DML trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.

             DML trigger performance overhead is usually low. The time involved in running a DML trigger is spent mostly in referencing other tables, which can be either in memory or on the database device. The deleted and inserted tables are always in memory. The location of other tables referenced by the trigger determines the amount of time the operation requires.

DML Trigger Execution and Partitioned Views

             In SQL Server 2005, the way UPDATE and DELETE triggers fire on tables that underlie a partitioned view has changed from the way they fire in SQL Server 2000. In SQL Server 2000, when an UPDATE or DELETE statement is issued against a partitioned view, either local or distributed, any UPDATE or DELETE triggers that are defined on the base tables of the view fire. These include triggers on tables that are not affected by the update or delete operation. In SQL Server 2005, an UPDATE or DELETE trigger fires only if the base table on which the trigger is defined is affected by the update or delete operation. This behavior is the same for both AFTER and INSTEAD OF triggers.


            For example, consider a partitioned view PV that consists of fields from tables T1 and T2. Additionally, both T1 and T2 have UPDATE triggers defined on them. In SQL Server 2000, an UPDATE statement issued against PV that only affects rows in T1 causes the UPDATE triggers on both T1 and T2 to fire. In SQL Server 2005, only the UPDATE trigger that is defined on T1 fires.


CREATE TRIGGER TriggerName ON TableName
(FOR / AFTER)     INSERT / DELETE / UPDATE
AS
BEGIN
END


Fig - (2) Syntax for DML Trigger


NOTE:
            There is no BEFORE trigger in SQL Server.


 


Understanding DDL Triggers vs. DML Triggers


DDL triggers and DML triggers are used for different purposes.

DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views.

              DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.

               DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior.

               Like DML triggers, DDL triggers can run managed code packaged in an assembly that was created in the Microsoft .NET Framework and uploaded in SQL Server. For more information, see Programming CLR Triggers.

               Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement. Also, a DDL trigger and the statement that fires it are run within the same transaction. This transaction can be rolled back from within the trigger. Serious errors can cause a whole transaction to be automatically rolled back. DDL triggers that are run from a batch and explicitly include the ROLLBACK TRANSACTION statement will cancel the whole batch. For more information, see Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION.

               Like DML triggers, DDL triggers can be nested. For more information, see Using Nested Triggers.

               When you are designing DDL triggers, consider how they differ from DML triggers in the following ways:


  • DDL triggers run only after a Transact-SQL statement is completed. DDL triggers cannot be used as INSTEAD OF triggers.
  • DDL triggers do not create the inserted and deleted tables. The information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, is captured by using the EVENTDATA function. For more information, see Using the EVENTDATA Function.

 


Important:
                     This article is copied from different MSDN links. I have just gethered all the information at single place.


Happy Learning !!!