A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.

The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached:

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001  9:56AM

In some situations, you may want create more than one trigger on table. By default, all triggers execute at the same time and if there was a dependency between your triggers, you’ll get an exception. In this situation you have to execute triggers one by one and in an order.

In SQL Server 2005, Microsoft introduced a system stored procedure name “SP_SETTRIGGERORDER” that do whatever you want.

Note: In “SP_SETTRIGGERODER”, you can set the first and last trigger to execute; so if you have more than tree triggers in a table, you won’t be able to set all triggers’ orders.

The structure of “SP_SETTRIGGERODER” system stored procedure is like this:

USE AdventureWorks;
GO
sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader',
 @order='First', @stmttype = 'UPDATE';

Note: You CANNOT use SP_SETTRIGGERODER for INSTEAD OF triggers.

Hope it helps.

Most of developers who’ve worked with SQL Server 2005 or earlier know that it’s normal to create a table a then edit it. But in SQL Server 2008, it won’t let you to edit a table that has some connections with other objects in SQL Server such as foreign key constrains. There are some reasons that Microsoft put these kinds of rules in the latest version of SQL Server.

Anyway, most developers I know cannot stand this restriction and all of them told me that why Microsoft put such a thing and is there any solution to disable it?

The answer is yes. In this post I want to tell you how you can disable this restriction so you can create and edit your SQL Server objects just like before:

In the normal case you will see the following error when you want to change a table:

In order to solve this problem you need to go the Tools -> Options. In the left side of the screen, choose Designer and the following windows will be displayed:

To disable the restriction, uncheck the “Prevent saving changes that require table re-creation” checkbox and then save changes by clicking OK.

Now you can just edit your objects in SQL Server as same as previous versions of SQL Server.

Validating data is one of the most important sections in data-driven applications. It's a critical task to control and validate data that are inserting or updating. Most of developers are using constrains (Default, Check, Unique) in order to do so; most of them are using Check constrains in order to validate fields.
Sometimes, a developer needs to use a validation frequently. For example, imaging you're going to validate email address in SQL Sever. In order to do so, you'll need to write a complex regular expression as a check constraint. But there is one better way; you can use Rules to store a validation expression and then use it where ever you want.

Creating a rule is simple:

CREATE RULE EmailValidator
AS
begin

@value like '%@%.[a-z][a-z].[a-z][a-z]';

end

Once you create a rule, you can use frequently. In order to do so, you need to make use of sp_bindrule system stored-procedure.

The structure of sp_bindrule property is something like this:

sp_bindrule [ @rulename = ] 'rule' , 
[ @objname = ] 'object_name' 
[ , [ @futureonly = ] 'futureonly_flag' ]

And here’s an example. Note that today is the name of our rule:

USE master;
GO
EXEC sp_bindrule 'today', 'HumanResources.Employee.HireDate'

For more information about sp_bindrule you can out its MSDN article: http://msdn.microsoft.com/en-us/library/ms176063.aspx

During my Pocket PC project development, I needed to convert Gregorian date to Persian date. It’s easy to do such a thing in Windows or Web applications using System.Globalization namespace (PersianCalendar class) but this feature is not available when developing a Pocket PC application. I’ve tried a lot of things such as web services, windows services, etc but none of them is as good as converting it in your database and fortunately my database is Microsoft SQL Server 2005. So I decided to use CLR integration and create an UDF (User-Defined Function) in order to do so. In this post I wanted to show you how you can use CLR to create a function in .NET environment and execute it in SQL Server 2005-2008 environments.

The first thing you need is to create a SQL Server project which is available in Database node in new Project window:

Note that I used .NET Framework 2 because I wanted to create a CLR function in SQL Server 2005. If you’re using SQL Server 2008, there’s no problem to use .NET Framework 3.5 (SP1).

After you create a SQL Server project you can add any object that is common in SQL Server to your project by right click on the project and point to add; so you add a UDF, SP, etc:

As I mentioned before, we want to create an application which converts DateTime to another culture so I need to create a UDF. I select User-Defined Function and name it “PersianDateConvertor”. As you can see, Visual Studio will create a class named UserDefinedFunctions and add a method with the name you just specified:

using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{    
[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString PersianDateConvertor()    
{    
}
};

Now you need to convert a DateTime to a Persian DateTime like before using System.Globalization.PersianCalendar class. So our class will be something like this:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString PersianDateConvertor(DateTime dt)
{
    PersianCalendar p = new PersianCalendar();
    return String.Format("{0}/{1}/{2}",
         p.GetYear(dt).ToString(),
        p.GetMonth(dt).ToString(),
        p.GetDayOfMonth(dt).ToString());
}

By the way, As you can see our method is a static method and it returns a SqlString. You can change the return type if you want.

You’ve created you CLR library! Now it’s time to use it in Microsoft SQL Server. By default, you cannot use CLR libraries in SQL Server unless you enable it. In order to enable CLR integration, you need to make use of sp_configure system stored-procedure:

EXECUTE sp_configure 'clr enabled' , '1'

Note: In order to enable CLR, you need administrative privileges.

In order to make use of an external assembly in SQL Server, you have to create an assembly like following:

CREATE ASSEMBLY DateConvertorFROM 'D:\MMR.CRL.DateConvertor.dll'WITH PERMISSION_SET = SAFE;GO

After you’ve created your assembly, you need to use it in an UDF, SP, etc:

CREATE FUNCTION dbo.GetPersianDate(@date datetime)
RETURNS nvarchar(max)AS EXTERNAL NAME DateConvertor.UserDefinedFunctions.PersianDateConvertor;
GO

You’re DONE! now you can use your UDF and the result will be something like this:

SELECT dbo.GetPersianDate(GetDate())

Result:
-------------------------------------------------------------
1387/12/2
(1 row(s) affected)

A while ago, when I was working in a company with my friend Omid Mafakher, he developed a table function which works just link split function in other languages such as VB.NET, C#, C++, etc.

After I get permission from Omid, I decided to publish it as a blog post. Here's the function:

-- This stored procedure splits a string value with the specified delimiter.
-- Copyright 2007 Omid Mafakher.
Create Function [dbo].[Split] (@String nvarchar(MAX), @Delimiter char(1))
Returns @Results Table (Items nvarchar(MAX))
As
Begin
    Declare @Index int
    Declare @Slice nvarchar(4000) 

    Set @Index = 1
    If @String Is NULL Return 

    While @Index != 0 Begin
        Select @Index = CharIndex(@Delimiter, @String)
        If (@Index != 0) Begin
            Select @Slice = left(@String, @Index - 1)
        End else Begin
            Select @Slice = @String
        End
        Insert into @Results(Items) Values (@Slice)
        Select @String = right(@String, Len(@String) - @Index) 

        If Len(@String) = 0 break
    End
    Return
End

SplitFunction.sql (1.38 kb)