Problem z pobraniem wartości z inserta w MS SQL

1

Witam
Mam problem z pobraniem wartości generowanej przez trigger typu "instead of insert" bezpośrednio po insercie rekordu.

Przykładowa tabela:

CREATE TABLE [dbo].[test1]
(
       [id] [int] NOT NULL,
       [id2] [int] NOT NULL,
       [opis] [nchar](10) NULL,
       CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED ([id] ASC,  [id2] ASC)
)

w której pole id będące składową klucza głównego jest generowane przez trigger:

CREATE TRIGGER [dbo].[TR_TEST1] ON [dbo].[test1]
INSTEAD OF INSERT 
AS
declare @id int;
declare @id2 int;
BEGIN
SET NOCOUNT ON;
SET @id = NULL;
select @id = id, @id2 = id2 from inserted;
if@id is null OR @id = 0
BEGIN
select @id = isnull(MAX(id),0) + 1 from [dbo].[test1]  WHERE id2 = @id2;
select * INTO #inserted from inserted ;
declare _cursor CURSOR FOR select * from #inserted
for update 
OPEN _cursor;
FETCH NEXT FROM _cursor
while @@FETCH_STATUS = 0
BEGIN
update #inserted set id = @id
where CURRENT OF _cursor;
SET @id = @id + 1;
FETCH NEXT FROM _cursor
END
CLOSE _cursor;
DEALLOCATE _cursor;
insert into [dbo].[test1] select * from #inserted;
END
ELSE
insert into [dbo].[test1](id, id2, opis)  select * from inserted;
END

Założeniem jest aby pole id było generowane jako kolejny numer rekordu w obrębie identyfikatora id2 i aby można je było pobrać bezpośrednio z polecania insert.

przykład inserta z próbą wyciągnięcia wartości wygenerowanej przez triggera:

declare @tmp table(id1_tmpTable int, id2_tmpTable int)
insert into [dbo].test1(id2, opis) output inserted.id, inserted.id2 into @tmp VALUES(4, 'test');

select * from @tmp

Wartości zwracane z select * from @tmp to:

id1_tmpTable id2_tmpTable
NULL 3
Niestety zwracana wartość id jest nulowa

I tutaj moja prośba, czy może spotkał się ktoś z podobnym problemem i wie jak go rozwiązać.
Od razu nadmienię, że nie ma możliwości dodania do tabeli dodatkowego pola z unikalnym identyfikatorem, po którym mógłbym później odpytać bazę o id z insertowanego rekordu.

Możliwe jest też oczywiście wyciągnięcie ostatniego id z zapytania:

select max(id) from dbo.test1 where id2 = @id2

Jednak jest to mało eleganckie rozwiązanie.

2

nie rozumiem , za bardzo Twojej treści polecenia. Co byś chciał uzyskać ...?

Napisałeś tak:

Założeniem jest aby pole id było generowane jako kolejny numer rekordu w obrębie identyfikatora id2 i aby można je było pobrać bezpośrednio z polecania insert.

jeśli dobrze zrozumiałam to edytujemy id, zwiększając je o id2+1. Jeśli, tak to poniższy kod własnie to wykonuje.

DECLARE test1_CURSOR CURSOR FOR
SELECT id, id2 FROM test1 FOR UPDATE OF id
OPEN test1_CURSOR
FETCH NEXT FROM test1_CURSOR
INTO @id, @id2

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @id2 = @id2 + 1
    UPDATE test1 SET id = @id2 WHERE CURRENT OF test1_CURSOR
    
    FETCH NEXT FROM test1_CURSOR
    INTO @id, @id2
END

CLOSE test1_CURSOR
DEALLOCATE test1_CURSOR

SET NOCOUNT OFF

Jeśli o to chodzi to można to tez wykonać poprzez dodawanie kolejnych rekordów.
Nie rozumiem czy to chcesz własnie wykonać, jeśli dodajemy za pomocą instrukcji insert id2 np 4 to *id * ma być 5 , tak???? Nie rozumiem co to znaczy w obrębie, czy nowe id ma być większe o 1 z największego *id2 * ????

0

Przepraszam, może wszystko za bardzo zakręciłem ;) Dlatego tłumaczę jeszcze raz.

Założenie jest takie, aby dla danego id2 generowały się kolejne numery id, czyli id ma być równe od 1 do n (gdzie n ilość rekordów dla danego id2). W wynikowej tabli wygląda to tak:

id id2
1 1
2 1
3 1
4 1
5 1
1 2
2 2
3 2

czyli np. id2 jest identyfikatorem użytkownika, a id numerem kolejnego rekordu w tabeli odnosząca się do tego użytkownika.
Trigger który napisałem dokładnie to robi. Problem jest w tym, że nie mogę zwrócić wartości id w nim wygenerowanej stosując zapytanie

declare @tmp table(id1_tmpTable int, id2_tmpTable int)
insert into [dbo].test1(id2, opis) output inserted.id, inserted.id2 into @tmp VALUES(3, 'test') 
 
select * from @tmp;

Wiem też, że można uprościć samo ciało triggera, ale w tym wypadku jest to sprawa drugorzędna

2

teraz rozumiem :) :) :) :) co byś chciał uzyskać. Te zapytanie t-sql zwraca właśnie taką postać. Jeszcze brakuje dodania za pomocą TRIGGERa modyfikacji podczas dodania nowego rekordu.

SELECT	
	Ranking,
	id2	
FROM
(
	SELECT
                id2,
		ROW_NUMBER() OVER(PARTITION BY id2 ORDER BY id2 asc) as Ranking
	FROM	test1 
) podzapytanie_nieskorelowane

Przykładowy wynik:

Ranking id2
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
1 2
2 2
3 2
1 3
1 4
2 4
3 4
4 4
1

mniej więcej działa. Zastosowanie TRIGGER w aplikacjach jest mało eleganckie oraz rzadko wykorzystywane. Jeśli już jest wykorzystywane, to należy jego określić typ. Po testuj kod zobacz co się dzieje używając FOR INSERT , FOR DELETE , INSTEAD OF UPDATE itp dlatego miałeś tam wartość NULL. To bym główny mankament. FOR INSERT oznacza ze *TRIGGER * uruchomi się w momencie dodania nowego rekordu. Iteruje nowe wartości *id * zwiększając po insert, nie są rozpoczęte od 0. Popróbuj już sam :) :) :) :)

DROP TRIGGER TR_TEST1
 go
CREATE TRIGGER [dbo].[TR_TEST1] ON [dbo].[test1]
 FOR INSERT
AS
	DECLARE @id INT;
	DECLARE @id2 INT;
BEGIN
	SET NOCOUNT ON;
	SET @id = 0; 
	
DECLARE test1_CURSOR CURSOR FOR SELECT * FROM test1 
 FOR UPDATE 
OPEN test1_CURSOR
FETCH NEXT FROM test1_CURSOR
INTO @id, @id2

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @id = isnull(MAX(id),0) + 1 FROM [dbo].[test1]  WHERE id2 = @id2;
   
    UPDATE test1 SET id = @id WHERE CURRENT OF test1_CURSOR
    
    FETCH NEXT FROM test1_CURSOR
    INTO @id, @id2
END

CLOSE test1_CURSOR
DEALLOCATE test1_CURSOR
END

sprawdzenie

select * from test1 order by id2 asc

Wynik dla przykładowych wartości:

ID ID2
4 1
5 1
6 1
7 1
3 2
4 2
4 3
5 3
6 3
4 4
5 4
6 4

Zmieleniami również tabele (opcja *Design * w Microsoft SQL Server )

create table test1(
     ID         int,
     id2        int
   )
 GO
 insert into test1(id2) values (1);
1

Witam

Dzięki za odpowiedź, ale mam dwie uwagi.

  1. Jeżeli będę używał triggera for insert wówczas nie mogę mieć ustawionego pola id jako not null, co zaznaczyłaś opisując modyfikację tabeli
  2. Ustawiając pole id na allow null pozbywam się możliwości założenia na to pole klucza głównego

Myślę, że mogło zajść kolejne nieporozumienie. Nie chodzi mi o rozwiązanie samego problemu automatycznego wpisywania wartości id do tabeli w triggerze, bo to jest robione już przez mój pierwszy trigger. Zastosowałem tam trigger instead of aby móc modyfikować dane jeszcze przed ich wpisaniem do tabeli (w tej sytuacji nie musiałem wykonywać update'a na rekordach taj jak jest to robione w triggerze for insert).
A zatem reasumując, w tabeli test1 otrzymuję poprawnie wpisany rekord z odpowiednio nadaną wartością dla id, sam problem dotyczy tylko bezpośredniego zwracanie wartości id w ramach polecenia insert.

Czyli mówiąc obrazowa w moim programie robię inserta, a w odpowiedzi chcę otrzymać wartość id która została nadana przez trigger (identycznie jak gdybym zastosował select scope_identity() w przypadku pola typy identity).
Teraz wartość ta jest wpisywana to tabeli zgodnie z zamorzeniami, ale nie jest ona zwracana bezpośrednio przez inserta do mojego programu.
Oczywiście mogę ją sobie odzyskać zadjąc kolejne zapytanie i wyciągając np. max'a z id dla konkretnego id2, ale chciałem tego uniknąć i dlatego stosowałem polecenie:


DECLARE @tmp TABLE(id1_tmpTable INT, id2_tmpTable INT)
INSERT INTO [dbo].test1(id2, opis) output inserted.id, inserted.id2 INTO @tmp VALUES(2, 'test')
 
SELECT * FROM @tmp;

Rozumiem, też, że w moim triggerze instead of insert nie modyfikuję bezpośrednio tabeli logicznej inserted, tylko jej kopię #inserted, która następnie wprowadzam do tabeli, dlatego też próbując później wyciągnąć wartość id z inserted otrzymuję null. I tutaj jest własnie moje pytanie, czy da się jakoś zrobić tak, aby mimo wszystko otrzymać na zakończenie polecenia insert jak9oś wartość id.
W przypadku firebirda zrobiłbym to następująco (z zastosowaniem prawie, że identycznego triggera typu before insert):

INSERT INTO test1(id2, opis) VALUES(2, 'test') returning id
0
CREATE TABLE [dbo].[test1] (
       [id] [INT] NOT NULL,
       [id2] [INT] NOT NULL,
       [opis] [NCHAR](10) NULL,
       CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED ([id] ASC,  [id2] ASC)
)

przy takich założeniach na tabeli, nie możesz dodać np: wartości zdublowanych. Dodawałeś do *id2 * wartość , a przy id był NULL
Dlatego zmieniłam postać tabeli , aby można było dodać np jedna wartość do kolumny id2 , a id uzupełnić.

Podstawowa informacja to czy użytkownik , zawsze musi dodać dwie dane?

  1. Jeżeli będę używał triggera for insert wówczas nie mogę mieć ustawionego pola id jako not null, co zaznaczyłaś opisując modyfikację tabeli
    tak
  1. Ustawiając pole id na allow null pozbywam się możliwości założenia na to pole klucza głównego
    tak

Zastosowałem tam trigger instead of aby móc modyfikować dane jeszcze przed ich wpisaniem do tabeli
on nic nie robi , przy powyższych założeniach na tabeli test1

A zatem reasumując, w tabeli test1 otrzymuję poprawnie wpisany rekord z odpowiednio nadaną wartością dla id, sam problem dotyczy tylko bezpośredniego zwracanie wartości id w ramach polecenia insert.

tak, ale to robi CONSTRAINT ( inaczej ograniczenie )
to zapytanie potwierdza, założenia:

 select * from test1 order by id2 asc , id asc

Jest jeszcze inne rozwiązanie , wyświetlenia ostatniego dodanego rekordu, za pomocą MERGE.
Utworzenie tabeli *temp * ,gdzie będą przechowywane wartości dodane

create  TABLE tmp (
  id1_tmpTable INT, 
  id2_tmpTable INT, 
  data_dodania date
)

dodanie nowego rekordu np:

 insert into test1 values (11,3,'a');

Za pomocą instrukcji *MERGE * wyświetlenie dodanego rekordu:

declare @czas date;
select @czas = SYSDATETIME()
MERGE temp AS T
USING test1  AS S
ON S.id = T.id1_tmpTable and
     S.id2 = T.id2_tmpTable 
WHEN  not MATCHED THEN
     INSERT (id1_tmpTable,id2_tmpTable ) values ( id ,id2) 
  
WHEN NOT MATCHED BY SOURCE THEN 
  DELETE
OUTPUT  Inserted.id1_tmpTable, Inserted.id2_tmpTable ,  @czas  ;

Zwracany wynik:

id1_tmpTable id2_tmpTable (No column name)
20 3 2013-03-05
Musisz pamiętać, ze w zależności ile rekordów dodasz do tabeli w transakcji Tyle wyświetli się.
1

pytanie podstawowe - nie możesz tego opakować w stored proc?

0

Twoje rozwiązanie z wykorzystaniem polecenia MERGE wygląda dość interesująco i z chęcią je przetestuję.
Niestety w moim przypadku może to być przerost formy nad treścią.
Z tego co widzę, to mimo wszystko najłatwiejszym rozwiązaniem będzie będzie generowanie sobie id w programie i wpisywanie do tabeli, ewentualnie pobierać ostatni dodany rekord dla danego id2 (może to być oczywiście w procedurze ;) ).

Wracając jeszcze tylko do Twojej wypowiedzi dotyczącej "instead of trigger":

Zastosowałem tam trigger instead of aby móc modyfikować dane jeszcze przed ich wpisaniem do tabeli
on nic nie robi , przy powyższych założeniach na tabeli test1

oczywiście masz rację, że nic on nie robi bezpośrednio na danych w tabeli test1, modyfikuje jednak dane, które mają być do niej wpisane (tabela logiczna inserted) ustawiając odpowiednio wartość id i dopiero wówczas wpisuje je do tabeli test1.

0
Darecki napisał(a):

[...]

jeszcze raz - pytanie podstawowe - nie możesz tego opakować w stored proc?
Piszesz procedurkę, która dostaje to co ma wstawić, oblicza ID i ID2 i zwraca Id, które chcesz. Szybko, łatwo i przyjemnie. Po stronie programu masz jedno zapytanie (właściwie to wywołanie stored proc), dostajesz to co chcesz z powrotem - ID

0

Jasne, że mogę. ;)

Zrobienie tego na triggerze dawało mi pewność, że nieważne jak będzie wstawiany nowy rekord (przez bazę, czy przez program) i tak zawsze id będzie odpowiednio wyliczane. Oczywiście mogę pozostawić też trigger i dodatkowo dopisać procedurę, która będzie insertowała rekord i zwracała ostatni id dla danego id2.

1 użytkowników online, w tym zalogowanych: 0, gości: 1