Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Ko je uneo ili promenio podatke – misterija kolona CreatedUser, CreatedDate, UpdatedUser, UpdatedDate

Friday, 29.06.2012 – Zidar

Uvod

Podaci koje cuvamo u bazama podataka su zivi, menjaju se konstantno. Ponekad je sasvim u redu cuvati samo tekuce podatke, i promene nas ne interesuju. Mnogo cesce, medjutim, promene nas interesuju. Sta je sada, a sta je bilo pre. To nije jednostavno pratiti u relacionim bazama podataka. Nije jednostavno, ali svima treba i svi se nekako dovijaju.

Prvi pokusaj da se prate promene dolazi iz zelje da znamo ko je i kada uneo podatke i da li su podaci od tada promenjeni , ko ih je i kada promenio. To je minimum koji zelimo da pratimo. I njaveci pocetnici brzo nauce da dodaju u tabelu kolone

CreatedDate – kada je red dodat u tabelu,

CreatedUser – ko je dodao red u tabelu (‘kreirao rekord’),

UpdatedDAte – kada je podatak promenjen,

UpdatedUser – ko je promenio podatak

Najcesce se sav napor tu I zavrsava – dodamo kolone I uzdamo se u Boga da se kolone nekako popune. Pa kad zatreba, necemo znati sta je bilo ranije u tabeli, ali cemo barem moci da pitamo onoga ko je napravio promene sta je uradio i zasto.

U ovom clanku, cemu ovu elementarnu stvar podici na malo visi nivo, tako da bar izgledamo kao profesionalci. Kod je proveren na MS SQL 2005. Uz male izmene, sve bi radilo I na MS SQL 2000.

CreatedUser,CreatedDate,UpdatedUser, UpdatedDate

Neka je data baza sa dve tabele, MyMasterTable i NyLookupTable. Evo skripte za kreiranje tabela:

IF Object_ID(‘MyLookupTable’) IS NOT NULL DROP TABLE MyLookupTable

GO

CREATE TABLE MyLookupTable

(Code int NOT NULL PRIMARY
, MyDescription varchar(15) NOT NULL
, CONSTRAINT unqMyLookupTable_MyDescription UNIQUE(MyDescription)
);

INSERT INTO MyLookupTable VALUES
(1,‘tra, la, la’);

INSERT INTO MyLookupTable VALUES
(2,‘bla bla, bla’);

INSERT INTO MyLookupTable VALUES
(3,‘trla baba lan’);

;

IF Object_ID(‘MyMasterTable’) IS NOT NULL DROP TABLE MyMasterTable

GO

CREATE TABLE MyMasterTable(

MyPK int NOT NULL PRIMARY KEY,
ColA int NULL,
ColB varchar(15) NOT NULL,
CONSTRAINT fkMyMasterTable_ColA_MyLookupTAble
FOREIGN KEY (ColA) REFERENCES MyLookupTAble(Code)
)

GO

Dve tabele, primarni kljucevi, jedan strani kljuc i NULL/NOT NULL ogranicenja. Zbog ovoga NULL/NOT NULL, vec smo iznad proseka. 🙂
Znaci, pored podataka koji se cuvaju u tabeli, zelimo da pratimo ko je i kada uneo podatke, i ko je i kada promenio podatke. Ovo nam verovatno korisnik nece navesti u specifikaciji, ali mi smo profesionalci i znamo da je korisno znati ko sta radi i kada po tabeli, pa ovo radimo sebe radi.

Da bi znali ko je i kada uneo podatke, dodacemo dve kolone

– CreatedDate datetime – kada je red dodat u tabelu

– CreatedUser datetime – ko je dodao red u tabelu

Primetite da ne dozvoljavamo NULL vrednosti ni u jednoj od kolona. Obe kolone imaju DEFAULT vrednosti, zbog cega ne moramo da ih navodimo u listi INSERT komande.

Ovako bi izgledalo kreiranje tabele:

IF Object_ID(‘MyMasterTable’) IS NOT NULL DROP TABLE MyMasterTable

GO

CREATE TABLE MyMasterTable

(

— stvarni podaci:

MyPK int NOT NULL PRIMARY KEY

, ColA int NULL

, ColB varchar(15) NOT NULL

— ko je i kada uneo podatke:

, CreatedDate datetime NOT NULL DEFAULT (GetDate())

, CreatedUser nchar(30) NOT NULL DEFAULT (system_user)

— veza sa lookup tabelom

, CONSTRAINT fkMyMasterTable_ColA_MyLookupTAble

FOREIGN KEY (ColA) REFERENCES MyLookupTAble(Code)

)

;

Ako pretpostavimo da niko nece menjati CreatedDate ili CreatedUser za sada smo OK Znacemo uvek ko je i kada uneo podatke u tabelu. To je lepo, ali mi hocemo da znamo i ko je i kada promenio podatke u tabeli? Ako nista drugo, uvek mozemo da pitamo coveka zasto je to uradio i ste je bilo u tabeli pre promene.

Zasto je pretpostavka da niko, namerno ili ne namerno, nece menjati podatke u kolonama za pracenje promena sumnjiva i opasna? Sumnjiva zato sto nemate garanciju da se to nece desiti. Opana zato sto cete imati pogresne podatke, a ovi podaci vam trebaju samo kada nesto inace krene lose.

Dodajmo kolone za pracenje promena u unetim podacima.

IF Object_ID(‘MyMasterTable’) IS NOT NULL DROP TABLE MyMasterTable

GO

CREATE TABLE MyMasterTable

(

— stvarni podaci

MyPK int NOT NULL PRIMARY KEY

, ColA int NULL

, ColB varchar(15) NOT NULL

— ko je i kada uneo podatke:

, CreatedDate datetime NOT NULL DEFAULT (GetDate())

, CreatedUser nchar(30) NOT NULL DEFAULT (system_user)

— Pracenje promena

, UpdatedDAte datetime NULL

, UpdatedUser nchar(30) NULL

— veza sa lookup tabelom

, CONSTRAINT fkMyMasterTable_ColA_MyLookupTAble

FOREIGN KEY (ColA) REFERENCES MyLookupTAble(Code)

)

;

Moramo da dozvolimo NULL vrednosti za UpdatedDAte i UpdatedUser, jer te kolone treba da dobiju vrednost tekkad dodje do promene. Kako cemo da popunimo Updated.. kolone? Automatski, pomocu trigera.

Minimalni triger izgleda ovako:

CREATE TRIGGER trgMyMasterTable_UPD ON
MyMasterTable

FOR UPDATE

AS

UPDATE MyMasterTable

SET

UpdatedDAte=getdate()

, UpdatedUser = system_user

FROM inserted AS I

JOIN MyMasterTable as
M ON I.MyPK = M.MyPk

;

Da vidimo da li sve ovo radi.

INSERT INTO MyMasterTable (MyPK,ColA,ColB) VALUES (1, 1, ‘Dobar!’);

INSERT INTO MyMasterTable (MyPK,ColA,ColB) VALUES (2, 2, ‘Bolji!’);

INSERT INTO MyMasterTable (MyPK,ColA,ColB) VALUES (3, 1, ‘Naj Bolji’);

Naravno da radi, vidi se ko je uneo podatke i kada:

SELECT * FROM MyMasterTable;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDAte	UpdatedUser
1	1	Dobar!	2012–06–29 13:33:53.400	Test\Zidar	NULL	NULL
2	2	Bolji!	2012–06–29 13:34:02.693	Test\Zidar	NULL	NULL
3	1	Naj Bolji	2012–06–29 13:34:02.703	Test\Zidar	NULL	NULL

U redu sa MyPK = 3 rec ‘najbolji’ je pogresno otkucana.
Pokusajmo da ispravimo gresku.

UPDATE
MyMasterTable

SET ColB = ‘Najbolji’

WHERE MyPK = 3

Sta sada imamo u tabeli:

SELECT * FROM MyMasterTable;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDAte	UpdatedUser
1	1	Dobar!	2012–06–29 13:33:53.400	Test\Zidar	NULL	NULL
2	2	Bolji!	2012–06–29 13:34:02.693	Test\Zidar	NULL	NULL
3	1	Najbolji	2012–06–29 13:34:02.703	Test\Zidar	2012–06–29 13:36:44.210	Test\Zidar

Za sada, sve izgledad dobro. Medjutim, uvek ima mesta za poboljsanja. Evo nekoliko poboljsanja koja se u praksi cesto zaboravljaju:

Prvo, ne zaboravimo da se izmena desava uvek posle unosenja podataka, pa treba da bude UpdateDAte>CreateDate.

Drugo, kolone UpdateDate i Updateduser moraju ili obe da budu NULL ili obe da budu NOT NULL. Podaci ne smeju d apostoje u jednoj a nedostaju u drugoj koloni.

Trece, svakao ne zelimo da bilo ko naknadno menja neku od kolona koje prate promene.

Prva dva poboljsanja se lako resavaju upotrebom CHECK constraints.

— Datum izmena mora doci kasnije od unosa

ALTER TABLE MyMasterTable WITH
CHECK

ADD CONSTRAINT ckMyMasterTable_UpdPosleCreate

CHECK (CreatedDate <
UpdatedDAte)

;

Primetite da nismo stavili (CreatedDate < UpdatedDAte OR UPdatedDate IS NULL). Nema potreba, jer kad izraz je u CHECK constraint jendk NULL smatra se da je pravilo zadovoljeno (nije prekrseno)

Da li ogranicenje radi:

UPDATE
MyMasterTable

SET UpdatedDAte = ‘19600310’ — 10 Mart 1960, veoma star datum 🙂

WHERE MyPK = 3

Ne ide, dobijemo ovu poruku:

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with
the CHECK constraint
“ckMyMasterTable_UpdPosleCreate”. The
conflict occurred in database
“Dejan”, table
“dbo.MyMasterTable”.

The statement has been
terminated.

Za drugo poboljsanje, “kolone UpdateDate i Updateduser moraju ili obe da budu
NULL ili obe da budu NOT NULL”, ogranicenje moze da se napise na vise nacina. Ja cu da
uradim ovako ovako:

Mora da bude zadovoljeno:

Ako (UpdatedDAte IS NULL) onda (UpdatedUser IS NULL)

i

Ako (UpdatedDAte IS NOT NULL) onda (UpdatedUser IS NOT NULL)

Ovo su dva izraza tipa A=>B. A => B ne moze da se
predstavi u Transact SQL. Medjutim, A=>B moze da se napise kao ((NOT A) OR
B), sto se lako pretvara u CHECK ogranicenje.

ALTER TABLE MyMasterTable WITH
CHECK

ADD CONSTRAINT
ckMyMasterTable_UpdatedDAteUpdatedUser_vs_NULL

CHECK (

(NOT (UpdatedDAte IS NULL) OR UpdatedUser IS NULL)

AND

(NOT (UpdatedDAte IS NOT NULL) OR (UpdatedUser IS NOT NULL))

)

;

Oslobadjanjem od zagrada izraz u CHECK ogranicenju bi mogao
da se naoko uprosti, ali bi bio znatno tezi za citanje i razumevanje.

Da li poslednje ogranicenje radi:

UPDATE
MyMasterTable

SET
UpdatedDAte = NULL

WHERE MyPK = 3

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with
the CHECK constraint
“ckMyMasterTable_UpdatedDAteUpdatedUser_vs_NULL”. The conflict occurred in
database “Dejan”, table
“dbo.MyMasterTable”.

The statement has been
terminated.

Primetite da se CHECK constraint se aktivirala pre trigera i
zaustavila tarnsakciju.

Testiranje nije gotovo:

UPDATE
MyMasterTable

SET
UpdatedUser = NULL

WHERE MyPK = 3

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with
the CHECK constraint
“ckMyMasterTable_UpdatedDAteUpdatedUser_vs_NULL”. The conflict occurred in
database “Dejan”, table
“dbo.MyMasterTable”.

The statement has been
terminated.

Sta bi bilo kad bismo uradili ovo:

UPDATE
MyMasterTable

SET

UpdatedUser = NULL

, UpdatedDAte = NULL

WHERE MyPK = 3

;

Sada nas je CHECK pustio da prodjemo, ali je triger odradio
svoj deo posla – zabelezio je ko je i kada promenio podatke. Ovom smo dobili
laznu promenu – promenili smo vreme polsenje promene, a da se u stvari u tabeli nije nista promenilo.

U svakom slucaju, CHECK constraint i trigger, u sadejstvu, nisu dozvolili da anuliramo podatke.

SELECT * FROM MyMasterTable;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDAte	UpdatedUser
1	1	Dobar!	2012–06–29 15:02:16.940	Test\Zidar	NULL	NULL
2	2	Bolji!	2012–06–29 15:02:27.820	Test\Zidar	NULL	NULL
3	1	Najbolji	2012–06–29 15:02:27.830	Test\Zidar	2012–06–29 15:05:06.337	Test\Zidar

Sta se desava ako pkusamo da uesemo UpdateUser i CreateUser
po zelji:

UPDATE
MyMasterTable

SET

UpdatedUser = ‘Yoggy Bear’

, UpdatedDAte = ‘2012-06-29 15:22:27.830’ — 20 minuta posle CreatedDate

WHERE MyPK = 3

;

SELECT * FROM MyMasterTable;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDAte	UpdatedUser
1	1	Dobar!	2012–06–29 15:02:16.940	Test\Zidar	NULL	NULL
2	2	Bolji!	2012–06–29 15:02:27.820	Test\Zidar	NULL	NULL
3	1	Najbolji	2012–06–29 15:02:27.830	Test\Zidar	2012–06–29 15:07:26.530	Test\Zidar

Triger je odradio svoj posao i nije uneti vreme koje smo mi zadali, nego vreme kad je proemna zaista upisana u atbelu. Podaci ipak nece biti vise tacni, jer smo izgubili stvarni datum promene. Ovo cemo da resimo kad budemo resavali poslednje od tri poboljsanja koje smo naveli: svakao ne zelimo da bilo ko naknadno menja neku od kolona koje prate promene.

Videli smo da se UpdateDate i UpdatedUser tesko mogu promeniti na nesto sto mi hocemo – trigger ce uvek upisati trenutak promene i ko je pokusao promenu, pa makar
menjali kolone UpdateDate i UpdatedUser.

Mozemo li da promenimo CraetedUser ili CreatedDate? Mozemo, ukoliko postujemo CHECK constraints.

UPDATE
MyMasterTable

SET

CreatedUser = ‘Piksi’ — korisnik uopste
ne postoji u sistemu

, CreatedDAte = ‘19020101’ — 100 godina ranije od originalnog CreatedDate

WHERE MyPK = 1

;

SELECT * FROM MyMasterTable;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDate	UpdatedUser
1	1	Dobar!	1902–01–01 00:00:00.000	Piksi	2012–06–29 15:19:03.993	Test\Zidar
2	2	Bolji!	2012–06–29 15:02:27.820	Test\Zidar	NULL	NULL
3	1	Najbolji	2012–06–29 15:02:27.830	Test\Zidar	2012–06–29 15:11:32.287	Test\Zidar

Kako da sprecimo promenu CreatedDate, CreatedUser i UpdatedDAte,UpdatedUser?

Mozemo u trigeru da utvrdimo da li korisnik pokusava da promeni neku od ove cetiri kolone i da to sprecimo. U MS SQL, funkcija koja nam pomaze u ovom slucaju je UPDATE(<ime kolone>)

ALTER TRIGGER trgMyMasterTable_UPD ON
MyMasterTable

FOR UPDATE

AS

— Novo:

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

— Staro:

UPDATE MyMasterTable

SET

UpdatedDAte=getdate()

, UpdatedUser = system_user

FROM inserted AS I

JOIN MyMasterTable as
M ON I.MyPK = M.MyPk

;

GO

Triger je podugacak, ali vidite da su dodatne komande jednostavne. Ako se manja bilo koja od nasih kolona za pracenje proemna, obustavlja se cela tarnsakcija (rollback) i izlazi se iz trigera (return). Da
testiramo:

UPDATE
MyMasterTable

SET

CreatedUser = ‘Batman’ — korisnik uopste
ne postoji u sistemu

, CreatedDAte = ‘1906-06-29 13:33:53.400’ — 100 godina ranije od originalnog
CreatedDate

, UpdatedUser = ‘SpidreMan’ — korisnik uopste ne postoji u sistemu

, UpdatedDAte = ‘1959-06-29 13:33:53.400’ — 100 godina ranije od originalnog
CreatedDate

WHERE MyPK = 2

;

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The
batch has been aborted.

SELECT * FROM MyMasterTable

;

Znaci, imamo cetiri kolone, dve Created.. i dve Updated…
Created.. kolone se popunjavaju preko DEFAULT vrednosti i ne smeju biti NULL.

UPpadted.. kolone se popunjavaju kroz triger, u momentu izmene. CHECK constraints sprecavaju nelogican unos podataka.

Triger ne dozvoljava da se menjaju kolone koje prate ko je i kada uneo ili izmenio podatke.

Moguca je jos jedna sitna popravka. I dalje mozemo da belezimo lazne promene.

Lazna promena je kad uradimo UPDATE, i damo novu vrednost koja je jednaka staroj vrednosti.

SELECT *
FROM MyMasterTable
WHERE MyPK=1;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDate	UpdatedUser
1	1	Dobar!	1902–01–01 00:00:00.000	Piksi	2012–06–29 15:19:03.993	Test\Zidar

Za MyPK = 1, colB = ‘Dobar!’.

Pokusajmo ovo:

UPDATE MyMasterTable

SET ColB = ColB — ista vrednost ce se upisati preko postojece

WHERE MyPK=1

;

SELECT *
FROM MyMasterTable
WHERE MyPK=1;

MyPK	ColA	ColB	CreatedDate	CreatedUser	UpdatedDate	UpdatedUser
1	1	Dobar!	1902–01–01 00:00:00.000	Piksi	2012–06–29 15:40:14.890	Test\Zidar

Podaci se nisu promenili, ali se datum u UpdatedDate promenio! To nije dobro.

Lazne promene otkrivamo u trigeru, tako sto uporedimo tabelu INSERTED sa nasom tabelom. Nacin na koji poredimo atbele inserted I nasu originalnu tabelu radi u MS SQL 2005. Verzija 2000 ne koristi EXCEPT, ali se moze koristiti LEFT JOIN.

ALTER TRIGGER trgMyMasterTable_UPD ON
MyMasterTable

FOR UPDATE

AS

— Najnovije: da li se Inesrted razlikuje od
MyMasterTable?

— Ako je Inserted = MyMasterTable onda EXCEPT vraca
prazan skup <=> NOT EXISTS

IF NOT EXISTS

(

SELECT I.MyPk, I.ColA, I.ColB

FROM Inserted AS I

EXCEPT

SELECT M.MyPk, M.ColA, M.ColB

FROM
MyMasterTable AS M

JOIN Inserted AS I ON I.MyPk = M.MyPk

)

BEGIN

rollback

return

END

— Novo:

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

IF UPDATE(CreatedDate)

BEGIN

rollback

return

END

— Staro:

UPDATE
MyMasterTable

SET

UpdatedDAte=getdate()

, UpdatedUser = system_user

FROM inserted AS I

JOIN
MyMasterTable as M ON
I.MyPK = M.MyPk

return

;

GO

SELECT *

FROM
MyMasterTable

WHERE MyPK=1

;

MyPK ColA ColB CreatedDate CreatedUser UpdatedDAte UpdatedUser

1 1 Dobar! 1902–01–01 00:00:00.000 Piksi 2012–06–29 15:40:14.890 Test\Zidar

UPDATE MyMasterTable

SET ColB = ColB — ista vrednost ce se upisati preko postojece

WHERE MyPK=1

;

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

SELECT *
FROM MyMasterTable
WHERE MyPK=1;

Zakljucak

Pokazali smo kako se moze obezbediti elementarno i minimalno pracenje ko je i kada uneo ili promenio podatke.

Nista narocito, reci ce mnogi, svako dete zna da doda kolone Created… i Updated…. Istina je, ali smo ovde sve podigli na malo ozbiljniji nivo, time sto smo sprecili nelogicne podatke i upis laznih promena.

A ako pogledamo pazljivo CHECK ogranicenja i triger koji smo morali napraviti, i nije sve bas tako jednostavno. 🙂

Za ozbiljan rad, ovo sto smo dobili nije dovoljno. Sta vredi znati ko je promenio podatke, ako ne znamo sta smo imali pre promene. Naravno da ako znamo ko je napravio proemnu, uvek mozemo da pitamo tu osobu zasto je to uradjeno I sta je bilo pre promene, ali to nije bas najprofesionalnije ponasanje, zar ne.

Sledeci clanak ce pokazati kako mozemo da cuvamo istoriju – stari podaci ne nestaju nego se cuvaju u zasebnoj tabeli. Trebace nam trigeri, a trebace i znati kako se istorijski podaci citaju. Pokazacemo klasican nacin, poznat preko 30 godina, a onda mozda pokazati i neka modrnija resenja.

Pokazana resenja ne treba shvatiti kao recept za rad. Cilj je da pocnemo razmisljati o stvarima koje lako zaboravimo – sacuvati podatke, ali i spreciti mugucu zloupotrebu ili kontaminaciju podataka, na visem nivou od FOREIGN KEY.