Friday, June 22, 2012

update user information list

SPWeb web = properties.OpenWeb();
           web.AllowUnsafeUpdates = true;

           SPList list = web.Lists["Távollétek"];
           SPListItem item = properties.ListItem;

           SPSite site = new SPSite("http://sp2010/sites/demo");
           SPWeb kezdo = site.OpenWeb();
           SPList felhasznalok = kezdo.Lists["User Information list"];
           
           
           string userReszleg = string.Empty;
           string nev = item["Name"].ToString();
           foreach (SPListItem felList in felhasznalok.Items)
           {
               if (nev == felList["Name"].ToString())
               {
                   userReszleg = felList["Department"].ToString();
               }
           }
           item["Department"] = userReszleg.ToString();
           item.Update();
           this.EventFiringEnabled = true;
 
 

SharePoint User Profiles - Internal Names

Two types of User Profiles:
1. WSS Profile - can be accessed from Welcome User > My Settings page (_layouts/userdisp.aspx?.......)


User Information List
Display Name
Internal Name
AccountName
First nameFirstName
Last nameLastName
NameTitle
Work phoneWorkPhone
OfficeOffice
DepartmentDepartment
TitleJobTitle
Work e-mailEMail
User nameUserName


2. MOSS profile - can be accessed from My Site (../Person.aspx?....). The MOSS profiles are created when users are imported into the system from AD or LDAP.


SSP User Profile
Display Name
Internal Name
Account nameAccountName
First nameFirstName
Last nameLastName
NamePreferredName
Work phoneWorkPhone
OfficeOffice
DepartmentDepartment
TitleTitle
Work e-mailWorkEmail


Basically, there are two MOSS Timer jobs that control the replication of the user profiles per web application. These jobs can be found from Central Administration -> Operations -> Timer Job definitions.

1. Profile Synchronization - updates the WSS profiles.
2. Quick Profile Synchronization - updates the MOSS profiles.
 

get all entity and user profile

To those doing software development for a Sharepoint environment, user profiles can be a mysterious and confusing area. This post will detail some of the basics, and outline some snafoos you may run into if you find yourself doing software development that interacts with the Sharepoint user profile information.
First, it should be explained that the user profile setup differs depending on whether you are operating in a full MOSS environment, or simply using the basic WSS structure. In the full MOSS environment, user profile information is stored in a central database, and profile information is shared across site collections that may be hosted on your server. This profile info can be read and/or edited via the UserProfileManager class within the Microsoft.Office.Server dll.
Read a little deeper about Profiles administration in more in one of my previous posts
http://panvega.wordpress.com/2008/11/05/mapping-user-properties-in-the-sp-central-administration

As mentioned above, this profile information is held in a global profile database, and persists across different site collections on your server. On the other hand, if you are operating in only a WSS environment, the user profile information is stored directly in a hidden list specific to an individual site collection, which is called the User Information List. If multiple site collections exist, the server will store separate user profiles for each one. These profiles can be access via the following:
using(Microsoft.Sharepoint.SPSite site = new Microsoft.Sharepoint.SPSite("http://url")){

    Microsoft.Sharepoint.SPWeb web = site.RootWeb;

    Microsoft.SharePoint.SPList userList = web.Lists["User Information List"];

    foreach (Microsoft.SharePoint.SPListItem user in userList.Items)

    {

        Console.WriteLine(user["ID"] + ": " + user["Name"]);

    }

}
Before you begin screaming that this second method does not make use of the SPUser class, you should note that the SPUser class provides access to fewer properties than those that are available by accessing this user list directly. However, for sake of completeness (and for those of you who shy away from tinkering under the hood), a list of all SPUser objects associated with the site collection can be obtained via the following:
using(Microsoft.Sharepoint.SPSite site = new Microsoft.SharePoint.SPSite("http://url")){

    Microsoft.SharePoint.SPWeb web = site.RootWeb;

    foreach(Microsoft.SharePoint.SPUser user in web.Users){

        System.Console.WriteLine(user.ID + ": " + user.Name);

    }

}
As I stated above, the profile properties available from the SPUser class are fewer than those available from the User Information List. The SPUser class allows you to access/change the following profile properties (e.g. those that show up in the My Settings page):
  • ID
  • Email
  • Name
  • LoginName
  • Notes
  • DisplayName
  • PrincipalType…
By comparison, the User Information List provides access to the following profile properties (plus many more that I’m not listing here):
  • Title
  • Name
  • EMail
  • Notes
  • SipAddress
  • Locale
  • IsSiteAdmin
  • Picture
  • Department
  • JobTitle
  • IsActive
  • FirstName
  • LastName
  • WorkPhone
  • UserName
  • WebSite
  • Office
  • ID
  • Modified
  • Created
  • Author
  • Editor
http://msdn.microsoft.com/en-us/library/microsoft.office.server.userprofiles.propertyconstants_members.aspx
As you can see, these different methods of accessing user profile information can be a bit confusing. A good first step in tackling this problem is to look at the default layout of your website. If you have a link for My Site at the top, near your name, then you are in a full MOSS environment, and should access user profiles via the first method described. If this link does not exist, you may be only using WSS profiles, in which case either of the other methods will be your preferred mode of access.
CodeSnippet, Accessing the WSS Profile of the SP MySite for the PeopleEditor:
protected PeopleEditor editor;…..
ArrayList peEntities = editor.Entities;
// 2. cast object Entities in PickerEntity class
PickerEntity pickEn = (PickerEntity)peEntities[i];

if (pickEn.IsResolved)
{

Hashtable hstEntityData = pickEn.EntityData;
builder.AppendLine(pickEn.DisplayText);
builder.AppendLine(pickEn.Description);

builder.AppendLine(Convert.ToString(hstEntityData["DisplayName"]));
builder.AppendLine(Convert.ToString(hstEntityData["Email"]));
builder.AppendLine(Convert.ToString(hstEntityData["PrincipalType"]))

In this Code above you can only access the basic properties.
How to retreive all Profile Properties with the SPUserProfileManager and store them in a drop down list:
;
SPSite site = SPContext.Current.Site;
ServerContext context = ServerContext.GetContext(site);
UserProfileManager profileManager = new UserProfileManager(context);
PropertyCollection props = profileManager.Properties;
DropDownList ddl = new DropDownList();
foreach (Property prop in props)
{
ddl.Items.Add(prop.Name);
}

Important SP Objects:
  • SPUserInfo
  • SPUserProfileManager
  • PickerEntity
  • UserProfile
  • PeopleEditor
Here is an example how to access the MOSS UserProfile rather than the WSS profile.
for (int i = 0; i < peoplePicker.ResolvedEntities.Count; i++)
{
PickerEntity pickerEntity = (PickerEntity)peoplePicker.ResolvedEntities[i];
SPUserInfo userInfo = new SPUserInfo();
userInfo.LoginName = pickerEntity.Key;
if (isGroup(web, userInfo))
{
// Loop through all members of this group and add them individually
foreach (SPUser usr in web.Groups[userInfo.LoginName].Users)
{
// Add the User
if (profileManager.UserExists(usr.LoginName))
{
UserProfile user = profileManager.GetUserProfile(usr.LoginName);
givenName = user[PropertyConstants.FirstName];
lastName = user[PropertyConstants.LastName];
email = user[PropertyConstants.WorkEmail];
}
}
}
else
{
// pickerEntity relates to an individual user
// Add the User
if (profileManager.UserExists(userInfo.LoginName))
{
UserProfile user = profileManager.GetUserProfile(userInfo.LoginName);
givenName = user[PropertyConstants.FirstName];
lastName = user[PropertyConstants.LastName];
email = user[PropertyConstants.WorkEmail];
}
}
}


Note:
Actually you can retreive all Users and Groups in the entire Active Directory forest when using the PeoplePicker Addressbook. However when you use the profileManager.UserExists(usr.LoginName), the Profilemanager looks only into the MOSS UserProfile store. Otherwise you have to import all users from the entire forest or make a custom specific AD User import.
Or access the properties without the PropertyConstants:
UserProfile user = profileManager.GetUserProfile(userInfo.LoginName);
string givenName = user[FirstName].Value.toString();
More Information:
http://www.codeplex.com/MOSSProfileImport
http://blogs.technet.com/tothesharepoint/archive/2008/10/21/3139351.aspx

update user

Tuesday, June 19, 2012

xxs stopper

Code download available at: WickedCode0408.exe (118 KB)
Browse the Code Online
Let's face it: every minute of every day, someone, somewhere, is patrolling the Web looking for sites to hack. ASP.NET developers must constantly be on their guard to ensure attempted hacks can't be successful. That means constraining and validating user input, accessing databases securely, storing sensitive data securely, and generally writing secure code that repels rather than accommodates these malevolent hackers.
A classic form of hack attack that ASP.NET sites must defend against is session hijacking. Simply put, session hijacking entails connecting to a Web site and accessing someone else's session state. The severity of the damage incurred depends on what's stored in session state. If sessions hold shopping cart information and users are required to verify their identities before checking out, session hijacking might not be very damaging. If sessions contain credit card numbers or similarly sensitive data that can be presented back to the user, you really have to watch out.
Session hijacking attacks are typically perpetrated in one of two ways: session ID guessing and stolen session ID cookies. Session ID guessing involves gathering a sample of session IDs and "guessing" a valid session ID assigned to someone else. ASP.NET apps tend to be much less susceptible to this form of session hijacking because ASP.NET uses highly random 120-bit numbers for its session IDs. Unless you replace ASP.NET session IDs with IDs of your own (last summer I met one developer who had done just that and used sequential session IDs, which are an open door to hackers), you have nothing to fear from session ID guessing.
Stolen session cookies are another matter. Secure Sockets Layer (SSL) can be used to protect session ID cookies on the wire, but few sites restrict session ID cookies to encrypted connections. Even if you use SSL, session ID cookies can be stolen in other ways, notably through cross-site scripting attacks, man-in-the-middle attacks, and gaining physical access to the cookie stores on victims' PCs. Furthermore, executing a successful session hijacking attack with a stolen session ID cookie requires little skill on the part of the attacker. The reason is simple: ASP.NET encodes no information in a session ID cookie other than the session ID. If it receives a cookie containing a valid session ID, ASP.NET connects to the corresponding session, no questions asked.
It's virtually impossible to build a foolproof defense against attacks that rely on stolen session ID cookies, but you can take steps to make it harder. Some Web sites, for example, encode heuristic information such as IP addresses in their session IDs. That doesn't prevent session hijacking altogether—after all, some hackers possess the means to spoof IP addresses, and IP addresses are not a reliable means for identifying callers anyway—but it does raise the bar. Security is all about raising the bar. The harder you make it for a hacker to execute a successful attack, the less likely it is that successful attacks will occur.

Introducing SecureSessionModule
[ Editor's Update - 7/20/2004: Figure 1 has been updated so as not to provide too much information to an attacker.]
using System; 
using System.Web; 
using System.Web.Security; 
using System.Configuration; 
using System.Security.Cryptography; 
using System.Runtime.Serialization; 
using System.Globalization; 
using System.Text; 

public class SecureSessionModule : IHttpModule 
{ 
    private static string _ValidationKey = null; 

    public void Init (HttpApplication app) 
    { 
        // Initialize validation key if not already initialized 
        if (_ValidationKey == null) 
            _ValidationKey = GetValidationKey (); 

        // Register handlers for BeginRequest and EndRequest events 
        app.BeginRequest += new EventHandler (OnBeginRequest); 
        app.EndRequest += new EventHandler (OnEndRequest); 
    } 

    public void Dispose () {} 

    void OnBeginRequest (Object sender, EventArgs e) 
    { 
        // Look for an incoming cookie named "ASP.NET_SessionID" 
        HttpRequest request = ((HttpApplication) sender).Request; 
        HttpCookie cookie = GetCookie (request, "ASP.NET_SessionId"); 

        if (cookie != null) { 
            // Throw an exception if the cookie lacks a MAC 
            if (cookie.Value.Length <= 24) 
                throw new InvalidSessionException 
                    ("Access Denied"); // don't tell bad guys too much

            // Separate the session ID and the MAC 
            string id = cookie.Value.Substring (0, 24); 
            string mac1 = cookie.Value.Substring (24); 

            // Generate a new MAC from the session ID and requestor info 
            string mac2 = GetSessionIDMac (id, request.UserHostAddress, 
                request.UserAgent, _ValidationKey); 

            // Throw an exception if the MACs don't match 
            if (String.CompareOrdinal (mac1, mac2) != 0) 
                throw new InvalidSessionException 
                    ("Access Denied"); // don't tell bad guys too much

            // Strip the MAC from the cookie before ASP.NET sees it 
            cookie.Value = id; 
        } 
    } 

    void OnEndRequest (Object sender, EventArgs e) 
    { 
        // Look for an outgoing cookie named "ASP.NET_SessionID" 
        HttpRequest request = ((HttpApplication) sender).Request; 
        HttpCookie cookie = GetCookie (
            ((HttpApplication) sender).Response, "ASP.NET_SessionId"); 

        if (cookie != null) { 
            // Add a MAC 
            cookie.Value += GetSessionIDMac (cookie.Value, 
                request.UserHostAddress, request.UserAgent, 
                _ValidationKey); 
        } 
    } 

    private string GetValidationKey () 
    { 
        string key = ConfigurationSettings.AppSettings 
            ["SessionValidationKey"]; 
        if (key == null || key == String.Empty) 
            throw new InvalidSessionException 
                ("SessionValidationKey missing"); 
        return key; 
    } 

    private HttpCookie GetCookie (HttpRequest request, string name) 
    { 
        HttpCookieCollection cookies = request.Cookies; 
        return FindCookie (cookies, name); 
    } 

    private HttpCookie GetCookie (HttpResponse response, string name) 
    { 
        HttpCookieCollection cookies = response.Cookies; 
        return FindCookie (cookies, name); 
    } 

    private HttpCookie FindCookie (HttpCookieCollection cookies, 
        string name) 
    { 
        int count = cookies.Count; 

        for (int i=0; i<count; i++) { 
            if (String.Compare (cookies[i].Name, name, true, 
                CultureInfo.InvariantCulture) == 0) 
                return cookies[i]; 
        } 

        return null; 
    } 

    private string GetSessionIDMac (string id, string ip, 
        string agent, string key) 
    { 
        StringBuilder builder = new StringBuilder (id, 512); 
        builder.Append (ip.Substring (0, ip.IndexOf ('.', 
            ip.IndexOf ('.') + 1))); 
        builder.Append (agent); 

        using (HMACSHA1 hmac = new HMACSHA1 
            (Encoding.UTF8.GetBytes (key))) { 
            return Convert.ToBase64String (hmac.ComputeHash 
               (Encoding.UTF8.GetBytes (builder.ToString ()))); 
        } 
    } 
} 

[Serializable] 
public class InvalidSessionException : Exception 
{ 
    public InvalidSessionException () : 
        base ("Session cookie is invalid") {} 

    public InvalidSessionException (string message) : 
        base (message) {} 

    public InvalidSessionException (string message, 
        Exception inner) : base (message, inner) {} 

    protected InvalidSessionException (SerializationInfo info, 
        StreamingContext context) : base (info, context) {} 
}
Figure 2 shows how SecureSessionModule works. First, it checks every outgoing response for a session ID cookie issued by ASP.NET's SessionStateModule. When it sees such a cookie, SecureSessionModule modifies it by appending a hashed message authentication code (MAC) to the session ID. The MAC is generated from the session ID, the network address portion of the requestor's IP address (for example, the 192.16 in 192.16.0.14), the User-Agent header received in the request, and a secret key stored on the server. The Framework's System.Security.Cryptography.HMACSHA1 class makes the task of generating the MAC really quite easy.Why use the network address instead of the full IP address? The node address of users that access the Internet through public proxy servers such as AOL's can change in every request, but the network address should not.
Figure 2 SecureSessionModule 
Second, SecureSessionModule examines every incoming request for an ASP.NET session ID cookie. Before allowing a request containing a session ID cookie to continue through the pipeline, SecureSessionModule validates the cookie by regenerating the MAC from the requestor's IP address, the User-Agent header, and the secret key. If the freshly computed MAC matches the one in the cookie, the MAC is stripped from the cookie and the request is allowed to proceed. If the MACs don't match, SecureSessionModule throws an InvalidSessionException, as shown in Figure 3.

The net result is that once a session ID cookie is issued, it's only considered valid if it's submitted from the same network address and with the same User-Agent header. An attacker who steals a session ID cookie can only use it if she can spoof IP addresses and HTTP headers. Both are certainly possible, but spoofing of this sort requires a higher skill level on the part of the attacker. In addition, getting a response back from a request with a spoofed IP address is much harder than simply submitting the request in the first place and can be defeated with proper egress filtering. It's impossible for the attacker to simply replace the hash in the cookie with one generated from her own IP address and User-Agent header without the secret key used to generate the MAC. To the extent that the key can be secured, casual hackers will find it difficult indeed to use stolen session IDs for nefarious purposes.

Deploying SecureSessionModule
Deploying SecureSessionModule is as simple as copying SecureSessionModule.dll into the application root's bin subdirectory and registering it in Web.config, like so:
<configuration>
  <appSettings>
    <add key="SessionValidationKey"
      value="DAD4D476F80E0148BCD134D7AA5C61D7" />
  </appSettings>
  <system.web>
    <httpModules>
      <add name="SecureSession"
        type="SecureSessionModule, 
          SecureSessionModule" />
    </httpModules>
  </system.web>
</configuration>
The SessionValidationKey value in the <appSettings> section of Web.config is required. This is the "secret key" used to generate the MAC; SecureSessionModule looks for it at load time and throws an exception if it's not there. The key should be unique for every application, and it should be long and random. I used a simple tool built around the .NET Framework RNGCryptoServiceProvider class to generate the one in Figure 3. You should use a similar tool to maximize randomness. In addition, if deployed on a Web farm, SessionValidationKey should be the same on every server. Of course, storing plaintext security keys in configuration files poses risks of its own. For an added measure of security, consider encrypting the secret key. The article at Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication describes how to use the Windows® Data Protection API (DPAPI) from ASP.NET. DPAPI is ideal for encrypting secrets in configuration files because it offloads the problem of key management—specifically, storing decryption keys—to the operating system itself.
Ideally, SecureSessionModule would use the same secret key that ASP.NET uses for hashing: the value of the validationKey attribute in Machine.config's <machineKey> element. However, there is no public API for retrieving this key, and after inspecting what the Framework does to retrieve validationKey, I elected not to duplicate the logic in the Framework to avoid potential incompatibilities with future versions of .NET. The new configuration API coming in version 2.0 of the .NET Framework will correct this omission and provide a documented means for reading the ASP.NET validation key.
Once deployed, SecureSessionModule works passively—no intervention is required. If you'd like, include an Application_Error method in Global.asax to log InvalidSessionExceptions in the Windows event log or elsewhere. That way you can check the log every morning over your first cup of coffee and find out if someone has been trying to spoof your server with stolen session IDs. Such a method might look like this:
<%@ Import Namespace="System.Diagnostics" %>

<script language="C#" runat="server">
void Application_Error(Object sender, EventArgs e)
{
    // Write an entry to the event log
    EventLog log = new EventLog ();
    log.Source = "My ASP.NET Application";
    log.WriteEntry (Server.GetLastError().ToString(),
        EventLogEntryType.Error);
}
</script>

Caveats
Before deploying SecureSessionModule on a production Web server, you should consider several potential issues.
First, SecureSessionModule is not 100 percent effective in detecting illicit session ID cookies. If the attacker has the same network address as the victim (if both, for example, use the same proxy server) or can spoof the victim's network address, then User-Agent headers are the last line of defense. And User-Agent headers are easily spoofed by someone aware that User-Agent headers are being used to validate session IDs.
Second, if for some reason a user's network address or User-Agent headers vary from request to request, that user will lose access to her session state. Third, SecureSessionModule doesn't work with cookieless session state. It assumes session IDs are passed in cookies, not URLs.
Finally, SecureSessionModule hasn't been tested in a production environment. If you use it, I'd love to hear from you—especially about any glitches that arise. Note that Microsoft has considered building similar protections into ASP.NET, but has always shied away from it for backward compatibility concerns.

Conclusion
Session hijacking remains a serious threat to security. SecureSessionModule raises the bar for hackers who hijack sessions using stolen session IDs by factoring evidence about the session owner into the session ID cookie. That evidence isn't conclusive because neither network addresses nor User-Agent headers can be used reliably to distinguish one user from another, but it nonetheless places an additional hurdle in the path of hackers who are actively seeking to compromise your Web servers by connecting to other users' sessions and grabbing their data.

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

best practice for trigger

Table of Contents

Overview

Implementing business rules as well as performing validation or data modifications, triggers are the best use for this purpose when other methods are not sufficient. Triggers are normally used in two areas: creating audit records and reflecting changes to crucial business tables, and validating changes against a set of business rules coded in T-SQL.
In this article, I would like to demonstrate how to create triggers, use of triggers, different types of triggers and performance considerations.

What is a Trigger?

A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBAs (Data Base Administrators) to create additional relationships between separate databases. In other ways, a trigger can be defined to execute before or after an INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.
Triggers can be assigned to tables or views. However, although there are two types of triggers, INSTEAD OF and AFTER, only one type of trigger can be assigned to views. An INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. An AFTER trigger fires after a modification action has occurred.
From a performance viewpoint, the fewer the triggers, the better, as we will invoke less processes. Therefore, do not think that having one trigger per action to make things modular will not incur performance degradation. A trigger's main overhead is referencing either two specialist tables that exist in triggers – deleted and inserted or other tables for business rules. Modularizing triggers to make the whole process easier to understand will incur multiple references to these tables, and hence a greater overhead.
Note: It is not possible to create a trigger to fire when a data modification occurs in two or more tables. A trigger can be associated only with a single table.

Why Use Triggers?

To improve data integrity, trigger can be used. When an action is performed on data, it is possible to check if the manipulation of the data concurs with the underlying business rules, and thus avoids erroneous entries in a table. For example:
  • We might want to ship a free item to a client with the order, if it totals more than $1000. A trigger will be built to check the order total upon completion of the order, to see if an extra order line needs to be inserted.
  • In a banking scenario, when a request is made to withdraw cash from a cash point, the stored procedure will create a record on the client's statement table for the withdrawal, and the trigger will automatically reduce the balance as required. The trigger may also be the point at which a check is made on the client's balance to verify that there is enough balance to allow the withdrawal. By having a trigger on the statement table, we are secure in the knowledge that any statement entry made, whether withdrawal or deposit, will be validated and processed in one central place.
Note: We discuss only data integrity here, and not referential integrity.
Another use of triggers can be to carry out an action when a specific criterion has been met. One example of this is a case where an e-mail requesting more items to be delivered is sent, or an order for processing could be placed, when stock levels reach a preset level. However, if we insert data into another table from within a trigger, we have to be careful that the table we insert into doesn't have a trigger that will cause this first trigger to fire. It is possible to code triggers that result in an endless loop, as we can define a trigger on TableA, which inserts into TableB, and a trigger for TableB, which updates TableA. This scenario will ultimately end in an error being generated by the SQL Server. The following diagram will demonstrate this:
Diagram
Figure 1
  1. A stored procedure, A, updates TableA.
  2. This fires a trigger from TableA.
  3. The defined trigger on TableA updates TableB.
  4. TableB has a trigger which fires.
  5. This trigger from TableB updates TableA.

Creating and Using a Trigger  

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. More details can be found at this link.

CREATE TRIGGER Statement

The CREATE TRIGGER statement defines a trigger in the database.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:
  • ALTER privilege on the table on which the BEFORE or AFTER trigger is defined
  • CONTROL privilege on the view on which the INSTEAD OF TRIGGER is defined
  • Definer of the view on which the INSTEAD OF trigger is defined
  • ALTERIN privilege on the schema of the table or view on which the trigger is defined
  • SYSADM or DBADM authority and one of:
    • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the trigger does not exist
    • CREATEIN privilege on the schema, if the schema name of the trigger refers to an existing schema
If a trigger definer can only create the trigger because the definer has SYSADM authority, the definer is granted explicit DBADM authority for the purpose of creating the trigger.

Syntax of a Trigger

CREATE TRIGGER name ON table
   [WITH ENCRYPTION]
   [FOR/AFTER/INSTEAD OF]
   [INSERT, UPDATE, DELETE]
   [NOT FOR REPLICATION]
AS
BEGIN
--SQL statements
...
END     

Sample Example  

Let’s take an example. We have a table with some columns. Our goal is to create a TRIGGER which will be fired on every modification of data in each column and track the number of modifications of that column. The sample example is given below:
-- ==========================================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description: Alter count for any modification  
-- ===========================================================
CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample] 
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @TransID  VARCHAR(36)
 SELECT @TransID = TransactionID FROM INSERTED
 UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + 1 
          ,LastUpdate = GETDATE()
    WHERE TransactionID = @TransID
END
SQL Table
Figure 2 (SQL table)

Types of Triggers

There are three main types of triggers that fire on INSERT, DELETE, or UPDATE actions. Like stored procedures, these can also be encrypted for extra security. More details can be found at this link.

Good Practice

The most important point is to keep the trigger as short as possible to execute quickly, just like stored procedures. The longer a trigger takes to fire, the longer the locks will be held on the underlying tables. To this end, we could place cursors within a trigger, but good practice dictates that we don't. Cursors are not the fastest of objects within a database, and we should try and revisit the problem with a different solution, if we feel the need for cursors. One way around the problem may be to run two, or perhaps, three updates, or even use a temporary table instead.
Use triggers to enforce business rules, or to complete actions that have either a positive effect on the organization, or if an action will stop problems with the system. An example of this is creation of a trigger that will e-mail a client when an order is about to be shipped, giving details of the order, and so on.
Note: Use an @@ROWCOUNT, where required, to check the number of rows that have been affected.

Saturday, June 16, 2012

best practice for stored procedure

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET
NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This helps in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method
  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET
@Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)
If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.
  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH
 
  • Disable parameter sniffing: Use local variable instead of parameter directly.