Insert trigger

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 / Janjazpar (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.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||jazpar (jannoergaard@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insert statement which uses a return value from an SP as an insert value

I'm quite stuck with this:

I have an import table called ReferenceMatchingImport which contains
data that has been sucked from a data submission. The contents of
this table have to be imported into another table ExternalReference
which has various foreign keys.

This is simple but one of these keys says that the value in
ExternalReference.CompanyRef must be in the CompanyReference table.
Of course if this is an initial import then it will not be so as part
of my script I must insert a new row into CompanyReference and
populate ExternalReference.CompanyRef with the identity column of this
table.

I thought a good idea would be to use an SP which inserts a new row
and returns @.@.Identity as the value to insert. However this doesn't
work as far as I can tell. Is there a approved way to perform this
sort of opperation? My code is below.

Thanks.

ALTER PROCEDURE SP00ReferenceMatchingImport
AS

/*
Just some integrity checking going on here
*/

INSERT ExternalReference
(
ExternalSourceRef,
AssetGroupRef,
CompanyUnitRef,
EntityTypeCode,
CompanyRef, --this is the unknown ref which is returned by the sp
ExternalReferenceTypeCode,
ExternalReferenceCompanyReferenceMapTypeCode,
StartDate,
EndDate,
LastUpdateBy,
LastUpdateDate
)
SELECT rmi.ExternalDataSourcePropertyRef,
rmi.AssetGroup,
rmi.CompanyUnit,
rmi.EntityType,
SP01InsertIPDReference rmi.EntityType, --here I'm trying to run the
sp so that I can use the return value as the insert value
1,
1,
GETDATE(),
GETDATE(),
'RefMatch',
GETDATE()
FROM ReferenceMatchingImport rmi
WHERE rmi.ExternalDataSourcePropertyRef NOT IN (
SELECT ExternalSourceRef
FROM ExternalReference
)Chris Gilbert (chris_q2@.hotmail.com) writes:
> I have an import table called ReferenceMatchingImport which contains
> data that has been sucked from a data submission. The contents of
> this table have to be imported into another table ExternalReference
> which has various foreign keys.
> This is simple but one of these keys says that the value in
> ExternalReference.CompanyRef must be in the CompanyReference table.
> Of course if this is an initial import then it will not be so as part
> of my script I must insert a new row into CompanyReference and
> populate ExternalReference.CompanyRef with the identity column of this
> table.
> I thought a good idea would be to use an SP which inserts a new row
> and returns @.@.Identity as the value to insert. However this doesn't
> work as far as I can tell. Is there a approved way to perform this
> sort of opperation? My code is below.

The best strategy is to use a staging table, and it seems that
ReferenceMatchingImport is this sort of table. So add a column to this
table with the CompanyRef, and before you insert into the
ExternalReference, you create the new company references, and then update
that value in ReferenceMatchingImport.

There are other possible techniques as well, but all boils down to that
you have to get the references before you INSERT. You cannot INSERT into
one table and then with the left hand insert into another table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns955DF29863C85Yazorman@.127.0.0.1>...
> The best strategy is to use a staging table, and it seems that
> ReferenceMatchingImport is this sort of table. So add a column to this
> table with the CompanyRef, and before you insert into the
> ExternalReference, you create the new company references, and then update
> that value in ReferenceMatchingImport.
> There are other possible techniques as well, but all boils down to that
> you have to get the references before you INSERT. You cannot INSERT into
> one table and then with the left hand insert into another table.

Thankyou Erland,

That was enough to make me give up on the approach above and resign
myself to using a cursor. I have done what you suggested and added a
column to the staging table and run the import as above but after
creating the CompanyRefs. The code is below for anyone refering to
this thread.

Erland, is this the approach you were suggesting? Is there a way to
avoid using a cursor?

Thanks, Chris

DECLARE @.ExternalDataSourcePropertyRef VARCHAR(250)
DECLARE @.CompanyRef BIGINT

DECLARE rm_cursor CURSOR FOR
SELECT rmi.ExternalDataSourcePropertyRef
FROM ReferenceMatchingImport rmi
WHERE rmi.CompanyRef IS NULL

OPEN rm_cursor
FETCH NEXT FROM rm_cursor INTO @.ExternalDataSourcePropertyRef

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC @.CompanyRef = SP01InsertCompanyReference 5
UPDATE ReferenceMatchingImport
SET CompanyRef = @.CompanyRef
WHERE ExternalDataSourcePropertyRef = @.ExternalDataSourcePropertyRef

FETCH NEXT FROM rm_cursor INTO @.ExternalDataSourcePropertyRef
END

CLOSE rm_cursor
DEALLOCATE rm_cursor|||Chris Gilbert (chris_q2@.hotmail.com) writes:
> That was enough to make me give up on the approach above and resign
> myself to using a cursor. I have done what you suggested and added a
> column to the staging table and run the import as above but after
> creating the CompanyRefs. The code is below for anyone refering to
> this thread.
> Erland, is this the approach you were suggesting? Is there a way to
> avoid using a cursor?

Probably. Although you make things difficult with using an IDENTITY
column on the CompanyRef table. That makes it more difficult to insert
many rows and know what the keys are. Better is to have artificial key
that you roll your own. Then you could do something like:

CREATE TABLE #extrefs (externalref whatever_type NOT NULL PRIMARY KEY,
ident int IDENTITY UNIQUE,
internalref int NULL)

INSERT #extrefs (externalref)
SELECT DISTINCT externalref FROM ReferenceMatchingImport

SELECT @.maxid = coalesce(MAX(id), 0) FROM RefTable

INSERT RefTable(id, externalref)
SELECT @.maxid + ident, externalref
FROM #extrefs e
WHERE NOT EXISTS (SELECT *
FROM RefTable r
WHERE r.externalref = e.externalref)

UPDATE #extrefs
SET internalref = r.id
FROM #extrefs e
JOIN RefTable r ON r.externalref = e.externalref

Here I am making wild assumptions on how you tables looks like, since I
don't have that information.

> EXEC @.CompanyRef = SP01InsertCompanyReference 5

Side note: in my opinion the return value of a stored procedure should
only be used to indicate status. To return data, use output parameters
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Insert Query - Basic Question

Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.

I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.

What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.

Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,

Regards

You can update all that does not have a siteref set with something like this

update t
set siteref = left(t.unitref, 2)
from dbo.table t
where t.siteref is null and len(t.unitref) > 1;

This will find all records in table where siteref is not set and has a unitref with 2 or more characters (otherwise the left(, 2) will be illegal) and set it as the siteref for the row

EDIT: Might have misunderstood you...