A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when a change occurs on a database table such as a record is inserted or updated or deleted. For example, a trigger can be set on a record insert in a database table. For example, if you want to increase the count of blogs in the Reports table when a new record is inserted in the Blogs table, we can create a trigger on the Blogs' table on INSERT and update the Reports table by increasing blog count to 1.

Types of Triggers
There are two types of triggers:
    DDL Trigger
    DML Trigger

DDL Triggers
The DDL triggers are fired in response to DDL (Data Definition Language) command events that start with Create, Alter and Drop, such as Create_table, Create_view, drop_table, Drop_view and Alter_table.

Code of a DDL Trigger
create trigger saftey
on database
for
create_table,alter_table,drop_table
as
print'you can not create ,drop and alter table in this database'
rollback;

When we create, alter or drop any table in a database then the following message appears:

DML Triggers
The DML triggers are fired in response to DML (Data Manipulation Language) command events that start with Insert, Update, and Delete. Like insert_table, Update_view and Delete_table.

create trigger deep
on emp
for
insert,update,delete
as
print'you can not insert,update and delete this table i'
rollback;

When we insert, update or delete in a table in a database then the following message appears,

There are two types of DML triggers
 
AFTER Triggers
AFTER triggers are executed after the action of an INSERT, UPDATE, or DELETE statement.

create trigger insertt
on emp
after insert
as
begin
insert into empstatus values('active')
end

INSTEAD Of Triggers
It will tell the database engine to execute the trigger instead of executing the statement. For example an insert trigger executes when an event occurs instead of the statement that would insert the values in the table .
CREATE TRIGGER instoftr
ON v11
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO emp
SELECT I.id, I.names
FROM INSERTED I

INSERT INTO emp1values
SELECT I.id1, I.name1
FROM INSERTED I
END

When we insert data into a view by the following query then it inserts values in both tables :
insert into v11 values(1,'d','dd')

You can see both tables by the folowing query:
select * from emp
select * from emp1values

In this article, I described triggers in SQL Server. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.

Continue learning more, Trigger in SQL

This article talk about what SQL injection is, how can that effect the security of our websites and what steps should be taken to create an ASP.NET application SQL injection proof. SQL injection is the attack in which the user of the website will input some SQL code as input which would result in creating a SQL statement that developers didn't intend to write. These SQL statements could result in unauthorized access, revealing secret user information and sometimes it could even wipe out the entire data lying on the server.

Getting to know SQL Injection

Let us take this discussion a little further by looking into the bad coding practices that will make the application prone to the SQL injection attacks. Let us create a simple table that contains username and password of the user for authentication.

Now I will create a small page that lets the user to enter his login credentials and get them validated against the Users table.

Note: Password should never be stored in plain text. This table contains password in plain text just for the sake of simplicity of this article.

The actual code that I will use to authenticate the user contains dynamic SQL that is being created by concatenating strings. This code will return true if the userid and password are found in the database otherwise false.  

public bool IsUserAuthenticated_Bad(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select userID from Users where userID = '" + username + "' and password = '" + password + "'";               

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return false;
}

For all the normal users this code will work fine. I can even test it using userid as sampleuser and password as samplepwd and this will work fine. For any other data except this it should say that authentication failed(since this is the only record in the table). The query that will get generated to test this input will be:

select userID from Users where userID = 'sampleuser' and password = 'samplepwd'

Now let us try to inject some SQL into this page. Let me give hacker' or 1=1-- as username and anything in the password(even leave it empty). Now the resultant SQL for this will become:

select userID from Users where userID = 'hacker' or 1=1--' and password = ''

Now when we execute this query the 1=1 clause will always return true(and the password check is commented out. Now irrespective of whatever data user has entered this will SQL return a row making this function return true and in turn authenticating the user. So What I have done now is that I gained access to the website even when I didn't knew the valid user credentials.

How can I curb this problem is something we will look into details in some time. But before that let us also look at one more example of SQL injection just to get little more understanding.

In this second example we will assume that the malicious user somehow got hold of the database schema and then he is trying to manipulate the application to find some confidential information. Lets say we have a page that is supposed to show all the products that are assigned to a user in the organization.

Let us start by looking at the Product table.

Let us now look at the code that is retrieving this data:

public DataTable GetProductsAssigner_Bad(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = '" + userID + "'";

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return result;
}

Now if I call this function with the proper data(as normal users would do) then this will show me the results. i.e. If I call this page for sampleuser the resulting query would be:

select * from Products where AssignedTo = 'sampleuser'

Now let me use this query string with this page: userID=' UNION SELECT 0 AS Expr1, password, userID FROM Users -- . Once this data is used with the current code this will show me all the username and passwords from the database. The reason will be quiet clear once we look into the resulting query of this input.

select * from Products where AssignedTo = '' UNION SELECT 0 AS Expr1, password, userID FROM Users --

Now we saw that how string concatenated dynamic SQL is prone to SQL injection. There are many other problems that could be created by injecting SQL. Imagine a scenario where the injected SQL is dropping tables or truncating all the tables. The problem in such cases would be catastrophic.

How to Prevent SQL Injection

ASP.NET provides us beautiful mechanism for prevention against the SQL injection. There are some thumb rules that should be followed in order to prevent injection attacks on our websites.

• User input should never be trusted. It should always be validated
• Dynamic SQL should never be created using string concatenations.
• Always prefer using Stored Procedures. 
• If dynamic SQL is needed it should be used with parametrized commands.
• All sensitive and confidential information should be stored in encrypted.
• The application should never use/access the DB with Administrator privileges. 

User input should never be trusted. It should always be validated

The basic thumb rule here is that the user input should never be trusted. First of all we should apply filters on all the input fields. If any field is supposed to take numbers then we should never accept alphabets in that. Secondly, All the inputs should be validated against a regular expression so that no SQL characters and SQL command keywords are passed to the database.

Both this filtration and validation should be done at client side using JavaScript. It would suffice for the normal user. Malicious users cans till bypass the client side validations. So to curb that all the validations should be done at server side too.

Dynamic SQL should never be created using string concatenations.

If we have dynamic SQL being created using string concatenations then we are always at the risk of getting some SQL that we are not supposed to use with the application. It is advisable to avoid the string concatenations altogether.

Always prefer using Stored Procedures.

Stored procedures are the best way of performing the DB operations. We can always be sure of that no bad SQL is being generated if we are using stored procedures. Let us create a Stored procedure for the database access required for our login page and see what is the right way of doing the database operation using stored procedure.

CREATE PROCEDURE dbo.CheckUser     
      (
      @userID varchar(20),
      @password varchar(16)
      )
AS
      select userID from Users where userID = @userID and password = @password
      RETURN

And now lets have a good version in our code using this stored procedure.

public bool IsUserAuthenticated_Good(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "CheckUser";
                cmd.Parameters.Add(new SqlParameter("@userID", username));
                cmd.Parameters.Add(new SqlParameter("@password", password));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return false;
}

If dynamic SQL is needed it should be used with parametrized commands.

If we still find our self needing the dynamic SQL in code then parametrized commands are the best way of performing such dynamic SQL business. This way we can always be sure of that no bad SQL is being generated. Let us create a parametrized command for the database access required for our Product page and see what is the right way of doing the database operation.

public DataTable GetProductsAssigner_Good(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = @userID";
                cmd.Parameters.Add(new SqlParameter("@userID", userID));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return result;
}

All sensitive and confidential information should be stored in encrypted.

All the sensitive information should be stored encrypted in the database. The benefit of having this is that even if somehow the user get hold of the data he will only be able to see the encrypted values which are not easy to use for someone who doesn't know the encryption technique used by the application.

The application should never use/access the DB with Administrator privileges.

This will make sure that even if the bad SQL is being passed to the Database by some injections, the database will not allow any catastrophic actions like dropping table.

Note: Refer the sample application attached to see the working examples SQL injection and how to curb them using parametrized commands and stored procedures.

In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below

And this is Data into table

I want output in following format

                                  Department                               FirstName
                                   IT                                             amiT,Emp1,Emp5
                                  Admin                                       Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

1SELECT DISTINCT
2Department,
3EmpNames = substring( ( SELECT ', ' + FirstName
4FROM Employees e2
5WHERE e2.Department = e1.Department FOR XML path(''), elements
6),2,500)
7FROM Employees e1

And the output of this SQL Query would be

Here are some important points to consider while restoring a database backup. Let us first create a sample database named test using the following code.

A new database named test will be created in your server and data and log files are created in the d: drive.

Let us assume that you regularly take backup of this database using the below code:

backup database test to disk='d:\test.bak'

Now if you want to create another database or restore this test database to a new database named testing, you can use the following code

restore database testing from disk='d:\test.bak'

However executing the code above will give you the following error

Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_dat.mdf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_dat' cannot be restored to 'h:\test_dat.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 2
The file 'h:\test_log.ldf' cannot be overwritten.  It is being used by database 'test'.
Msg 3156, Level 16, State 4, Line 2
File 'test_log' cannot be restored to 'h:\test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This error occurs because the data and log files are currently being used by the database test. So you need to give different names for those files while restoring, as shown below

restore database testing from disk='d:\test.bak'
with
move 'test_dat' to 'd:\testing.mdf',
move 'test_log' to 'd:\testing.ldf'

The above code will work fine and new database will be created with the name testing.

Just be aware of this point while restoring a backup of existing database!

In this article, we make a database in Microsoft Access and that database is used in SQL Server. 

So let's take a look at a practical example of how to import a Microsoft Access database table into SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple steps to do that as described here.

Creating a Empty Database in SQL Server

Now Press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Database -> right-click-> select New database

This would open the "New database" window:

Now enter a database name to create a database.

Now click on the OK button to create the database. The new database will be shown in the Object Explorer:

Creating a Table in Access Database

Now open the Microsoft Access database and create a database.  The Access database looks as in the following:

Importing Access database in SQL Server

Now right-click on the Test database and select the Import Data option.

database Name -> Tasks -> Import data

Now click on Import data. The import and export wizard will be opened.

Now select a Data Source. And browse the file from the system.

Now click on the Next Button and enter the computer name or server name and select a database.

Server Name: MCNDESKTOP08\MYMSSQLSERVER

Database name: Test

Now click on the Next Button.

Now click on the Next Button.

Now again click on the Next Button.

Now click on the Finish Button.

And finally click on the Close button.

All of your Access data is nicely imported into a SQL Server 2008 database. Now refresh the test database and expand it to see the imported table.