Trigger Locking the Table

[Nithindev]

The code is :


USE [Religare]
GO
/****** Object: Trigger [dbo].[clientpanno] Script Date: 10/22/2008 11:16:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[clientpanno] on [dbo].[ClientCode] for insert
As
Begin
Declare @ccid as int
Delete from sql_identity where id_tableid=10183
Delete cc
Output
'PAN No does not exist',
Deleted.cc_ClientCode,
Deleted.cc_RMcode,
Deleted.cc_Panno,
Deleted.cc_Productname
Into ClientCodeError
From Clientcode cc left outer join client c
On cc.cc_panno=c.client_panno
Where c.client_panno is null

Delete CC
Output
'RMCode in clientcode is not associated with client',
Deleted.cc_ClientCode,
Deleted.cc_RMcode,
Deleted.cc_Panno,
Deleted.cc_Productname
Into clientCodeError
From clientcode cc
Left outer
Join client c
On cc.cc_panno=c.client_panno
Join users u on client_userid=user_userid
Where u.user_empcode!=cc.cc_rmcode

Delete CC
Output
'RMCode does not exist in CRM',
Deleted.cc_ClientCode,
Deleted.cc_RMcode,
Deleted.cc_Panno,
Deleted.cc_Productname
Into clientCodeError
From clientcode cc left outer join users u on cc.cc_rmcode=u.user_empcode
Where u.user_empcode is null

Delete T1
Output
'ClientCode already exists in the database',
Deleted.cc_ClientCode,
Deleted.cc_RMcode,
Deleted.cc_Panno,
Deleted.cc_Productname
Into clientCodeError
From ClientCode T1, ClientCode T2
Where T1.cc_clientcode = T2.cc_clientcode
And T1.cc_clientcodeid > T2.cc_clientcodeid

Declare @bcpCommand varchar(100)
SET @bcpCommand='bcp "SELECT * FROM Religare..ClientCodeError" queryout "c:\ClientCodeError.txt" -U sa -P SA1 -c'
EXEC master..xp_cmdshell @bcpCommand

Truncate table clientcodeerror

Select @ccid=cc_clientcodeid from clientcode order by cc_clientcodeid asc
Set @ccid=@ccid+1

Update Sql_Identity Set Id_NextId=@ccid

/*Update cc set cc.cc_activeid=c.client_activeid
from clientcode cc ,client c
where cc.cc_panno=c.client_panno and cc.cc_activeid is null
Update cc set cc.cc_assetid=am.assetid
from clientcode cc ,ClientCodeAssetMapping am
where cc.cc_productname=am.productname*/

Insert Into
[APPSERVER].[PortfolioTracker].[DBO].ClientCodeAssetMapping(assetid,ModifiedDateTime,CreatedDateTime,clientcode,jvid,ModifiedBy,CreatedBy)
Select c1.cc_assetid 'AssetID',getdate(),getdate(),c1.cc_clientcode,c1.cc_activeid,'system','system'
From clientcode c1 left outer join inserted c2
on c1.cc_clientcodeid=c2.cc_clientcodeid where c1.cc_clientcodeid=c2.cc_clientcodeid
End



The trigger is basically being used to validate the rows and delete and output the invalid rows of the table ClientCode.
The code works fine if I execute it normally that is select all the DML statements and execute it works fine.

I have not done any package development before.I created this package from Import/Export Wizard.

 

[Nithindev]

I am inserting records from excel sheet using SSIS package which can fire triggers.When I execute the package or just use insert statement to insert records the table gets locked and it shows error in SSIS messages and just shows "executing query..." and never completes the query.