Sunday, February 14, 2010

Trigger with multiple insert/ Update/Delete

Hello Friends
Recently i faced a problem and got solution of it so..
i would like to put the solution on my Blog

I wanted to use Trigger to drop entries in a table on insertion of records in its parent table...
on first phase i created a trigger for the parent table (Table_1) to drop entries on Table_2.

this was working very perfectly...
but suddenly i came to know that it is not as per i expected...

i mean when i fire query of Insert using single record insert
i.e Insert into Table_1 (...fields..) values (...values..)

in this case it was perfectly fine...

but issue was when i fire query of multiple records to insert on a single query
i.e. Insert into Table_1
     select [Name],[Address] from Table_3
In this case the trigger fires only once for first record
so i was not getting rest records inserted into Table_2

after doing small RnD I came to know...the exact solution


the trigger for this issue is like this


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter TRIGGER Trigger_1 -- the Trigger Name
   ON  Table_1 -- the table name on which the trigger will fire
   AFTER INSERT -- the action
AS
BEGIN
-- Declare a TempTable to store inserted Records
Declare @TempTable Table
(
id int,
Name nvarchar(50),
Address nvarchar(50)
)
        -- insert the newly inserted records into The Temporary Table
        insert into @TempTable
        select Id,Name,Address from Inserted    --  The Key word "Inserted" is a Table of
                                                --    Rows which are inserted for the Table

        Declare @id int,
            @Name nvarchar(50),   
            @Address nvarchar(50)



            -- Using Cursor or any other alternatives you can   
            -- now perform with inserted Records
            DECLARE Curs_1 CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR
                SELECT Id,Name,Address FROM @TempTable
            OPEN Curs_1
            FETCH NEXT FROM Curs_1  INTO @id,@Name,@Address

            WHILE @@FETCH_STATUS <> -1
            BEGIN

                -- The logic is placed  over here
                insert into table_2 (Name,ItemName) values(@Name,@Address)

            FETCH NEXT FROM Curs_1  INTO @id,@Name,@Address
            END
            CLOSE Curs_1
            DEALLOCATE Curs_1


END
GO

Hope you got the code and Solution....

All the Best
Post a Comment