July 24, 2009 at 12:35 am
Can someone please help me 🙁
i want [totalpay] from TD2 to automatically calculate [TD1].[rate] * [hours] when values get inserted. How will i go about doing this?
thankx
example
create table T1(
T1ID int primary key,
rate int,
)
create table T2(
T2ID int primary key,
hours int,
totalpay money,
T1ID foreing key references T1(T1ID)
)
July 24, 2009 at 1:05 am
govnaah (7/24/2009)
i want [totalpay] from TD2 to automatically calculate [TD1].[rate] * [hours] when values get inserted. How will i go about doing this?
Hi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
July 24, 2009 at 1:23 am
Hi
Hi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
]
Guy needs total=rate*hours.
Above answer would simply return ID times hours.
July 24, 2009 at 1:23 am
Hi
Hi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
]
Guy needs total=rate*hours.
Above answer would simply return ID times hours.
July 24, 2009 at 1:23 am
Hi
Hi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
]
Guy needs total=rate*hours.
Above answer would simply return ID times hours.
July 24, 2009 at 1:23 am
Hi
Hi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
]
Guy needs total=rate*hours.
Above answer would simply return ID times hours.
July 24, 2009 at 2:23 am
t.hitendra (7/24/2009)
HiHi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
]
Guy needs total=rate*hours.
Above answer would simply return ID times hours.
Ok,
then use the trigger during the insert statement like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay int
)
create trigger XYZ
on T2
for insert
as
declare @result int
select @result = T1ID from inserted
update T2
set totalpay = hours * (select rate from T1 where T1ID = @result)
GO
July 24, 2009 at 3:15 am
arun.sas (7/24/2009)
t.hitendra (7/24/2009)
HiHi,
use the compute column in the table like
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay as (T1ID * hours)
)
]
Guy needs total=rate*hours.
Above answer would simply return ID times hours.
Ok,
then use the trigger during the insert statement like
create table T2(T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay int
)
create trigger XYZ
on T2
for insert
as
declare @result int
select @result = T1ID from inserted
update T2
set totalpay = hours * (select rate from T1 where T1ID = @result)
GO
i just tried that and i got this error
Msg 271, Level 16, State 1, Procedure XYZ, Line 8
The column "totalpay" cannot be modified because it is either a computed column or is the result of a UNION operator.
July 24, 2009 at 3:22 am
Hi,
create the table T2 with the totalpay column as int,
then create trigger
July 24, 2009 at 4:33 pm
arun.sas (7/24/2009)
Hi,create the table T2 with the totalpay column as int,
then create trigger
thank you very much... works 🙂
but i am facing another problem when inserting.
IF YOU COULD PLEAASE TRY THE FOLLOWING CODE AND YOU WILL SEE WHAT I'M TLKING ABOUT (i don't know how to explain it)
create table T1(
T1ID int primary key,
rate int,
)
create table T2(
T2ID int primary key,
hours int,
T1ID int FOREIGN KEY references T1(T1ID),
totalpay int,
)
go
--===============================================================
create trigger XYZ
on T2
for insert
as
declare @result int
select @result = T1ID from inserted
update T2
set totalpay = hours * (select rate from T1 where T1ID = @result)
--================================================================
--insert into T1
insert into T1
select 4,1
union all
select 5,15
--insert into T2
insert into T2
select 7,10,5,null
union all
select 3,12,1,null
union all
select 4,12,3,null
select * from T1
go
select * from T2
July 24, 2009 at 4:38 pm
what was happening was that on every new insert, the above rows get affected (updated)
due to this code. update T2
set totalpay = hours * (select rate from T1 where T1ID = @result)
i'm wondering if there is a way to get pass this? 🙂
THANK YOU EVERYONE FOR YOUR HELP :):)
July 26, 2009 at 1:30 pm
FIGURED IT OUT! (@ 6am in the morning, while i was dreaming in bed) -- aint that some shit
just had to add a "WHERE" statement
THANK YOU ALL FOR YOUR HELP :):)
July 26, 2009 at 9:13 pm
Hi,
Zeal person only having these type of dreams, you note it down your entire dream in the record its help one day defiantly.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply