May 21, 2013 at 11:29 pm
i am having two tables
A,B
On Insertion in A, i insert data in B table(using Insert trigger) but i need to get data for some columns in B table from a view.
But it's causing an error - Invalid object name - view name.
/**/
CREATE TRIGGER [dbo].[InsCsBpAsset]
ON [dbo].[CsAsset]
AFTER INSERT
AS
BEGIN
declare @tax decimal(18,4),
@Ret decimal(18,4),
@EfctRet money,
@IntAmt money,
@Amt money,/*final*/
@CsId int,
@NetR money,
@RealR money,
@Inflation decimal(18,4)money,@AsetId smallint
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select @Amt=AsetAmt from inserted;
select @AsetId=AsetId from inserted
select @tax=isnull(cast(Tax as decimal(18,4)),0.0000) from VwBPAssumption where Assetid=AsetId and lkptyp='AST';
select @Ret= isnull(cast(Rtrn as decimal(18,4)),0.0000) from VwBPAssumption where Assetid=AsetId and lkptyp='AST';
select @EfctRet=((@Ret/100)*(1-(@Tax/100)));
select @IntAmt=(@Amt*(@EfctRet/100));
/*
insert into csbpasset (CsId,MemId,AsetId,AsetHd,AsetAmt,RetId,TaxId,EfctRet,IntrstAmt)
values(CsId,MemId,AsetId,AsetHd,AsetAmt,@Ret,@Tax,@EfctRet,@IntAmt)
*/
insert into csbpasset (CsId,MemId,AsetId,AsetHd,AsetAmt,RetId,TaxId,EfctRet,IntrstAmt)
select CsId,MemId,AsetId,AsetHd,AsetAmt,@Ret,@Tax,@EfctRet,@IntAmt from inserted
-- Insert statements for trigger here
/*Update final amounts in table - csbpfnlasset*/
select @NetR= ((isnull(sum(IntrstAmt),0.0000)/sum(AsetAmt))*100) from inserted;
select @Inflation= isnull(Rtrn,7.0000) from vwBPAssumption where Asmptnid=18;
select @RealR=((@NetR-@Inflation)/(100+@Inflation)) ;
if exists(select 1 from csbpasset where csid=@CsId)
begin
insert into csbpfnlasset(csid,NetRet,RealRet) values(@CsId,@NetR,@RealR)
end
else
begin
update csbpfnlasset set NetRet=@NetR,RealRet=@RealR where CsID=@CsId
end
END
May 22, 2013 at 1:23 am
The trigger will execute in the context of the user. Does the user have permissions to the view "VwBPAssumption"?
May 22, 2013 at 1:57 am
Besides that, there's a whole bunch of problems with that trigger...
bhanupratapsngh9 (5/21/2013)
select @Amt=AsetAmt from inserted;select @AsetId=AsetId from inserted
So what happens when there's more than one row in inserted?
That trigger can only handle a single-row insert. You need to fix it so that it can handle any number of rows in the inserted table. Set-based logic, not row-by-row logic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2013 at 2:11 am
Dear Gila,
i want solution for it as i am new to triggers, Please !!
i am having 3 tables,
A - csid,memid,asetid,asetamt
B - csid,memid,asetid,asetamt,Taxid,retid,EffRet,InsRet
C - csid,NetRet,RealRet
when user inserts value in table A
i use insert trigger
which will insert data in table B,table C
but taxid,retid will be received by a 'View'
EffRet, InsRet comes after calculation using taxid,Retid,Astid,AsetAmt
after that for perticular csid table C will calculate
NetRet, RealRet which is according to sum of AsetAmt of table B
May 22, 2013 at 2:16 am
I don't have time right now to write it for you. If you want someone to give you a solution, post the table definitions (as CREATE TABLE), some sample data (as INSERT statements), the view definition and a description of what the trigger is supposed to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply