History table

Hi I wanted to create a history table, but it doesn′t really works:
CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
FOR INSERT, UPDATE AS
INSERT into dbo.Helpdesk_History
(
Helpdesk_History.Computer_Idn,
Helpdesk_History.DeviceName,
Helpdesk_History.InsertDate,
Helpdesk_History.ProblemTitle,
Helpdesk_History.Duration,
Helpdesk_History.ProblemDetails,
Helpdesk_History.ProblemSolution,
Helpdesk_History.CallDispatcher,
Helpdesk_History.Responsible,
Helpdesk_History.Status
)
SELECT
Helpdesk.Computer_Idn,
Helpdesk.DeviceName,
Helpdesk.InsertDate,
Helpdesk.ProblemTitle,
Helpdesk.Duration,
Helpdesk.ProblemDetails,
Helpdesk.ProblemSolution,
Helpdesk.CallDispatcher,
Helpdesk.Responsible,
Helpdesk.Status
from Helpdesk
With my trigger all values will be written in the helpdesk_history table.
But I want that only new oder changed collums will should be insert in the
history table.
Any idea ?
Thx
WolfgangI think this is what you want
CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
FOR INSERT, UPDATE AS
INSERT into dbo.Helpdesk_History
(
Helpdesk_History.Computer_Idn,
Helpdesk_History.DeviceName,
Helpdesk_History.InsertDate,
Helpdesk_History.ProblemTitle,
Helpdesk_History.Duration,
Helpdesk_History.ProblemDetails,
Helpdesk_History.ProblemSolution,
Helpdesk_History.CallDispatcher,
Helpdesk_History.Responsible,
Helpdesk_History.Status
)
SELECT
d.Computer_Idn,
d.DeviceName,
d.InsertDate, -- this would probably be getdate() if you want
today's date
d.ProblemTitle,
d.Duration,
d.ProblemDetails,
d.ProblemSolution,
d.CallDispatcher,
d.Responsible,
d.Status
from deleted d
http://sqlservercode.blogspot.com/|||Hi in case of update this works perfect,
but if I insert a new record the complete table helpdesk will be copied to
the helpdesk_history table.
Any idea?
thx
Wolfgang
"SQL" wrote:

> I think this is what you want
> CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk]
> FOR INSERT, UPDATE AS
> INSERT into dbo.Helpdesk_History
>
> (
> Helpdesk_History.Computer_Idn,
> Helpdesk_History.DeviceName,
> Helpdesk_History.InsertDate,
> Helpdesk_History.ProblemTitle,
> Helpdesk_History.Duration,
> Helpdesk_History.ProblemDetails,
> Helpdesk_History.ProblemSolution,
> Helpdesk_History.CallDispatcher,
> Helpdesk_History.Responsible,
> Helpdesk_History.Status
> )
> SELECT
>
> d.Computer_Idn,
> d.DeviceName,
> d.InsertDate, -- this would probably be getdate() if you want
> today's date
> d.ProblemTitle,
> d.Duration,
> d.ProblemDetails,
> d.ProblemSolution,
> d.CallDispatcher,
> d.Responsible,
> d.Status
>
> from deleted d
>
> http://sqlservercode.blogspot.com/
>|||When you do an insert the deleted table should not be available
Is there possible another trigger on the table that updates after
inserting?
http://sqlservercode.blogspot.com/|||Hi Wolfgang !
That would mean to build a block of code, comparing each column to the
new one, and keeping the information that this value was changed and
then issueing something like a dynamic sql statement, to only store the
information that were changed.
HTH, Jens Suessmeyer.|||Hi,
yes there is another trigger:
CREATE TRIGGER [Computer_idn] ON dbo.Helpdesk
FOR INSERT
AS
UPDATE Helpdesk SET Computer_idn =
(Select Computer.Computer_idn
from Computer
where
computer.devicename = helpdesk.devicename
and computer.SWLastScanDate =
(SELECT MAX(computer.SWLastScanDate) FROM computer))
This trigger updates the Computer_Idn ...and with this trigger it updates
the whole table. How can I change this trigger that only the Computer_Idn of
the new inserted value will be updated ?
Thanks
Wolfgang
"SQL" wrote:

> When you do an insert the deleted table should not be available
> Is there possible another trigger on the table that updates after
> inserting?
> http://sqlservercode.blogspot.com/
>|||On Wed, 9 Nov 2005 00:10:11 -0800, Wolfgang Dausend wrote:

>Hi,
>yes there is another trigger:
>
>CREATE TRIGGER [Computer_idn] ON dbo.Helpdesk
>FOR INSERT
>AS
>UPDATE Helpdesk SET Computer_idn =
>(Select Computer.Computer_idn
>from Computer
>where
>computer.devicename = helpdesk.devicename
>and computer.SWLastScanDate =
>(SELECT MAX(computer.SWLastScanDate) FROM computer))
>
>This trigger updates the Computer_Idn ...and with this trigger it updates
>the whole table. How can I change this trigger that only the Computer_Idn o
f
>the new inserted value will be updated ?
Hi Wolfgang,
Add
WHERE EXISTS
(SELECT *
FROM inserted
WHERE inserted.WhateverYourKeyIs = Helpdesk.WhateverYourKeyIs)
at the end of the UPDATE statement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

History of data for documents

Hello,
I have following situation:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[CountryId] [uniqueidentifier] NOT NULL ,
[StreetName] [nvarchar] (100) NOT NULL ,
[StreetNo] [nvarchar] (10) NOT NULL ,
[LocalNo] [nvarchar] (10) NOT NULL ,
[PostalCode] [nvarchar] (20) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OwnerContractorId] [uniqueidentifier] NOT NULL ,
[TargetContractorId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL ,
[OrderId] [uniqueidentifier] NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[Quantity] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[CultureName] [nvarchar] (20) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
The Problem is:
Order - needs information about contractor and his address (address is
related to the contractor by one-to-many
relation). Furthermore, the order needs information about products (its
names, which are stored in
separate table - each product can have several names, depending on language)
.
If a name of a product would change, it will be changed in all orders. But
order is the document and I need to
keep it as it was at the moment of creation (constractor data - names,
address etc. , product data - names etc.).
To do this, I have to design some kind of history of data connection to each
other. It is bad situation to me,
when I change address of some contractor - all orders will have changed
addresses, the proper data connections
will collapse.
My idea is to copy all related data for the record which is edited. For
example if an address for contractor is
changed, the new contractor record is created with new id (guid) and the
same symbol (is unique for all active
records) (old one have flag "history" for example - is deactivated) and the
new address is related to this new contractor record. The same story applies
when a name of product is edited.
This mechanism lets to store correct data for each order in the database -
all documents(orders) are connected to the same data as at the moment of
their creation.
Reports in this case are performed by queries operationg in the "Symbol" of
the contractor - this gives the
possibility to find all orders of one contractor - each having correct
address for the moment of creation of
order.
My question is: is this design corresponding to the "rules of art" somehow?
What are your ideas for solving such problems?
I will be thankful for your opinions
Adam Rozycki & friends>> My question is: is this design corresponding to the "rules of art"
No
I am not sure where you got the idea of using a globally unique identifier
type for every key in your tables.
We all work within existing computational constraints; so no business
segment requires a machine generated global identifier for a table unless
you are researching on such values. Other than the "" factor, abuse &
hype, there is nothing simple, pragmatic or meaningful about using a
uniqueidentifier column as a key for Orders or Products table as in your
situation.
One approach to your problem, based on your narratives, can be like:
CREATE TABLE Orders (
Order_nbr INT NOT NULL PRIMARY KEY,
Product_id INT NOT NULL,
REFERENCES Products ( Product_id )
Contractor_id INT NOT NULL
Address_id INT NOT NULL,
REFERENCES Contractors ( Contractor_id, Address_id )
.. ) ;
CREATE TABLE Contractors (
Contractor_id INT NOT NULL,
Address_id INT NOT NULL,
REFERENCES Addresses ( Address_id ) ,
Name...
PRIMARY KEY ( Contractor_id, Address_id )
) ;
CREATE TABLE Addresses (
Address_id INT NOT NULL PRIMARY KEY,
Address VARCHAR( 40 ) NOT NULL,
City_state_zip VARCHAR( 40 ) NOT NULL,
UNIQUE ( Address, City_state_zip )
.. ) ;
CREATE TABLE Products (
Product_id INT NOT NULL PRIMARY KEY,
Product_name ...
) ;
To keep track of history of change in Product names use another table with
temporal datatypes like:
CREATE TABLE ProductHistory (
Product_id INT NOT NULL,
Product_name VARCHAR ( 100 ) NOT NULL,
Assigned_date DATETIME NOT NULL,
Withdrawn_date DATETIME NOT NULL,
..
PRIMARY KEY ( Product_id, Assigned_date )
CHECK ( Withdrawn_date >= Assigned_date )
);
The same approach can be used for changes in other attributes like symbols
which you mentioned as well.
Anith|||
"Anith Sen" wrote:

> I am not sure where you got the idea of using a globally unique identifier
> type for every key in your tables.
My idea was to single keys instead of complex ones.

> We all work within existing computational constraints; so no business
> segment requires a machine generated global identifier for a table unless
> you are researching on such values.
The reason why I used GUIDs is that this database has to be replicable and
data exchangable with several separate databases. There is to be one master
DB and several slave DBs. Since data can be added in some independent places
- I think I need to use the global identifiers.
My problem is that I have to have a data corresponding to Order, just the
same as it was at the moment of creating of Order. I cannot have situation
when changed contractor data changes data in all orders.
The database has to store names for products in several languages, since
that I cannot put all information about products in one table.
I would like to achieve rather some sort of document revision than history
of action on documents.
Adam|||>> My idea was to single keys instead of complex ones.
Good. Simple keys are a recommended consideration for a primary key.
However, having a uniqueidentifier in a table as the only key in your table
does nothing for entity identification, which is the main purpose of a key
in the first place.
Not necessarily. You could opt for any arbitrary namespace to determine
independent databases distributed over different servers. While
uniqueidentifier type guarantees the value to be unique globally, it cannot
guarantee the uniqueness of the corresponding entity.
For instance, a customer by name Adam in a table in database A cannot be
distinguished from another customer by same name Adam in similar table in a
replicable database B or even in the same table in the same database, just
by virtue of arbitrary GUIDs alone. All you'll have is duplicated entries of
Adam in the table with different GUIDs associated with them. How do you
identify the row corresponding to Adam? How will you enforce entity
integrity? How do you track down an alleged error, for instance in data
entry? Can you use GUIDs for referencing keys reliably without cascading
changes?
It is mostly hard to provide any specific meaningful suggestions here. While
you are familiar with your business model regarding the orders, customers,
languages, symbols, revisions etc., others in this newsgroup have no clue on
what they are or how they are related. You did provide a set of CREATE TABLE
statements without any keys, constraints, references etc. however databases
cannot be designed based on such. Esp. when only a couple of lines of
narrative are provided, there is a high chance that the overall business
model and rules are miscommunicated, misrepresented and/or misunderstood.
As a general suggestion, your approach to use GUIDs all over the table as
primary keys with no identifying attribute seems inherently flawed. However,
if you have made provisions for entity identification using UNIQUE NOT NULL
constraints, perhaps you might be able to work it out to some extent.
Consider using temporal datatypes for tracking historical information unless
you are using them already.
Also a few general design rules of thumb, if it helps:
* When you have a one-to-one relationship between two entity types, unless
there are any non-dependency preserving relationships, you may represent
them in a single table.
* When you have a many-to-one relationship between two entity types, you
should use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have a many-to-many relationship between two or more entity
types, you should introduce an "association" table which reduces the schema
to two or more many-to-one relationships on each table representing these
entity types.
Anith|||
"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creationof tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Is it now possible that you provide some judge of my record-history idea or
provide some other ideas how to perform this correctly?
Adam|||"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creation of tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Can you now provide some judgement of my record-history idea or provide some
other ideas how could it be performed correctly?
Adam

Hierarchy

Hello all,
I have the following table structure (legacy, not my design :-D ):
CREATE TABLE [dbo].[hierarchy]
([idproduct] [int] NOT NULL ,
[name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[idlevel0] [int] NOT NULL ,
[name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel1] [int] NULL ,
[name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel2] [int] NULL ,
[name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
)
ON [PRIMARY]
GO
Some data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
idproduct field: id of a product
name= name o a product
idlevel0 = id of the root hierarchy
name0 = name of the root hierarchy
idlevel1 = id of the second node in the hierarchy
name1 = name of the second node in the hierarchy
idlevel2 = id of the third node in the hierarchy
name2 = name of the third node in the hierarchy
basically, there can be only three levels in the tree but as much brances as
the number of records are.
what I want is to reformat the hierarchy this way:
nodeid / nodename/ parentid
any hints on this?
Kind regards,
TudorSorry, pressed send before i actually finished:
So, further data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1001, 'Product2', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1003, 'Product3', 1, 'root node', 10,
'Intermediate10', 101, 'Intremediate201')
insert into hierarchy values (1004, 'Product4', 1, 'root node', 11,
'Intermediate11', 100, 'Intremediate202')
insert into hierarchy values (1005, 'Product5', 1, 'root node', 12,
'Intermediate12', 102, 'Intremediate202')
I would like the data to be stored as this:
nodeid nodename parentid
1 rootnode
2 intermediate10 1
3 intermediate200 2
4 intermediate200 2
5 intermediate11 1
etc...
Thanks,
Tudor
"Tudor" <tudor@.hopscotch.com> wrote in message
news:eeN5qxbQGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have the following table structure (legacy, not my design :-D ):
> CREATE TABLE [dbo].[hierarchy]
> ([idproduct] [int] NOT NULL ,
> [name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [idlevel0] [int] NOT NULL ,
> [name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel1] [int] NULL ,
> [name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel2] [int] NULL ,
> [name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> )
> ON [PRIMARY]
> GO
> Some data:
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
>
> idproduct field: id of a product
> name= name o a product
> idlevel0 = id of the root hierarchy
> name0 = name of the root hierarchy
> idlevel1 = id of the second node in the hierarchy
> name1 = name of the second node in the hierarchy
> idlevel2 = id of the third node in the hierarchy
> name2 = name of the third node in the hierarchy
> basically, there can be only three levels in the tree but as much brances
> as the number of records are.
> what I want is to reformat the hierarchy this way:
> nodeid / nodename/ parentid
> any hints on this?
> Kind regards,
> Tudor
>
>
>

Hierarchical table (count)

Hello

for MS SQL 2000 i am having :

CREATE TABLE [dbo].[Items](
[id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_ItemsSup] [int] NULL,
[Name] [nvarchar] (100) NOT NULL,
[SubItems][int] DEFAULT (0)
) ON [PRIMARY]

with :
UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1
I get how many subItems has Item = 1

how can I update the Column SubItems (for each row) ?
to get the total of subItems for each Item ?

thank youupdate Items set subitems = (select count(*) from items where Id_ItemsSup = A.Id_ItemsSup) from Items A|||it works fine

thank you