Triggers/calculated fields MSSQL7

Triggers/calculated fields MSSQL7

Post by Ken Sorenso » Sat, 05 Feb 2000 04:00:00



What is the recommended way to update calculated fields? I was assuming I
would have to create triggers to sum the detail fields into a total field. I
cannot seem to find much information about triggers. I have a simple table,
and all I need to do is a few calculations and get some totals.. Anyone have
an example of similar trigger syntax? Perhaps a place I can find additional
info ?  It this what I should be using? I do not want to have the math at
the client level.

Thanks!

KS

 
 
 

Triggers/calculated fields MSSQL7

Post by BPMargoli » Sat, 05 Feb 2000 04:00:00


Ken,

Perhaps this will help you.

create table KenDetail
(
 PK_Detail int identity (1, 1) primary key,
 Customer  varchar (5),
 DetailAmt int
)

create table KenSummary
(
 Customer varchar (5) primary key,
 SummaryAmt int
)
go

create trigger DoSummary on KenDetail for Insert, Update
as

-- Existing Customers
update KenSummary
  set SummaryAmt = SummaryAmt + t.AmtChange
from (select i.Customer, AmtChange = sum(i.DetailAmt) - sum
(IsNull(d.DetailAmt, 0))
          from Inserted as i
          left outer join Deleted  as d
            on (i.PK_Detail = d.PK_Detail)
         group by i.Customer) as t
join KenSummary as ks
  on (ks.Customer = t.Customer)

-- New Customers
insert into KenSummary
select i.Customer, sum(i.DetailAmt)
from Inserted as i
left outer join KenSummary as ks
  on (i.Customer = ks.Customer)
where ks.Customer IS NULL
group by i.Customer

go

insert into KenDetail values ('Cust1', 10)
insert into KenDetail values ('Cust2', 20)
insert into KenDetail values ('Cust1', 30)
go

insert into KenDetail values ('Cust1', 40)
insert into KenDetail values ('Cust2', 50)
go

update KenDetail
  set DetailAmt = 15
where PK_Detail = 1
go

select * from KenSummary


Quote:> What is the recommended way to update calculated fields? I was assuming I
> would have to create triggers to sum the detail fields into a total field.
I
> cannot seem to find much information about triggers. I have a simple
table,
> and all I need to do is a few calculations and get some totals.. Anyone
have
> an example of similar trigger syntax? Perhaps a place I can find
additional
> info ?  It this what I should be using? I do not want to have the math at
> the client level.

> Thanks!

> KS


 
 
 

1. calculated field based on another calculated field

Is it possible to create a calculated field based on another calculated
field?
for example I use this sql statement :
SELECT day, day * 2 AS 'first calculated' from Travels
And I want to add new calculated field based on 'first calculated'
I get an error when I do:
SELECT day, SUM(Minutes) AS 'first calculated' , first calculated * 2 as
'second calculated' from Travels
Can someone help?
Thanks

2. SQL Builder Tool

3. Triggers and calculated fields

4. Monitoring lock tables

5. Triggers to Update Calculated Fields

6. how to run getTables ? what is catalog ? what is schemaPattern ?

7. Row-Display trigger and calculated fields

8. Licensing Question

9. Help - Calculating With a Calculated Field

10. Calculating calculated fields in 1 query

11. Storing calculated fields vs calculating on the go

12. How to re-calculate a calculated field

13. Calculated fields that don't calculate