Trigger in SQL Server

Trigger is precompiled set of statement which is executed on some sort of action like Insert data, update data or delete data. Trigger is executed automatically. You don’t worry to execute it manually.

You have to specify the database table name and action (insert, update and delete) while creating the trigger. You can create two types of trigger: after and instead of.

If you specify insert trigger after then that trigger will execute after inserting the data into database table. Instead of trigger will execute instead of executing insert statement.

Trigger is mainly used to do some post-action operation. For example, you want to log every action in database then you can create trigger which will log the information into log table while inserting, updating or deleting the records.

Let’s take an example, whenever any new record is inserted/updated/deleted to EmployeeMaster table then you want to log the information into LogMaster table. You can use below trigger:

 

CREATE TRIGGER trg_EMPLOYEEMASTER_INSERT

on EMPLOYEEMASTER

After Insert

As

begin

Declare @name varchar(1000)

Select @name = EmpName from inserted

Insert into LOGMaster (LogField1) values (@name)

end

This trigger will insert new record into LOGMaster table whenever any user will create new employee. I used one magic table “Inserted”. Inserted table is used to get the newly inserted record into database table. Here Inserted table schema is same as EmployeeMaster database table because we have created trigger on EmployeeMaster table.

Deleted magic table is used to get the record which is deleted from database table which invoke this trigger. This table is used into Delete and Update scenario.  In update scenario, you can get new record information from Inserted table and old record information from Deleted table.

Instead of Trigger:

Instead of trigger will be used to verify the data and do some pre-operation action and insert data from the trigger. Insert/Update/Delete option is not performed if you have created “instead of” trigger.

Before trigger is not available in SQL Server.

Remove duplicate rows in SQL Server

Sometime we face problem of duplicate records in SQL server database. In this scenario, we need to remove duplicate rows.

There are many ways available to remove duplicate rows from the database like

• Using temporary table – In this you have to transfer data into temporary table which have identity column so you can identify record uniquely and remove duplicate rows. But it gives more loads on SQL if you have lots of data in your table.
• Using adding Identity column into existing database

All above options gives very heavy load to SQL server. You can remove duplicate rows using following method:
You just have to create in-memory table which just create schema of your table and add new row number column. Using row number column, you can remove duplicate rows.

With [Temp_Table_Name] as
(
Select RN = row_number() over (PARTITION by col1, col2, col3 order by col1, col2, col3), * from [Table_Name]
)

delete from [Temp_Table_Name] where RN > 1

The above script add new column of row number in your table. This is just in-memory change. You can remove duplicate rows which actually remove from your table.

Here I used Row_Number function with “Order by” and “Partition by”.

Left Outer Join

Left outer join is used to retrieve all records from left side table and matched data from right side of table. If you included any column in select clause from right side of table then SQL server will display NULL value when right side of table does not have any matching record with left side table.

Syntax:

Select column1[, column2 …]

from Table1
     Left [outer] join Table2 on Table1.Column1 = Table2.Column4 [ and Table1.Column6 = Table2.ColumnN]

Let’s take an example to understand left outer join.

We have two tables Employee and Projects. Employee table have 10 employee records and Project table have 5 projects information.

Out of 10 employees, 2 employees are not allocated into any project. So when we execute the query, it will return 10 records.

In result pane, you can see 2 employee records which have NULL value for project information. (These two employees are not allocated into any project).

Query syntax:

Select *

from EmployeeMaster

      left join ProjectMaster on EmployeeMaster.ProjectId = ProjectMaster.ProjectId

Result:

EmployeeID	EmployeeName	ProjectID	ProjectID	ProjectName
1	Emp1	1	1	TestProject1
2	Emp2	1	1	TestProject1
3	Emp3	2	2	TestProject2
4	Emp4	NULL	NULL	NULL
5	Emp5	3	3	TestProject3
6	Emp6	3	3	TestProject3
7	Emp7	NULL	NULL	NULL
8	Emp8	4	4	TestProject4
9	Emp9	4	4	TestProject4
10	Emp10	4	4	TestProject4

Don’t confuse between “Left outer join” and “Left Join”. Both joins are same. “Outer” keyword is optional.

Inner join

Inner join is one type of join in SQL server to retrieve data from two tables.

Inner join syntax is as below:

Select column1[, column2 …]

from Table1

     inner join Table2 on Table1.Column1 = Table2.Column4 [ and Table1.Column6 = Table2.ColumnN]

Inner join returns only those data which are matched with condition in both tables. Inner join will exclude the data from table1 and table2 which are not met the condition.

Let’s take an example:

We have two tables Employee and Projects. Employee table have 10 employee records and Project table have 5 projects information.

Out of 10 employees, 2 employees are not allocated into any project. So when you tried to execute inner join on that table then inner join will return 8 records (except the 2 employees which are not allocated into the project).

Syntax:

Select *

    from EmployeeMaster
         inner join ProjectMaster on EmployeeMaster.ProjectId = ProjectMaster.ProjectId

Employee records (which are not allocated into the project) are not met the condition. So that data will be excluded from the output.

Joins in SQL Server

SQL server supports following types of joins to retrieve the data from more than one table.

1. Inner join
2. Left outer join
3. Right outer join
4. Self-join
5. Cross join

Inner join: This join is used to retrieve the data which are matched into both tables. For example, if table1 have 10 records and table2 have 8 records and only 3 records are matched with each other using condition then only 3 records will return.

Left outer join: This join is used when need to retrieve all data from the left side table and matched data from right side table. For example table1 have 10 records and table2 have 8 records and only 3 records are matched according to condition then left outer join returns 10 records.

Right outer join: This join is used when need to retrieve all data from the right side table and matched data from left side table. For example table1 have 10 records and table2 have 8 records and only 3 records are matched according to condition then right outer join returns 8 records.


Self-join: When we use same table on both side of join then it is called self-join.


Cross join: This join is used to retrieve the cross joined data. Means every record in table1 will join with all the records of table2. For example, If table1 have 10 records and table2 have 8 records then this join will return 80 records.