|
|
Posted by jazpar on 10/02/65 11:51
Erland Sommarskog skrev:
> jazpar (jannoergaard@hotmail.com) writes:
> > I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> > Turnover, VAT, Netturnover). I get a file which I have to import every
> > know and then, with new data. In this file I only get values for (ID,
> > Date, Turnover and VAT). The import is working fine with the import
> > wizard.
> >
> > The problem is, that I want to have the Netturnover computed at the
> > time of insert to equal [Turnover-VAT], but I don't really know how to
> > as I'm new to these triggers.
>
> The simplest is to make NetTurnover a computed column:
>
> CREATE TABLE DebtorTurnover
> (ID int NOT NULL,
> Date datetime NOT NULL,
> Turnover decimal(10,2) NOT NULL,
> VAT decimal(10, 2) NOT NULL,
> Netturnover AS Turnover - VAT)
>
> A trigger would look like this:
>
> CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
> FOR INSERT, UPDATE AS
> UPDATE DebtorTurnover
> SET Netturnover = dt.Turnover - dt.VAT
> FROM DebtorTurnover dt
> WHERE EXISTS (SELECT *
> FROM inserted dt
> WHERE dt.ID = i.ID
>
> The "inserted" table is a virtual table that holds the inserted rows,
> or in case of an UPDATE, the update rows after the table.
>
Hi Thanks for you reply
I made the following
Table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DepTurnOver]
GO
CREATE TABLE [dbo].[DepTurnOver] (
[Year] [int] NULL ,
[Week] [int] NULL ,
[CalleId] [int] NULL ,
[ShopId] [int] NULL ,
[ItemGroupId] [int] NULL ,
[TurnOver] [real] NULL ,
[Discount] [real] NULL ,
[Qty] [real] NULL ,
[Customer] [int] NULL ,
[VAT] [real] NULL ,
[Consumption] [real] NULL,
[Netturnover] AS [Turnover]-[VAT]
) ON [PRIMARY]
GO
Trigger:
CREATE TRIGGER DepTurnover_tri ON DepTurnover
FOR INSERT, UPDATE AS
UPDATE DepTurnover
SET Netturnover = idt.Turnover - idt.VAT
FROM DepTurnover idt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.Year = idt.Year
AND dt.Week = idt.week
AND dt.CalleId = idt.CalleId
AND dt.ShopId = idt.ShopId
AND dt.ItemGroupId = idt.ItemGroupId)
But when I try to save the trigger I get the following error:
Server: Msg 271, Level 16, State 1, Procedure DepTurnover_tri, Line 3
Column 'Netturnover' cannot be modified because it is a computed
column.
Have I done anything wrong here.
Thanks in advance
BR/ Jan
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|