Jun 25, 2013

Triggers in Sql Server : Part - 4

In earlier articles, we learnt
In this article, we will take an example of Instead of trigger and will see how it works.

In the example we are taking two tables,
  • Product for maintaining product master details
  • Product Log for logging on product master
With trigger we will create log in product_log table on performing insert/update/delete operations on product table

Script to create product and product_log Table
create table product (prod_id int, prod_desc varchar(10))
go
create table product_log (prod_id int, prod_desc varchar(10), action char(1))
go
insert into product values(1, 'prod-1')

Now lets create 3 triggers -
  • Instead of Insert
  • Instead of Update
  • Instead of Delete
on product Table which will create log in product_log table based on the operation performed.

create trigger tr_product_insert
on product
instead of insert
as
begin
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'I' from inserted
end
go
create trigger tr_product_update
on product
instead of update
as
begin
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'B' from inserted
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'A' from deleted
end
go
create trigger tr_product
on product
instead of delete
as
begin
    insert into product_log(prod_id, prod_desc, action)
    select prod_id, prod_desc, 'D' from deleted
end

Let's perform an Insert operation on product table. On insertion tr_product_insert trigger will automatically execute and create log entries in product_log table.

insert into product values(2, 'prod-2')
select * from product
select * from product_log
OUTPUT


Look after Insert Operation, Trigger tr_product_insert inserted a log entry in product_log but actual Insert Operation on product table is not performed.

Now let's perform an Update Operation on product table. On updation tr_product_update trigger will automatically execute and create log entries in product_log table.

update product set prod_desc = 'prod-2' where prod_id = 1
select * from product
select * from product_log
OUTPUT


Look after Update Operation, Trigger tr_product_update inserted log entries in product_log but actual update operation on product table is not performed.

Now let's perform a Delete Operation on product table. On deletion tr_product_delete trigger will automatically execute and create log entries in product_log table.

delete from product where prod_id = 1
select * from product
select * from product_log
OUTPUT


Look after Delete Operation, trigger tr_product_delete inserted log entries in product_log but actual delete operation on product table is not performed.

<< Prev Home

Triggers in Sql Server : Part - 3

In earlier articles, we learnt
In this article, we will take an example of After Trigger and will see how it works.

In the example we are taking two tables, one for Sales and one for Stocks and with trigger we will update Stock table on performing insert/update/delete operations on Sales table

Lets' first create Stock and Sale Tables and populate some data in Stock Tabledata

create table stock(prod_id int, prod_name varchar(50), qty int)
go
insert into stock
select 1, 'Product-1', 10 union all
select 2, 'Product-2', 10
go
create table sale(bill_id int, prod_id int, qty int)

Now lets create 3 Triggers:
  • After Insert
  • After Update
  • After Delete 
on Sale Table which will update the Stock table based on the operation performed.

create trigger tr_sale_insert
on sale
after insert
as
begin
    update s set qty = s.qty - i.qty
    from stock s
    inner join inserted i on s.prod_id = i.prod_id
end
go
create trigger tr_sale_delete
on sale
after delete
as
begin
    update s set qty = s.qty + d.qty
    from stock s
    inner join deleted d on s.prod_id = d.prod_id
end
go
create trigger tr_sale_update
on sale
after update
as
begin
    update s set qty = s.qty + d.qty - i.qty
    from stock s
    inner join inserted i on s.prod_id = i.prod_id
    inner join deleted d on s.prod_id = d.prod_id
end

Let's first run a query to see the data of Stock Table

select * from stock
OUTPUT


Now let's perform an insert operation on Sale Table. On insertion tr_sale_insert trigger will automatically execute and update the Stock Table.
insert into sale values(1, 1, 5)
select * from stock
OUTPUT


Look at the Stock Table after insert operation. Trigger tr_sale_insert updated the qty of prod_id 1 from 10 to 5.

Now let's perform an update operation on Sale Table. On updation tr_sale_update trigger will automatically execute and update the Stock table.
update sale set qty = 3 where bill_id = 1 and prod_id = 1
select * from stock
OUTPUT


Look at the Stock Table after update operation. Trigger tr_sale_update updated the qty of prod_id 1 from 5 to 7.

Now let's perform delete operation on Sale Table. On deletion tr_sale_delete trigger will automatically execute and update the Stock table.
delete from sale where bill_id = 1 and prod_id = 1
select * from stock
OUTPUT


Look at the Stock Table after delete operation. Trigger tr_sale_delete updated the qty of prod_id 1 from 7 to 10.

In the next part, we will take an example of Instead of Trigger

<< Prev Home Next >>

Triggers in Sql Server : Part - 2

In Previous article, we learnt about what is trigger.

In this article, we will learn about Magic tables in triggers.

Magic Tables are logical tables which are created automatically when DML operations (insert/update/delete") are performed. These tables temporarily holds the data depending upon the operation and are accessible in triggers.

There are two types of magic tables
  • Inserted
  • Deleted
Magic Tables Inserted/Deleted are created depending on the DML operation "insert/update/delete" performed
  • Insert: Inserted table is created when an insert operation is performed and holds recently inserted data in the table.
  • Update: Both Inserted and Deleted tables are created when an update operation is performed. Inserted table holds the updated rows (new data) while deleted table holds the old data of the rows which are updated as a result of update statement.
  • Delete: Deleted table is created when delete operation is performed and holds recently deleted data from the table.
In the next article, we will discuss the examples of different types of triggers and how to use these magic tables in triggers.

<< Prev Home Next >>

Triggers in Sql Server : Part - 1

A Trigger is a special kind of stored procedure that automatically executes when an event occurs in the database.

Types of Triggers
  • DDL Triggers execute on data definition language (DDL) events like CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. 
  • DML Triggers execute on data manipulation language (DML) event like INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
  • DML Triggers are of two types.
    • After Triggers: These triggers run after once insert, update or delete operation is executed on a table
    • Instead Of Triggers: These triggers run before insert, update or delete operation and do not let execute actual insert, update or delete performed on the table.
When a trigger is executed, magic tables "inserted/deleted" are automatically created and are accessible in the trigger.

In the next article, we will learn more about Magic Tables

Home Next >>

Jun 10, 2013

Collation Issues SQL Server

In previous article, we learnt about collation in SQL Server

In this article, we will discuss some of the common issues that encounters with collation.
Collation issues arises with string type of datataypes like char, nchar, varchar, nvarchar, text etc...
Common scenerio of collation issues
  • columns with different collations in where/on condition in a query
  • columns with different collations in union/union all
Lets take examples to understand the issues described above

First create two tables with different collation
create table tab1 (val varchar(10) collate Latin1_General_100_CI_AS)
create table tab2 (val varchar(10) collate Latin1_General_100_CS_AS)
insert into tab1
select 'ABC' union all
select 'DEF'
insert into tab2
select 'ABC'

Lets run a query with different collations with "on" condition
select    *
from    tab1 t1
inner join tab2 t2 on t1.val = t2.val
OUTPUT


Lets run a query with different collations with union

select val from tab1
union all
select val from tab2
OUTPUT

Cause: We are getting this error because collation of the columns used in the comparing condition are different.
Solution: In order to resolve the issue, we need to run the query with the same collation for both the columns. The simplest and easiest solution to resolve the same is to set the collation of both the columns as of database

select    *
from    tab1 t1
inner join tab2 t2 on t1.val collate database_default = t2.val collate database_default

select val collate database_default from tab1
union all
select val collate database_default from tab2
OUTPUT

Collation in SQL Server

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Types of collation sensitivity
  • Case Sensitivity
  • Accent Sensitivity
  • Kana Sensitivity
  • Width Sensitivity
Case Sensitivity
If A and a, B and b, etc. are treated in the same way, then it is case-insensitive.
A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.
  • CI specifies Case Insensitive 
  • CS specifies Case Sensitive
Accent Sensitivity
If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.
  • AI specifies Accent Insensitive 
  • AS specifies Accent Sensitive
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Query to check collation and compatibility level of a database
SELECT compatibility_level, collation_name
FROM   sys.databases
WHERE  name = 'DatabaseName'

Query to find out all available collations in SQL Server database
SELECT    [name]
        , COLLATIONPROPERTY([name], 'CodePage') AS [CodePage]
        , COLLATIONPROPERTY([name], 'LCID') AS [LCID]
        , COLLATIONPROPERTY([name], 'ComparisonStyle') AS [ComparisonStyle]
        , COLLATIONPROPERTY([name], 'Version') AS [Version]
        , [description]
FROM    fn_helpcollations()

In the next article, we will explore,
common issues encountered with collation

Jun 8, 2013

yield keyword in c#

With yield keyword, the control moves from the caller to source and from the source back to the caller to and fro.

yield statement can be used in two forms
yield return <expression>;
yield break;

yield return statement returns each element at at time.
Yield return allows you to run custom iteration without temp collection.

Lets take an example to understand in more detail

Function to return RandomNumbers using List
static IEnumerable<int> GetRandomNumbers(int count)
{
    Random rand = new Random();
    List<int> ints = new List<int>(count);
    for (int i = 0; i < count; i++)
    {
        ints.Add(rand.Next());
    }
    return ints;
}

Same function using return yield without using any temp collection
using System;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            foreach (int i in GetRandomNumbers(10))
                Console.WriteLine(i);
        }     

        static IEnumerable<int> GetRandomNumbers(int count)
        {
            Random rand = new Random();           
            for (int i = 0; i < count; i++)
            {
                yield return rand.Next();
            }           
        }
    }
}
OUTPUT

See, in the above examples, both are performing the same thing, but in the first example we are creating temp collection "list" but in the second example with yield there is no need create any temp collection

yield break statement is used to break the iteration. break statement is used to terminate the loop but yield break is used to return from the method.

static void Main(string[] args)
{           
    foreach (int i in GetRandomNumbers(10))
        Console.WriteLine(i);
    System.Threading.Thread.Sleep(5000);           
}

static IEnumerable<int> GetRandomNumbers(int count)
{
    Random rand = new Random();
    for (int i = 0; i < count; i++)
    {
        yield return rand.Next();
        yield break;
    }
}
OUTPUT