Problem mit Identity

[Astner Klaus]

Hallo zusammen,

weiss jemand eine Lösung auf das folgende Problem ? Ich habe folgende Situation:

Habe eine Tabelle, welche früher (MSSQL7) mit einer Identity versehen war, damals gingen soviel ich weiss GUID's noch nicht. Nun mussten wir die Identity so umstellen, dass wir auch händisch IDs vergeben können. Dafür zählen wir in einer Tabelle immer mit, und mittels eines Triggers wird bei einem Insert eine ID generiert. Das Problem ist nun, dass ich mehre Datensätze auf einmal mit einem Select in die Tabelle einfügen muss. Mit dem Trigger geht es dann nicht mehr. Kann ich irgendwie bei einer abfrage wie folgt

INSERT INTO Test (tst_id, tst_nam)
SELECT ??????? ,ts2_nam from Test2

so eine ID vergeben ? Mit einer Identity. Oder mit einer Stured Procedure, welche mir dann für jeden neuen Datensatz eine neue ID zurückgibt??

Bräuchte es wirklich dringend.

mfg

Astner Klaus

[hwoess]

Hallo Klaus,

wenn ich das richtig verstanden habe, dann sollten die records alle eine "identity" haben, die entweder händisch vergeben wird oder sonst von der DB generiert werden sollte. Und das kontrolliert dann der Trigger?
Also wenn das bis hierher stimmt und auch mehr als ein Record per select Statement eingefügt werden können sollte, dann fällt mir im Moment nur ein, eine View für das einfügen zu benutzen, auf der ein Trigger sitzt, der dann in einer Schleife einzeln die Records von der view in die eigentliche Tabelle schiebt und dabei jeweils die identity-Geschichte verwaltet.

hth,
Helmu

[Thomas Frank]

<p>Hallo Klaus,</p>
<p></p>
<p>vorne weg: ich arbeite mit MSSQL-Server 7.0, da es hier nur
AFTER-Trigger gibt, kann ich natuerlich nur zu dieser Triggerart etwas
beitragen.</p>
<p></p>
<p>Im Trigger hast du ueber die temporaere Tabelle INSERTED
Zugriff auf alle eingefuegten (bzw. geaenderten) Datensaetze (das Aequivalent
beim Loeschen heisst DELETED).</p>
<p>In deinem Fall wuerde ich einen Cursor auf diese Tabelle deklarieren:</p>
<p></p>
<p>DECLARE curInserted CURSOR FOR</p>
<p>SELECT *</p>
<p>FROM INSERTED</p>
<p></p>
<p>Dann kannst du fuer jeden Datensatz ueber eine Stored Procedure eine ID generieren und zu den Daten hinzufuegen</p>
<p></p>
<p>UPDATE Tabellenname</p>
<p>SET ID = @NewId</p>
<p>WHERE ……hier stehen die Selektionskriterien, die diesen einen Datensatz in der Tabelle
eindeutig identifizieren.</p>
<p></p>
<p></p>
<p>Ich hoffe, damit kommst du weiter.</p>
<p></p>
<p>Gruss Thomas</p&gt

[Astner Klaus]

Hallo Helmut, hallo Thomas,

ich möchte hier nochmals kurz erklären was ich genau brauche, dazu kopiere ich die Scripts die ich verwende ein.

Ich habe folgende Tabelle:

CREATE TABLE [ArtBestand] (
[abt_id] [int] NOT NULL CONSTRAINT [DF_ArtBestand_abt_id] DEFAULT (0),
[abt_art_id] [int] NOT NULL ,
[abt_lag_id] [uniqueidentifier] NULL ,
[abt_mag_id] [int] NULL ,
[abt_bestand] [float] NOT NULL CONSTRAINT [DF_ArtBestand_abt_bestand] DEFAULT (0),
[abt_yyyy] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ArtBestand] PRIMARY KEY NONCLUSTERED
(
[abt_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

welche folgenden Trigger hat:

ALTER TRIGGER ti_ArtBestand
ON dbo.ArtBestand
FOR INSERT
AS
declare
@id int,
@newid int
select @id=inserted.abt_id from inserted
if (@id < 1)
begin
select @newid=max(abt_id) + 1 from ArtBestand
update ArtBestand set abt_id=@newid where abt_id=@id

if not exists(select * from ids where ids_table='ArtBestand')
insert into ids ( ids_id, ids_table ) values ( 0, 'ArtBestand' )

update ids set ids_id=@NewID where ids_table='ArtBestand'
end

Dabei gibt es eine Tabelle ids, welche wie folgt aufgebaut ist:

CREATE TABLE [IDs] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ids_id] [int] NULL CONSTRAINT [DF_IDs_ids_id] DEFAULT (0),
[ids_user] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ids_table] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ids_sprache] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_IDs] PRIMARY KEY NONCLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_IDs] UNIQUE NONCLUSTERED
(
[ids_table]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

und einen Eintrag in ids_table hat welcher ArtBestand heisst, wobei bei ids_id die letzte vergebene ID drinnen steht.

Nun brauche ich den Trigger so modifiziert, dass er nicht nur für einen Datensatz funktioniert, sondern auch für ein

INSERT INTO ArtBestand
(abt_mag_id, abt_bestand)
SELECT xxxx ,xxxx
FROM xxxxxx

Was mir Thomas hier beschrieben hat, klingt gut, nur kann ich nicht ganz folgen, bin noch nicht so richtig fit bei Triggern.

Vielleicht könnt ihr mir da noch weiter helfen.

Grüsse

Klau

[Thomas Frank]

Hallo Klaus, <BR>
<BR>
der Trigger und die Tabellendefinition der Tabelle ArtBestand erlauben es meiner Meinung nach nicht, mehrere Datensaetze ueber INSERT INTO ... SELECT .... einzufuegen. <BR>
<BR>
MSSQL 7.0 unterstuetz AFTER-Trigger, das heisst, der Trigger wird nach dem Einfuegen ausgeloest. Da aber abt_id der Primaerschluessel ist, kann nicht erst im Trigger die abt_id bestimmt werden, wenn mehrere Datensaetze eingefuegt werden sollen, da es hier zu einer Primaerschluesselverletzung kommt!<BR>
<BR>
Mach das Einfuegen ueber eine Stored Procedure, in der du einen Cursor benutzt:<BR>
<BR>
DECLARE curInsert CURSOR FOR<BR>
SELECT xxxx, xxxx FROM xxxxx<BR>
<BR>
Dann durchlaeufst du den Cursor Zeile fuer Zeile, generierst dir die abt_id und traegst die Daten dann in die Tabelle ArtBestand ein.<BR>
<BR>
<BR>
Gruss Thoma

[hwoess]

Hallo Thomas,

bin im Moment arbeitsmäßig etwas im Stress und muß mich daher auf einfache Tips ohne codebeispiel beschränken. Aber vielleicht mal soviel zu Triggern: wenn man die ID vor dem echten Insert setzen muss, kann man natürlich keinen AFTER-Trigger verwenden, sondern einen "INSTEAD OF". Da heisst es schon mal aufpassen, denn bei Tabellen mit Fremdschlüsseln und cascadierenden Aktionen ist ein instead-of nicht möglich. Daher ein anderer Ansatz: vielleicht geht es mit einer user defined function. Dein Programm könnte dann etwa so aussehen:

INSERT INTO Test (tst_id, tst_nam) SELECT dbo.myNewID ,ts2_nam from Test2

... wobei myNewID die UDF ist. Wie man eine user defined function anlegt müsstest du dir in der OnlineHilfe anschauen - geht aber vielleicht erst ab server 8.0 ??
Würde aber auch bedeuten, dass die ID schon durch das select generiert wird und nicht erst durch den Trigger.

bye,
Helmu

[Astner Klaus]

Hallo Helmut,

das mit der UDF habe ich jetzt probiert, das Problem ist nur, dass die Function dann so auschauen sollte:

ALTER FUNCTION dbo.Function1
(
@TableName varchar(30)
)
RETURNS Int
AS
BEGIN
Declare @NewID int

if not exists(select * from ids where ids_table=@TableName)
insert into ids ( ids_id, ids_table ) values ( 0, @TableName )

select @NewID=ids_id + 1 from ids where ids_table=@TableName

update ids set ids_id=@NewID where ids_table=@TableName

RETURN @NewID
END

Nur sind Update und Insertanweisungen in Functionen nicht erlaubt. Proceduren schon. Habe probiert alles in eine Funktion zu packen, das geht aber auch nicht. Das Problem ist nämlich dass ich die eingetragenen IDs auch wieder zurückschreiben muss.

grüsse

Klau

[hwoess]

Hi Klaus,

schade, das habe ich nicht bedacht. Wird wohl ein InsteadOf-Trigger die einzige sinnvolle Möglichkeit sein. Sollte aber eigentlich kein Problem sein, da man in diesem ja das Insert mit den erweiterten oder korrigierten Werten anschließend selber nochmals aufrufen kann und dabei der InsteadOf-Trigger nicht mehr angesprochen wird. Kann mich erinnern das mal selber eingesetzt zu haben, wo bei einem record von einem Datetime feld die Zeit immer auf 0 zu setzen war.

bye,
Helmu

[Astner Klaus]

Hi Helmut,

habe mir mal folgendes zusammengebaut, aber es funktioniert nicht.

<PRE>
ALTER TRIGGER ti_ArtBestand
ON dbo.ArtBestand
FOR INSERT
AS
declare
@id int,
@newid int
DECLARE ArtBestand_Cursor CURSOR
FOR SELECT inserted.abt_id from inserted
OPEN ArtBestand_Cursor
FETCH NEXT FROM ArtBestand_Cursor
Into @id
While @@Fetch_Status = 0
begin
if (@id < 1)
begin
Execute GetNewID 'ArtBestand',@NewID
/*select @newid=max(abt_id) + 1 from ArtBestand*/
update ArtBestand set abt_id=@newid where abt_id=@id
/*if not exists(select * from ids where ids_table='ArtBestand')
insert into ids ( ids_id, ids_table ) values ( 0, 'ArtBestand' )
update ids set ids_id=@NewID where ids_table='ArtBestand'*/
FETCH NEXT FROM ArtBestand_Cursor
Into @id
end
end
CLOSE ArtBestand_Cursor
DEALLOCATE ArtBestand_Cursor
</PRE>

Wie du siehst, probiere ich hier das Update auf der Tabelle ArtBestand zu machen. Ich weiss allerdings nicht ob das richtig ist. Müsste ich das Update nicht irgendwie auf der Tabelle Inserted machen, der Datensatz wird wohl noch nicht in der Richtigen Tabelle stehen oder ?

grüsse

Klau

[hwoess]

Hallo Klaus,

du hast da ja einen AFTER-Trigger geschrieben, es wäre aber ein InsteadOf-Trigger erforderlich, in dem man erst die neue ID bestimmt und das das Insert nachholt, also ungefähr so:

ALTER TRIGGER ti_ArtBestand ON dbo.ArtBestand
INSTEAD OF INSERT AS

declare @id int
DECLARE ArtBestand_Cursor CURSOR
FOR SELECT abt_id from inserted

OPEN ArtBestand_Cursor<BR>
FETCH NEXT FROM ArtBestand_Cursor Into @id<BR>

While @@Fetch_Status = 0 begin<BR>
if (@id < 1) begin<BR>
Execute GetNewID 'ArtBestand', @id<BR>
/*select @newid=max(abt_id) + 1 from ArtBestand*/<BR>
/*if not exists(select * from ids where ids_table='ArtBestand')<BR>
insert into ids ( ids_id, ids_table ) values ( 0, 'ArtBestand' )<BR>
update ids set ids_id = @id where ids_table='ArtBestand'*/<BR>
end<BR>
-- und jetzt das Insert selber machen mir der neuen ID<BR>
-- und allen anderen Werten aus Tabelle inserted:<BR>
insert into ArtBestand select @id, feld1, feld2, ... from inserted<BR>
FETCH NEXT FROM ArtBestand_Cursor Into @id<BR>
end<BR>
CLOSE ArtBestand_Cursor <BR>
DEALLOCATE ArtBestand_Cursor<BR>

bye,
helmu

[Astner Klaus]

Hallo Helmut,

habe mich jetzt einige Zeit damit beschäftigt, aber ich kriege es nicht hin. Laut mir liegt das Problem daran, dass beim

insert into ArtBestand select @id, feld1, feld2, ... from inserted

nicht nur der eine Datensatz, sondern alle Datensätze in der Tabelle inserted selectiert werden. Kann das sein ? Das Problem ist nur das, dass dadurch dass keine ID vorhanden ist, ich auch nicht jeden Datensatz einzeln selektieren kann.

grüsse

Klau

[hwoess]

Hallo Klaus,

da hast du ganz recht, habe vergessen, dass auch mehrere records in inserted stehen können. Und dann geht's nicht. Dazu 2 Ideen:<BR>
a) entweder geht's mit WHERE CURRENT OF (also etwa: insert into ArtBestand select @id, feld1, feld2, ... from inserted where current of ArtBestand_Cursor)<BR>
oder<BR>
b) man liest per cursor nicht nur die abt_id sondern alle Felder in Variable ein und verwendet diese dann für das Insert - das geht 100%

bye,<BR>
Helmu

[Astner Klaus]

Gute Idee,

werde ich probieren

Danke

Klau