May 5, 2008 at 5:47 pm
I have 2 tables (TableA and TableB) with various fields. I want to build a new table (TableZ), that multiplies two fields (one each table) when one event occurs. Like:
TableA
#sell
description
price
TableB
#salesman
percentage
TableZ
salesman
sell
percentagetotal ((= tablea.price x tableb.percentage) when salesman and sell have values, automatically the value of percentagetotal is fielded!!!
Is this possible? This is a simple example that i want to do, but i think its enough, but i can be more specific. Thanks for the help!
May 5, 2008 at 8:51 pm
as far as i know you can't use different tables for computed column. columns has to be in the same table as per microsoft:
computed_column_expression
Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. The column is computed from an expression that uses other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or contain alias data types.
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.
Note:
Each row in a table can have different values for columns that are involved in a computed column; therefore, the computed column may not have the same value for each row.
Based on the expressions that are used, the nullability of computed columns is determined automatically by the SQL Server 2005 Database Engine. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows also produce NULL results. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL with the check_expression constant, where the constant is a nonnull value substituted for any NULL result. REFERENCES permission on the type is required for computed columns based on common language runtime (CLR) user-defined type expressions.
I m sure there is a way around this.
May 5, 2008 at 11:17 pm
I think you can find a way out by using a trigger. Check the following scenario.
CREATE TABLE [dbo].[test1](
[salesid] [int] NOT NULL,
[price] [int] NULL,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[salesid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[test2](
[salesmanid] [int] NOT NULL,
[salesPercentage] [int] NULL,
CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED
(
[salesmanid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[test3](
[salesid] [int] NULL,
[salesmanid] [int] NULL,
[reqResult] [int] NULL
) ON [PRIMARY]
GO
USE [testanam]
GO
ALTER TABLE [dbo].[test3] WITH CHECK ADD CONSTRAINT [FK_test3_test1] FOREIGN KEY([salesid])
REFERENCES [dbo].[test1] ([salesid])
GO
ALTER TABLE [dbo].[test3] WITH CHECK ADD CONSTRAINT [FK_test3_test2] FOREIGN KEY([salesmanid])
REFERENCES [dbo].[test2] ([salesmanid])
INSERT INTO test1 VALUES (1, 101)
INSERT INTO test1 VALUES (2, 102)
INSERT INTO test1 VALUES (3, 103)
INSERT INTO test2 VALUES (11, 10)
INSERT INTO test2 VALUES (12, 20)
INSERT INTO test2 VALUES (13, 30)
CREATE TRIGGER [inscomp]
ON [dbo].[test3]
AFTER INSERT
AS
BEGIN
Update a
set reqResult = b.price * c.salesPercentage
from test3 a
inner join inserted i on a.salesid = i.salesid and a.salesmanid = i.salesmanid
inner join test1 b on b.salesid = a.salesid
inner join test2 c on c.salesmanid = a.salesmanid
END
INSERT INTO test3 (salesid, salesmanid) VALUES (1, 11)
INSERT INTO test3 (salesid, salesmanid) VALUES (2, 12)
SELECT * FROM test1
SELECT * FROM test2
SELECT * FROM test3
salesid price
----------- -----------
1 101
2 102
3 103
(3 row(s) affected)
salesmanid salesPercentage
----------- ---------------
11 10
12 20
13 30
(3 row(s) affected)
salesid salesmanid reqResult
----------- ----------- -----------
1 11 1010
2 12 2040
(2 row(s) affected)
May 6, 2008 at 3:29 am
Thanks to all! I'm clarified!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply