August 6, 2008 at 12:31 am
Basically i am trying to upload some data from excel to the sql table, and a trigger needs to be executed which gets the active id from a table called Client and updates table ClientCode. But the trigger is not executing. Can some one help me with this?
Thanks
USE [Database]
GO
/****** Object: Trigger [dbo].[clientpanno] Script Date: 08/06/2008 11:41:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[clientpanno] on [dbo].[ClientCode] for insert,update
as
begin
declare @panno as varchar(10)
declare @activeid as varchar(10)
declare @clientcodeid as int
select @clientcodeid=cc_clientcodeid from inserted
print @clientcodeid
select @panno=cc_panno from clientcode where cc_clientcodeid=@clientcodeid
print @panno
select @activeid=client_activeid from client where client_panno=@panno
print @activeid
update clientcode set cc_activeid=@activeid where cc_clientcodeid=@clientcodeid
End
August 6, 2008 at 12:44 am
Also the excel sheet is getting uploaded properly without any error, so only the trigger is not getting executed,
August 6, 2008 at 1:16 am
Your trigger expects a single row to be added/updated in your table, you can't be sure of that.
Try this way:
USE [Database]
GO
/****** Object: Trigger [dbo].[clientpanno] Script Date: 08/06/2008 11:41:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[clientpanno] on [dbo].[ClientCode] for insert,update
as
begin
update clientcode
set cc_activeid = c.client_activeid
from clientcode as a
inner join inserted as b
on a.cc_clientcodeid = b.cc_clientcodeid
inner join client as c
on a.cc_panno = c.client_panno
end
Regards
Gianluca
-- Gianluca Sartori
August 8, 2008 at 6:56 am
First of all really sorry for the late reply.
Thanks for the code but the code is not right in our context.
Let me clear few thing here
1.ClientCode is an empty table which gets populated by the excel sheet.
2.Client is an existing table with existing data
So when we upload the excel sheet ,data from excel goes to ClientCode table, also some data comes from Client table. So the link between Client record and clientCode record is PanNo, which is already present in Client table in the field client_panno and in clientcode table in cc_panno. Based on the Panno in excel record we pull in data from Client table and pass it to Client Code.
August 8, 2008 at 7:09 am
To test that whether it is expecting only one row I uploaded only one row from excel nut even then it did not work..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply