insert into clientcode(cc_clientcode,cc_rmcode,cc_productname,cc_panno)
values(13213,20003,'Equity','AAGPW6964M')

USE [Religare]
GO
/****** Object:  Trigger [dbo].[clientpanno]    Script Date: 10/17/2008 12:00:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[clientpanno] on [dbo].[ClientCode] for insert 
As
Begin
	Delete from sql_identity where id_tableid=10183
	Declare @panno as nvarchar(10),@panno1 as nvarchar(10)
	Declare @rmcode as nvarchar(10),@rmcode1 as nvarchar(10),@clientcode as nvarchar(10),@top as nvarchar(10)
	Declare @activeid as varchar(10)
	Declare @ccid as int,@count as int
	/*Create Table ClientCodeError 
		(SerialNo int Identity(1,1),
		Remark     nchar(50) not null,
		ClientCode nchar(20) Not Null,
		RMCode     nchar(20) not null,
		PANNo	   nchar(20) not null,
		Product    nchar(20) not null)*/
	Declare @InvalidRMCodematch as nvarchar(50),@InvalidRMCode as nvarchar(50),@InvalidPANNo as nvarchar(50),@DuplicateClientCode as nvarchar(50)
	Set @InvalidRMCodematch='Clientcode mapped to wrong RMCode'
	Set @InvalidRMCode='RMCode does not exist in the CRM'
	Set @InvalidPANNo='PAN No. does not exist in the CRM'
	Set @DuplicateClientCode='This Client Code already exists'
	Select @ccid=cc_clientcodeid from clientcode order by cc_clientcodeid
	Declare curClientCode SCROLL cursor
	For Select cc_panno,cc_rmcode from inserted where cc_activeid is null Order By cc_clientcodeid 
	Open curClientCode
	Fetch curClientCode into @panno,@rmcode
	While(@@fetch_status=0)
	Begin
		Select @activeid=client_activeid from client where client_panno=@panno
		Select @rmcode1=user_empcode from users where user_empcode=@rmcode
		Select @panno1=client_panno from client 
			where client_userid=(Select user_userid from users where user_empcode=@rmcode)
		select @count=count(cc_clientcode) from clientcode 
			where cc_panno=@panno
		select @top=cc_clientcodeid from clientcode 
			where cc_panno=@panno order by cc_clientcodeid desc
		if(@activeid is null)
		Begin
			Print 'The PAN No does not exist'
			Insert into	ClientCodeError 
			Select @InvalidPANNo,cc_clientcode,cc_rmcode,cc_panno,cc_productname 
			from clientcode where cc_panno=@panno
			delete from clientcode where cc_panno=@panno		
		End
		Else if(@rmcode1 is null)
		Begin
			Print 'The RMCode does not exist in CRM'
			Insert into ClientCodeError
			Select @InvalidRMCode,cc_clientcode,cc_rmcode,cc_panno,cc_productname 
			from clientcode where cc_panno=@panno
			delete from clientcode where cc_panno=@panno
		End
		Else if(@panno1 != @panno)
		Begin
			Print 'Clientcode mapped to wrong RMCode'
			Insert into ClientCodeError
			Select @InvalidRMCodematch,cc_clientcode,cc_rmcode,cc_panno,cc_productname 
			from clientcode where cc_panno=@panno
			delete from clientcode where cc_panno=@panno
		End
		Else if(@count>1)
		Begin
			Print 'This Client Code already exists'
			Insert into ClientCodeError
			Select @DuplicateClientCode,cc_clientcode,cc_rmcode,cc_panno,cc_productname 
			from clientcode where cc_panno=@panno
			delete from clientcode where cc_clientcodeid=@top
		End
		Else
		Begin			
			Update clientcode set cc_activeid=@activeid where cc_panno=@panno
		End
		Fetch curClientCode into @panno,@rmcode
		Print 'The Pan No. is '+@panno
		Print 'The RMCode is '+@rmcode	
	End
	Close curClientCode
	Deallocate curClientCode
	Select @ccid=cc_clientcodeid from clientcode order by cc_clientcodeid
	Set @ccid=@ccid+1
	Insert sql_identity (id_tableid,id_nextid)values(10183,@ccid)
	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
	Delete from ClientCodeError

End

ALTER TRIGGER	dbo.trgClientPanno
ON		dbo.ClientCode
FOR INSERT 
AS

SET NOCOUNT ON

-- PAN No. does not exist in the CRM
DELETE		cc
OUTPUT		'PAN No. does not exist in the CRM',
		cc.cc_clientcode,
		cc.cc_rmcode,
		cc.cc_panno,
		cc.cc_productname
INTO		ClientCodeError
FROM		ClientCode AS cc
INNER JOIN	inserted AS i ON i.cc_panno = cc.cc_panno
LEFT JOIN	Client AS c ON c.client_panno = cc.cc_panno
WHERE		c.client_panno IS NULL

quote:

---

Originally posted by Peso

Try something like this instead...

ALTER TRIGGER	dbo.trgClientPanno
ON		dbo.ClientCode
FOR INSERT 
AS

SET NOCOUNT ON

-- PAN No. does not exist in the CRM
DELETE		cc
OUTPUT		'PAN No. does not exist in the CRM',
		cc.cc_clientcode,
		cc.cc_rmcode,
		cc.cc_panno,
		cc.cc_productname
INTO		ClientCodeError
FROM		ClientCode AS cc
INNER JOIN	inserted AS i ON i.cc_panno = cc.cc_panno
LEFT JOIN	Client AS c ON c.client_panno = cc.cc_panno
WHERE		c.client_panno IS NULL

---

E 12°55'05.63"
N 56°04'39.26"

---

DELETE cc
OUTPUT 'PAN No. does not exist in the CRM',
cc.cc_clientcode,
cc.cc_rmcode,
cc.cc_panno,
cc.cc_productname
INTO ClientCodeError
FROM ClientCode AS cc
LEFT JOIN Client AS c ON c.client_panno = cc.cc_panno
WHERE c.client_panno IS NULL

it gave me this error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cc.cc_clientcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cc.cc_rmcode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cc.cc_panno" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cc.cc_productname" could not be bound.

DELETE cc
OUTPUT 
DELETED.cc_clientcode,
DELETED.cc_rmcode,
DELETED.cc_panno,
DELETED.cc_productname
INTO ClientCodeError
FROM ClientCode AS cc
LEFT JOIN Client AS c ON c.client_panno = cc.cc_panno
WHERE c.client_panno IS NULL