Deadlock Problem in SQL Trigger Urgent

Hello Db experts,

Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.What does the trigger do?|||I'd bet that we can. Check out the FAQ entry for How to get quick and correct answers (http://www.dbforums.com/showthread.php?t=1212452#post4527530) for help.

-PatP|||if you want it fixed you need to give us code.|||Dear Coolberg

Please know thrigger do some calculation for Table1's last inserted record and insert or Update in an another table like Control1 table|||Hello Db experts,

Recently I am facing a problem as, I have a trigger on table1 in after insert
event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.

Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!|||Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!Easy for you to say! ;) Thanks for the Bengali assistance, I'm sure it will help.

-PatP|||Hello Again,

Sorry for not posting the code before. Please have a look at my code below. Now let me explain what problem I faced,there is a table named Table1 where data being inserted from 4 different sources in huge number (Almost 1000 records/sec), The After Insert trigger(Trigger1) written on Table1 inserts or updates the extracted information from Table1 to Table1_Backup.
Most of the times during Insertion to Table1 I am getting a message as:

"Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. "

and I am loosing data in Table1 i.e not all records were inserted.
But when I removed the Trigger , I didn't get this error message and there were no data loss. Looking Forward For any help to come out of this problem.

Code:

create table Table1
(sId int,
sName varchar(50),
sPhone varchar(50),
sBalance bigint
)

create table Table1_Backup
(sId int,
sName varchar(50),
sPhone varchar(50),
sBalance bigint

)

Create TRIGGER Trigger1
ON Table1
After INSERT
AS
Declare

@.counter int,
@.sId int,
@.sName varchar(50),
@.sPhone varchar(30),
@.sBalance bigint;

Begin

set @.counter = 0;
set @.sId = 0;
set @.sName = null;
set @.sPhone = null;
set @.sBalance = 0;


select @.sId = sId,
@.sName = sName,
@.sPhone = sPhone,
@.sBalance = sBalance
from INSERTED Table1;


Select @.counter = @.sId From Table1_Backup WHERE sId = @.sId;

IF (@.counter < 1 )
insert into Table1_Backup
values(@.sId,@.sName,@.sPhone,@.sBalance);
ELSE
Update Table1_Backup
set sBalance = sBalance + @.sBalance
Where sId = @.sId;
End|||Hi Dada,
Nin bangaly bolchi, kintu ami to problem ta post korechi shudhu bangali der jonno noy, ekhane to non bangali o ache. Kintu apnar shahajjo korar icche dhekhe khub valo laglo. Tai apnake bangaly likchi. Amra bangalay kotha (chat) bolte pari jodi apni chan, amar yahoo id hocche "mahfuz_onsky@.yahoo.com".
Asha kori shomadhan ta apnar kachei pabo ebong ekjon bangali dada er kache pele valoi lagbe.

Kotha hobe.

Mahfuz.


Nomoskar Mahfuz,
Ami apnake ekta private message diyechi.Doyakore oti porben.Ar apnar problem ti bhalo kore amay bolle kuub balo hoy.Ar apni je trigger ta likhechen ,otar code akhane likle bhalo hoy, mane akhane sobai apnake Banglay likhe bolte anurod koreche.Apni amar likkhe pathan, ba banglay akhane likhun,tahole DBA ra kuub sohoje apnake sahajjo korte parbe.
Bhalo Thakben!!|||Can you show me the Insert statement that you are using to insert data? By the way which version of Sql server are you running?
It may be a problem in your application code,plz provide the code where you are inserting the data.|||In SQL Server, a trigger fires once for each SQL Statement that launches the trigger, no matter how many rows the original statement might have affected. There could be 1000 or more rows in the INSERTED table for any given execution of your trigger.

There appears to be a logic error inside your trigger, but without knowing exactly what you are trying to do, I can't fix that. The code translated to use sets instead of working one row at a time would be:CREATE TRIGGER Trigger1
ON Table1
After INSERT
AS

INSERT INTO Table1_Backup (
sId, sName, sPhone, sBalance)
SELECT sId, sName, sPhone, sBalance
FROM INSERTED
WHERE sID < 1

UPDATE Table1_Backup
SET sBalance = sBalance + (SELECT Sum(INSERTED.sBalance)
FROM INSERTED
WHERE INSERTED.sId = Table1_Backup.sId)

END-PatPsql