Trigger logujący - kto i co zmienił?

Witam!
Tworzę właśnie trigger który ma "logować" zmiany w wybranych tabelach, a mianowicie:
kto, co, z jakiej wartości, na jaką wartość, oraz kiedy zostało zmienione.

Utworzyłem więc tabele, do której będę zapisywał informacje:

CREATE TABLE [dbo].[zmiana_tw_towar](
	[id] [bigint] NOT NULL,
	[kto] [varchar](max) NULL,
	[co] [varchar](max) NULL,
	[z_czego] [varchar](max) NULL,
	[na_co] [varchar](max) NULL,
	[kiedy] [datetime] NULL,
 CONSTRAINT [PK_zmiana_tw_towar] PRIMARY KEY CLUSTERED 

No i oczywiście wiem już jak pobrać kto coś zmienia, teraz pozostaje mi problem, co zostało zmienione i na co?
Prosił bym o radę w jaki sposób pobrać do triggera nazwę tabeli i kolumny jaka została zmieniona np. przez UPDATE?
Był bym również bardzo wdzięczy za przykładowy kod ;)

Z góry dziękuje za zainteresowanie tematem i wskazówki

(chyba, bo nie znam na tyle mssqla) nie da się napisać jednego triggera dla wszystkich tabel - musisz dla każdej tabeli napisać osobny trigger, który będzie pisał do tej tabeli

alez oczywiście - bo triger jest przypisany zawsze do tabeli - a przeczytałem mój post powyżej już poprawiam babola ;)
Oczywiście wpadł tu błąd, chodziło dokładnie:
Prosił bym o radę w jaki sposób pobrać do triggera nazwę (numer)wiersza i kolumny jaka została zmieniona np. przez UPDATE?

to Ci powinno rozjaśnić http://www.mssqltips.com/sqlservertip/2342/understanding-sql-server-inserted-and-deleted-tables-for-dml-triggers/

juniorchat napisał(a):

Tworzę właśnie trigger który ma "logować" zmiany w wybranych tabelach, a mianowicie:
kto, co, z jakiej wartości, na jaką wartość, oraz kiedy zostało zmienione.

To, o co Ci chodzi nie nazywa się "logowanie" tylko "audyt". Zazwyczaj też nie zapisuje się tego w takim formacie, jak Ty proponujesz, lecz robi się kopię modyfikowanego rekordu wraz z dodatkowymi danymi (data, id użytkownika) i zapisuje do oddzielnej tabeli.

Na marginesie - niekoniecznie trzeba to robić za pomocą triggerów, istnieją dość wygodne rozwiązania programistyczne, np. Envers.

Mozesz do tego tematu podejsc troche inaczej.
Zeby bylo to efektywne musisz wiedziec co chcesz hostoryzowac (czyli zmiany jakich pol Ci interesuja).
Musisz miec tez tabele w ktorej bedziesz przechowywal dane zmienione.

Proponuje utowrzyc tabele hostoryzujaca dane w oparciu o typ sql_variant.

create table [dbo].[NazwaTabeli](
	[NazwaTabeliId] [int] identity(1,1) not null,
	[TrackId] [int] not null,  -- referencja do opisu pola historyzowanego
	[RecordId] [int] not null,
	[Host] [nvarchar](256) null,
	[UserId] [int] null,
	[UserName] [nvarchar](100) null,
	[ValueBefore] [sql_variant] null,
	[ValueAfter] [sql_variant] null,
	[RegisterDate] [datetime] not null,
 constraint [Nazwa_PK] primary key clustered 
(
	[Klucz_glowny] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]

go

alter table [dbo].[NazwaTabeli] add  default (getdate()) for [RegisterDate]
go

Musisz sobie zmienic nazwe na taka jaka bedzie Tobie pasowala. A teraz tabela opisujaca historyzowane pola.

create table [dbo].[Track](
	[TrackId] [int] identity(1,1) not null,
	[Name] [nvarchar](256) null,
	[Hint] [nvarchar](500) null,
	[DatabaseObject] [sysname] not null,  -- nazwa tabeli ktora bedzie historyzowana
	[FieldName] [sysname] not null,  -- nazwa pola ktore bedzie historyzowane
	[TriggerName] [sysname] null, -- nazwa triggera historyzujacego
	[DataType] [int] null,  -- typ danych wg sys.types
	[Enabled] [int] not null,
 constraint [PK_Track] primary key clustered 
(
	[TrackId] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]

go

alter table [dbo].[Track] add  default ((1)) for [Enabled]
go

a teraz trigger

...... -- deklaracja naglowka na obiekcie
        
        select * into [#inserted] from inserted -- nalezy pamietac ze pola typu blob nie moga byc tak kopiowane
        select * into [#deleted] from deleted 

        declare @sql varchar(max);
        set @sql = 'declare @update [int] = 0 ';

	if (select count(*) from inserted i) = (select count(*) from deleted d) 
		set @sql = 'declare @update [int] = 1 ';

	select @sql = @sql + 
		' insert into ..( ' +
                '       .......... -- tutaj pola ktore maja byc standardem
		'	ValueBefore, ' + 
		'	ValueAfter) ' + 
		' select ' + 
		'   ' + -- tutaj odpowiednio dane z tabel zrodlowych 
		'	' + quotename(isnull(@@ServerName, '') + ' | ' + Suser_Name(), '''') + ', ' +
		'	isnull(i.Modify_Id_User, d.Create_Id_User), ' +
		'	isnull(i.Modify_User_Name, d.Create_User_Name), ' + 
		'	d.' + quotename(CHT.FieldName) + ', ' + 
		'	i.' + quotename(CHT.FieldName) +
		' from ' +
		'	[#inserted] i ' + 
		'	left join [#deleted] d on (i.Id_Przesylki = d.Id_Przesylki) ' + 
		' where ' + 
		'	(@update = 0) ' + 
		'	or ' + 
		'	( ' + 
		'		(@update = 1) and (d.' + quotename(CHT.FieldName) + ' <> ' + 'i.' + quotename(CHT.FieldName) +')' +
		'	) ' + char(13) + char(10)
	from 
		dbo.Track t
	where		
		(t.[Enabled] = 1)
		and (upper(t.DatabaseObject) = upper('dbo.Nazwa_tabeli_z_konfiguracji'))
		and (upper(CHT.TriggerName) = upper('[dbo].[nazwa_tego_triggera]')) -- uzytkownik moze spbie napisac swoja wersje 

	if (select count(*) from dbo.Track CHT where .... dodatkowe warunki CHT.[Enabled] = 1) = 0
	begin
		drop table [#inserted]
		drop table [#deleted]
		return;
	end;

	execute(@sql);

Dziękuje wam za odpowiedź. Nie wszystko co prawda zrozumiałem, ale dopytam się po skończeniu mojego zadania;)
crowa dziękuje za przykłady, jednak jak na razie są zbyt skomplikowane na mój móżdżek..., będę musiał nad tym więcej przysiąść ale to potem;)
Wracając do tematu:
Jako, ze okazało się, że program, który robi zmiany w bazie wykonuje je dla wszystkich użytkowników pod jednym loginem do bazy. Jednak udało mi się to obejść.
Ale wracając do tematu, zrobiłem tak że dla każdej operacji (INSERT, UPDATE, DELETE) wykonam oddzielny trigger:
INSERT

-- TRIGGER LOGUJĄCY OPERACJE INSERT

ALTER TRIGGER [dbo].[zmiana_insert] 
	ON [dbo].[tw__Towar] 
	AFTER INSERT
		AS 
		BEGIN
			DECLARE
				@uz_id int,
				@what int,
				@kiedy datetime,
				@operacja varchar(10)
			SELECT @uz_id=ss_uid FROM dbo.pd_Sesja WHERE (ss_spid = @@SPID)
			SELECT @what = tw_Id FROM INSERTED
			SELECT @kiedy = SYSDATETIME()
			SELECT @operacja = 'INSERT'
			SET NOCOUNT ON; 
			INSERT INTO dbo.zmiana_tw_towar([kto],[co],[kiedy],[operacja]) VALUES (@uz_id, @what, @kiedy, @operacja);
		END

DELETE

-- TRIGGER LOGUJĄCY OPERACJE DELETE
ALTER TRIGGER [dbo].[zmiana_delete] 
	ON [dbo].[tw__Towar] 
		FOR DELETE 
		AS 
		BEGIN
			DECLARE
				@uz_id int,
				@what int,
				@kiedy datetime,
				@operacja varchar(10)
			SELECT @uz_id=ss_uid FROM dbo.pd_Sesja WHERE (ss_spid = @@SPID)
			SELECT @what = tw_Id FROM DELETED
			SELECT @kiedy = SYSDATETIME()
			SELECT @operacja = 'DELETE'
			SET NOCOUNT ON; 
			INSERT INTO dbo.zmiana_tw_towar([kto],[co],[kiedy],[operacja]) VALUES (@uz_id, @what, @kiedy, @operacja); 
		END

Ale mam teraz problem z UPDATE, a mianowicie, przy operacji UPDATE, chciał bym zapisywać każdą zmianę osobno. Problem polega na tym, że nie wiem jak pobrać nową i starą wersje danej komórki. No i jak określić, która komórka jest zmieniana?
Czy podpowiecie w jaki sposób mogę to odczytać w podobnym triggerze do poprzednich?
Chciał bym to w miarę możliwości jak najprościej rozwiązać, stąd pozostaje przy opcji stworzenia trzeciego triggera.
Ewentualnie prosił bym o rady jak do tego podejść?
Z góry dziękuje za pomoc ;)

stara jest tam gdzie przy usuwaniu a nowa tam gdzie przy wstawianiu :) - update to po prostu delete + insert :)

ok a jak określić, która komórka została zmieniona? Zrobić pętle i przeszukać?

jedynie porównując starą i nową wersję każdej komórki

Ok, w jaki więc sposób porównać np komórkę x jednego wiersza do komórki x innego wiersza tabeli?
Zawsze się piszę np: SELECT id FROM tabela, ale jeżeli nie znam nazw kolumn?

@juniorchat, widzisz teraz, że łatwiej jest odkładać całe rekordy, tak jak pisałem wcześniej?

no cóż głupota ludzką każe zawsze spróbować inaczej :) w takim razie powiedz mi proszę w jaki sposób zapisać taka zmianę na twój sposób ?:) ewentualnie chociaż podpowiedz czego powinienem szukać i czemu się przyjrzeć ?:) chyba że masz jakieś przykłady to i również bym był bardzo wdzięczny :)

chyba że ktoś jeszcze ma jakiś sposób, w jaki sposób logowac takie zmiany?:)
z góry dzikuje za pomoc :)

wiadomość pisana na telefonie z trzesacego się pociągu, więc z góry przepraszam za błędy :)

edit:
po krótkim zastanowieniu już chyba wiem co masz na myśli:

1. zrobić kopie tabeli (jako pusta tabele) dodając do niej datę i id użytkownika (zmieniającego)
2. kopiować cały zmieniany rów do tej tabeli z informacja deleted i dodając informace inserted, stąd będę mógł sprawdzać co i gdzie było zmieniane, porównując zmiany:)
   dodatkowo dodawać jak przy insercie i delete daty. to da mi pełen obraz.. jednak będzie słabe przy odczycie bo zmiany będą tylko pośrednio wyświetlane, w sensie będzie trzeba je samemu porównać, a bezpośrednio zmian trigger mi nie wypisze.
   stąd i tak będę potrzbowal później funkcji, która porówna zmiany.tylko teraz pytanie jak ją zrobić w sqlu? bo przecież pisać program, żeby porównywał zmiany poza serwerem to chyba mija się z celem. prawda?

stąd nadal pozostaje pytanie, jak porównać jeden row z 2gim żeby zobaczyć co było zmieniane?

od sql 2008 masz mozliwosc porowania datasetow (czyli okreslenia czy isnieja roznice we wszystkich kolumnach)

sluzy do tego polecenie except