Insert trigger

[jazpar]

Hi

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.

Could anyone help me I would appriciate this.
BR / Jan

[Erland Sommarskog]

jazpar (ja**********@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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jazpar]

Erland Sommarskog skrev:

jazpar (ja**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

jazpar (ja**********@hotmail.com) writes:

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:

Sorry, I was a bit brief. If you have a computed column, you don't
need the trigger at all. I included the trigger code, in case you
were not in position to change the table definition.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx