MS SQL: viewd on teie sõbrad…

Arusaamatul põhjusel inimesed kardavad view’sid. Võin öelda, et olles MS SQL andmebaasiga sõber alates 2000 aastast juba, ütlen view’d on teie sõbrad. Toon ühe lihtsa näite, mida mina kasutanud. Viewd sai mitu probleemi lahendatud:

  1. Kasutajad ei pääse otse ühelegi tabelile ligi, kõik toimub läbi view’de. Õigused omistate vaid viewle !

  2. Viewdes saab where lauses piirata kirjete nähtavust õigustega. MSSQL lubab viewd kasutada kui “tabelit” kui view genereeritud stiilis (ei tohi olla select osas teiste tabelite välju !) SELECT minutabel.* FROM minutabel JOIN Osakonnad  o ON o.id = minutabel.osakondid AND o.tootajalogin = suser_name()

  3. Viewdes saab kuvada vaid nö live kirjeid, üheski natukene tõsisemas süsteemis reaalselt kirjeid ei kustutata. Põhjus lihtne, kui mingi probleem, saab läbi kustutatud kirjete näha, mida on korda saadetud. Stiilis … WHERE kustutatud = 0

Koheselt tuleb saalis köhatus, aga mis siis juhtub, kui lähtetabelisse tekivad uued väljad, view ju neid ei kuva. Ärge muretsege: lihtsalt üks väike lisaliigutus, ei pea VIEW’d droppima ja uuesti looma. Viimase tegevusega kaoks ka kõik õigused.

Vaid üks käsk: sp_refreshview supervaade_vw

Panen siia ka koodinäite:

CREATE TABLE tmp_kasutaja
(
id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
eesnimi VARCHAR(35),
pereknimi VARCHAR(35),
lisajaid INTEGER,
lisatudkp DATETIME,
muutjaid INTEGER,
muudetudkp DATETIME,
kustutajaid INTEGER,
kustutatudkp DATETIME,
kustutatud BIT
)

 

CREATE VIEW tmp_kasutaja_vw
AS
SELECT * FROM tmp_kasutaja WHERE kustutatud = 0

Kuna kustutamine pole kunagi hea mõte, sest tihti kasutajatel tekib kollektiivne mäluauk ja alati infosüsteemid süüdi. Siis sellised kirjete logid stiilis, et kustutatud kirje reaalselt ei kustu ja muutja ning lisaja salvestatakse, see tegevus parandab tihti kollektiivset mäluauku.  Nii mõnelegi kasutajale järsku tuleb meelde, jaaa ma vist kustutasin midagi.  Teine variant teha tervilik auditlogi, kus igasugust muutmist logitakse, aga tihti ka sellisest “lihtsast” auditlogist täiesti piisab (kes muutis, kes lisas, kes kustutas).

Selleks teeme ühe toreda INSTED OF TRIGGERI

CREATE TRIGGER tmp_kasutaja_upt_del_trg ON dbo.tmp_kasutaja INSTEAD OF DELETE
AS
BEGIN

DECLARE @tootajaid INT;

SET NOCOUNT ON;
SELECT @tootajaid = id
FROM Tootaja
WHERE login = SUSER_NAME()

UPDATE t
SET kustutatud=1, kustutajaid=@tootajaid, kustutatudkp=GETDATE()
FROM tmp_kasutaja t
INNER JOIN deleted d ON
t.id = d.id

END

 

CREATE TRIGGER tmp_kasutaja_upt_trg on tmp_kasutaja
FOR UPDATE, INSERT
AS
BEGIN
DECLARE @tootajaid INT;

SET NOCOUNT ON;
SELECT @tootajaid = id
FROM Tootaja
WHERE login = SUSER_NAME()

IF EXISTS(SELECT * FROM DELETED)
BEGIN
IF UPDATE(kustutatud) return
UPDATE t
SET muutjaid=@tootajaid, muudetudkp=GETDATE()
FROM tmp_kasutaja t
INNER JOIN DELETED d ON
d.id = t.id
END ELSE
BEGIN
UPDATE t
SET lisajaid=@tootajaid, lisatudkp=GETDATE()
FROM tmp_kasutaja t
INNER JOIN INSERTED i ON
i.id = t.id
END
END

 

Lisa kommentaar

Täida nõutavad väljad või kliki ikoonile, et sisse logida:

WordPress.com Logo

Sa kommenteerid kasutades oma WordPress.com kontot. Logi välja /  Muuda )

Google photo

Sa kommenteerid kasutades oma Google kontot. Logi välja /  Muuda )

Twitter picture

Sa kommenteerid kasutades oma Twitter kontot. Logi välja /  Muuda )

Facebook photo

Sa kommenteerid kasutades oma Facebook kontot. Logi välja /  Muuda )

Connecting to %s


%d bloggers like this: