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

quote:

---

Originally posted by visakh16

Isnt it much better to remove the trigger and do the check in ssis itself. If its invalid row (containing invalid values), use redirect row option to populate an error file or table. Trigger causes a serious impact on performance especially when you're doing lots of insert/updates.

---

quote:

---

Originally posted by nithin.gujjar

quote:

---

Originally posted by visakh16

Isnt it much better to remove the trigger and do the check in ssis itself. If its invalid row (containing invalid values), use redirect row option to populate an error file or table. Trigger causes a serious impact on performance especially when you're doing lots of insert/updates.

---

I have not done any package development before.I created this package from Import/Export Wizard.I dont know how to do what you have said.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by nithin.gujjar

quote:

---

Originally posted by visakh16

Isnt it much better to remove the trigger and do the check in ssis itself. If its invalid row (containing invalid values), use redirect row option to populate an error file or table. Trigger causes a serious impact on performance especially when you're doing lots of insert/updates.

---

I have not done any package development before.I created this package from Import/Export Wizard.I dont know how to do what you have said.

---

you just need to check for you validity condition using a conditional task. it will have two outputs one for valid rows and other for invalid ones. valid output will be connected to your destination table. Invalid output will go to error file or table (whichever you like).
If you want more help, post what your exact scenario is & i will try to provide details of tasks to be used.

---

quote:

---

Originally posted by visakh16

then what you need is use lookup tasks instead of each join you used in delete statement. On failure give redirect row option and use oledb destination task to populate invalid rows to ClientErrorCode table.
Refer below article for more info

http://www.sqlis.com/311.aspx

---