CREATE PROCEDURE sp_NextID_tri
AS
BEGIN
DECLARE @CRLF AS VARCHAR (2)
DECLARE @DDL_SQL_String AS NVARCHAR(2000) -- GesamtString
DECLARE @tabname AS VARCHAR(500) -- String für Tabellenname
DECLARE @trigger_name AS SYSNAME -- String für Triggername
DECLARE @SQL_Text AS VARCHAR (2000) -- String für Deklaration der Feldnamen
DECLARE @SQL_Text2 AS VARCHAR (2000) -- String für Feldparameter
DECLARE @SQL_Text3 AS VARCHAR (2000) -- String alle Feldname einer Tabelle
DECLARE @colname AS VARCHAR(500) -- String für Feldname
DECLARE @ColID AS VARCHAR(20)
DECLARE @Feldtyp AS VARCHAR(20) -- String für Feldtyp
DECLARE @Feldlaenge AS INTEGER -- String für Feldlaenge
SET @CRLF = CHAR(13) + CHAR(10) -- String für Zeilenumbruch
DECLARE triggerCursor CURSOR FOR -- Tabellen abrufen
SELECT DISTINCT name
FROM sysobjects
WHERE xtype='TR' AND name like 'NextID%'
FOR READ ONLY
OPEN triggerCursor -- erste Zeile abrufen
WHILE (0 = 0) BEGIN
FETCH NEXT FROM triggerCursor INTO @trigger_name
IF (@@fetch_status <> 0) BREAK
SET @DDL_SQL_String = N'DROP TRIGGER [dbo].[' + @trigger_name +']'
-- Führt eine Transact-SQL-Anweisung oder einen -Batch aus, die bzw. der mehrfach wiederverwendet werden kann oder dynamisch erstellt wurde.
-- Die Transact-SQL-Anweisung oder der -Batch können eingebettete Parameter enthalten.
EXEC SP_executesql @DDL_SQL_String
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
DECLARE tabcursor CURSOR FOR -- Tabellen abrufen
SELECT DISTINCT Tabellenname
FROM syst_datadictionary
WHERE objecttyp='U' AND Feldname like Tabellenname+'ID' AND NOT Tabellenname='Stringliste'
ORDER BY Tabellenname
FOR READ ONLY
OPEN tabcursor -- erste Zeile abrufen
WHILE (0 = 0) BEGIN
FETCH NEXT FROM tabcursor INTO @tabname
IF (@@fetch_status <> 0) BREAK
SET @DDL_SQL_String = 'CREATE TRIGGER NextID_'+@tabname +'_tri ON [dbo].' +@tabname + @CRLF +'INSTEAD OF INSERT '+ @CRLF +'AS '+ @CRLF +'BEGIN' + @CRLF +
+ ' SET NOCOUNT ON
DECLARE @NextID AS bigint
DECLARE ins'+@tabname+' CURSOR' + @CRLF +
' FOR SELECT * FROM INSERTED
DECLARE @CountRec AS bigint' + @CRLF
DECLARE colcursor CURSOR FOR -- Felder abrufen
SELECT DISTINCT ColID,Feldname,Feldtyp,Feldlaenge
FROM vw_information_schema_columns
WHERE objecttyp='U' AND Tabellenname=@tabname
FOR READ ONLY
OPEN colcursor -- erste Zeile abrufen
SET @SQL_Text = ''
SET @SQL_Text2 = ''
SET @SQL_Text3 = ''
WHILE (0 = 0) BEGIN
FETCH NEXT -- so lange es noch Zeilen gibt, wiederholen...
FROM colcursor
INTO @ColID,@colname,@Feldtyp,@Feldlaenge
IF (@@fetch_status <> 0) BREAK
SET @SQL_Text = @SQL_Text + ' DECLARE @' +@colname+ ' AS ' +@Feldtyp
IF NOT(@Feldtyp = 'bigint' or @Feldtyp = 'int')
BEGIN
SET @SQL_TEXT = @SQL_TEXT + '('+ltrim(cast(@feldlaenge as varchar(10)))+')'+ @CRLF END
ELSE BEGIN
SET @SQL_TEXT = @SQL_TEXT + @CRLF
END
SET @SQL_Text2 = @SQL_Text2 + '@'+ @colname+','
SET @SQL_Text3 = @SQL_Text3 + @colname +','
END
CLOSE colcursor
DEALLOCATE colcursor
-- String zusammenfassen
SET @SQL_Text2 = SUBSTRING(@SQL_Text2, 1 , LEN(@SQL_Text2) - 1) -- letztes Komma wird gelöscht
SET @SQL_Text3 = SUBSTRING(@SQL_Text3, 1 , LEN(@SQL_Text3) - 1) -- letztes Komma wird gelöscht
SET @DDL_SQL_String = @DDL_SQL_String + @SQL_Text + @CRLF -- GesamtString
+' SET @NextID = -1
SET @CountRec = (SELECT Count(*) FROM INSERTED WHERE '+@tabname+'ID' +' <= 0)
IF @CountRec > 0
EXEC sp_syst_GetNextID '''+@tabname+''',NULL,NULL,@CountRec,@NextID OUTPUT
IF @NextID is NULL
RAISERROR(50101,16,1)
IF (@CountRec = 0) OR (@CountRec<>0 and @NextID > 0 )
OPEN ins'+@tabname+'
FETCH NEXT FROM ins'+@tabname+'
INTO '+@SQL_Text2+'
WHILE @@FETCH_STATUS = 0
BEGIN
IF @'+@tabname+'ID' +' <= 0
BEGIN
SET @'+@tabname+'ID' +' = @NextID
SET @NextID = @NextID + 1
END
INSERT INTO '+@tabname+' ( '+@SQL_Text3+' )
SELECT '+@SQL_Text2+'
FETCH NEXT FROM ins'+@tabname+'
INTO '+@SQL_Text2+'
END
CLOSE ins'+@tabname+'
DEALLOCATE ins'+@tabname+'
END'+ @CRLF + @CRLF
EXEC sp_executesql @DDL_SQL_String -- führt batch-text aus
PRINT @DDL_SQL_String
END
CLOSE tabcursor
DEALLOCATE tabcursor
END
GO
-----------------------------------
table syst_datadictionary enthaelt die ganzen tableproberties(datatyp/table-/fieldname/max_length/checkConstraint....)
-----------------------------------
problemstelle in der procedure ist fett makiert...
und zwar hol ich mir alle tabellen die in der DD(DataDictionary) vorhanden sind, deren Feldname=Tabellename+ID ist.... so wurde der PK fuer jede Tabelle, falls sie einen besitzt, definiert.... z.B. table ADRESS hat PK = AdressID...
nun ist es do... die procedure erstellt einen trigger fuer jede tabelle die einen PK s.o. hat...
aber da den trigger auch auf eine zwischentabelle brauch, habe ich jetzt einige probz...
den z.B. die tabelle PersonAdress hat nen FK=PersonID und FK=AdressID...
aber da der cursor nur nach Tablename+ID sucht und in der table PersonAdress keinen PK gibt, wird auch logischerweise der trigger nicht angelegt....
hat jemand nen vorschlag ueberwas ich den cursor laufen lassen kann?
dachte ueber sysobjects where xtype='PK' aber dann muss ich diese wiederrum mit der onformation_schema.columns joinen... bla...
ich glaub keine kappiert was ich mein =) lol
AS
BEGIN
DECLARE @CRLF AS VARCHAR (2)
DECLARE @DDL_SQL_String AS NVARCHAR(2000) -- GesamtString
DECLARE @tabname AS VARCHAR(500) -- String für Tabellenname
DECLARE @trigger_name AS SYSNAME -- String für Triggername
DECLARE @SQL_Text AS VARCHAR (2000) -- String für Deklaration der Feldnamen
DECLARE @SQL_Text2 AS VARCHAR (2000) -- String für Feldparameter
DECLARE @SQL_Text3 AS VARCHAR (2000) -- String alle Feldname einer Tabelle
DECLARE @colname AS VARCHAR(500) -- String für Feldname
DECLARE @ColID AS VARCHAR(20)
DECLARE @Feldtyp AS VARCHAR(20) -- String für Feldtyp
DECLARE @Feldlaenge AS INTEGER -- String für Feldlaenge
SET @CRLF = CHAR(13) + CHAR(10) -- String für Zeilenumbruch
DECLARE triggerCursor CURSOR FOR -- Tabellen abrufen
SELECT DISTINCT name
FROM sysobjects
WHERE xtype='TR' AND name like 'NextID%'
FOR READ ONLY
OPEN triggerCursor -- erste Zeile abrufen
WHILE (0 = 0) BEGIN
FETCH NEXT FROM triggerCursor INTO @trigger_name
IF (@@fetch_status <> 0) BREAK
SET @DDL_SQL_String = N'DROP TRIGGER [dbo].[' + @trigger_name +']'
-- Führt eine Transact-SQL-Anweisung oder einen -Batch aus, die bzw. der mehrfach wiederverwendet werden kann oder dynamisch erstellt wurde.
-- Die Transact-SQL-Anweisung oder der -Batch können eingebettete Parameter enthalten.
EXEC SP_executesql @DDL_SQL_String
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
DECLARE tabcursor CURSOR FOR -- Tabellen abrufen
SELECT DISTINCT Tabellenname
FROM syst_datadictionary
WHERE objecttyp='U' AND Feldname like Tabellenname+'ID' AND NOT Tabellenname='Stringliste'
ORDER BY Tabellenname
FOR READ ONLY
OPEN tabcursor -- erste Zeile abrufen
WHILE (0 = 0) BEGIN
FETCH NEXT FROM tabcursor INTO @tabname
IF (@@fetch_status <> 0) BREAK
SET @DDL_SQL_String = 'CREATE TRIGGER NextID_'+@tabname +'_tri ON [dbo].' +@tabname + @CRLF +'INSTEAD OF INSERT '+ @CRLF +'AS '+ @CRLF +'BEGIN' + @CRLF +
+ ' SET NOCOUNT ON
DECLARE @NextID AS bigint
DECLARE ins'+@tabname+' CURSOR' + @CRLF +
' FOR SELECT * FROM INSERTED
DECLARE @CountRec AS bigint' + @CRLF
DECLARE colcursor CURSOR FOR -- Felder abrufen
SELECT DISTINCT ColID,Feldname,Feldtyp,Feldlaenge
FROM vw_information_schema_columns
WHERE objecttyp='U' AND Tabellenname=@tabname
FOR READ ONLY
OPEN colcursor -- erste Zeile abrufen
SET @SQL_Text = ''
SET @SQL_Text2 = ''
SET @SQL_Text3 = ''
WHILE (0 = 0) BEGIN
FETCH NEXT -- so lange es noch Zeilen gibt, wiederholen...
FROM colcursor
INTO @ColID,@colname,@Feldtyp,@Feldlaenge
IF (@@fetch_status <> 0) BREAK
SET @SQL_Text = @SQL_Text + ' DECLARE @' +@colname+ ' AS ' +@Feldtyp
IF NOT(@Feldtyp = 'bigint' or @Feldtyp = 'int')
BEGIN
SET @SQL_TEXT = @SQL_TEXT + '('+ltrim(cast(@feldlaenge as varchar(10)))+')'+ @CRLF END
ELSE BEGIN
SET @SQL_TEXT = @SQL_TEXT + @CRLF
END
SET @SQL_Text2 = @SQL_Text2 + '@'+ @colname+','
SET @SQL_Text3 = @SQL_Text3 + @colname +','
END
CLOSE colcursor
DEALLOCATE colcursor
-- String zusammenfassen
SET @SQL_Text2 = SUBSTRING(@SQL_Text2, 1 , LEN(@SQL_Text2) - 1) -- letztes Komma wird gelöscht
SET @SQL_Text3 = SUBSTRING(@SQL_Text3, 1 , LEN(@SQL_Text3) - 1) -- letztes Komma wird gelöscht
SET @DDL_SQL_String = @DDL_SQL_String + @SQL_Text + @CRLF -- GesamtString
+' SET @NextID = -1
SET @CountRec = (SELECT Count(*) FROM INSERTED WHERE '+@tabname+'ID' +' <= 0)
IF @CountRec > 0
EXEC sp_syst_GetNextID '''+@tabname+''',NULL,NULL,@CountRec,@NextID OUTPUT
IF @NextID is NULL
RAISERROR(50101,16,1)
IF (@CountRec = 0) OR (@CountRec<>0 and @NextID > 0 )
OPEN ins'+@tabname+'
FETCH NEXT FROM ins'+@tabname+'
INTO '+@SQL_Text2+'
WHILE @@FETCH_STATUS = 0
BEGIN
IF @'+@tabname+'ID' +' <= 0
BEGIN
SET @'+@tabname+'ID' +' = @NextID
SET @NextID = @NextID + 1
END
INSERT INTO '+@tabname+' ( '+@SQL_Text3+' )
SELECT '+@SQL_Text2+'
FETCH NEXT FROM ins'+@tabname+'
INTO '+@SQL_Text2+'
END
CLOSE ins'+@tabname+'
DEALLOCATE ins'+@tabname+'
END'+ @CRLF + @CRLF
EXEC sp_executesql @DDL_SQL_String -- führt batch-text aus
PRINT @DDL_SQL_String
END
CLOSE tabcursor
DEALLOCATE tabcursor
END
GO
-----------------------------------
table syst_datadictionary enthaelt die ganzen tableproberties(datatyp/table-/fieldname/max_length/checkConstraint....)
-----------------------------------
problemstelle in der procedure ist fett makiert...
und zwar hol ich mir alle tabellen die in der DD(DataDictionary) vorhanden sind, deren Feldname=Tabellename+ID ist.... so wurde der PK fuer jede Tabelle, falls sie einen besitzt, definiert.... z.B. table ADRESS hat PK = AdressID...
nun ist es do... die procedure erstellt einen trigger fuer jede tabelle die einen PK s.o. hat...
aber da den trigger auch auf eine zwischentabelle brauch, habe ich jetzt einige probz...
den z.B. die tabelle PersonAdress hat nen FK=PersonID und FK=AdressID...
aber da der cursor nur nach Tablename+ID sucht und in der table PersonAdress keinen PK gibt, wird auch logischerweise der trigger nicht angelegt....
hat jemand nen vorschlag ueberwas ich den cursor laufen lassen kann?
dachte ueber sysobjects where xtype='PK' aber dann muss ich diese wiederrum mit der onformation_schema.columns joinen... bla...
ich glaub keine kappiert was ich mein =) lol
