Prev: Insert and Delete
Next: Yellowfin 5.0 release
From: rek on 17 Apr 2010 11:16
hi friends,
can any one help me pl!!
m using visual studio 2005 n ms sql server 2005 as backend.actually, i
have created a table with a column name as rid,total,cur_value and
balance.
In balance column, this total value minus(-) cur_value is stored.
1)now,after each transaction i wanna store this balance value into the
total column again automatically…how to do it?
2)how to increment the rid value lik (1,2,3,4,5…)after each
transaction is executed?
3) how to get a particular value in a table using aggregate function
as condition ?
4)is it possible to show error/alert msg if it exceeds the given
condition ?

thank you very much..
From: Erland Sommarskog on 18 Apr 2010 17:34
rek (pooh4rsoft(a)gmail.com) writes:
> can any one help me pl!!
> m using visual studio 2005 n ms sql server 2005 as backend.actually, i
> have created a table with a column name as rid,total,cur_value and
> balance.
> In balance column, this total value minus(-) cur_value is stored.
> 1)now,after each transaction i wanna store this balance value into the
> total column again automatically�how to do it?

I'm not sure that I understand the business rules. In a table with
a balance column, I would expect something for which we are keeping the
balance off, for instance an account. But I cannot see anything here.
Nor do I know what rid stands for. Or "total" for that matter.

OK, so given the meagreness of the column, this seems to be some form
of training exercise, but that may actually make things more difficult
as there is no clear definition of what you are aiming at.

> 2)how to increment the rid value lik (1,2,3,4,5�)after each
> transaction is executed?

Either you make the column an IDENTITY column, in which case SQL Server
would handle the increment, at the price that you will have to accept
gaps. Or something like:

DECLARE @rid int
BEGIN TRANSACTION

SELECT @rid = coalesce(MAX(rid), 0) + 1 FROM tbl WITH (UPDLOCK)

INSERT tbl (rid, ...)
VALUES (@rid, ...)

COMMIT TRANSACTION


> 3) how to get a particular value in a table using aggregate function
> as condition ?

Could you clarify what you are looking for?

> 4)is it possible to show error/alert msg if it exceeds the given
> condition ?

Again, could you clarify what you are looking for?

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Insert and Delete
Next: Yellowfin 5.0 release