Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
loggin_trigger-audit
/* trigger na logy AUDIT TRIGGER Trigger sa spusti ked sa uzivatel pokusy zmenit akukolvek polozku v tabulke vyplaty */ /* vytvorenie tabulky */ CREATE TABLE vyplaty ( meno_zamestnanca TEXT PRIMARY KEY, plat INTEGER NOT NULL ); /* vytvorenie tabulky pre zapis zmien v tabulke vyplaty */ CREATE TABLE vyplaty_zmeny_log ( pozmenene_uzivatelom TEXT DEFAULT CURRENT_USER, datum_zmeny TIMESTAMP DEFAULT CURRENT_TIMESTAMP, druh_operacie TEXT, meno_zamestnanca TEXT, stary_plat INTEGER, novy_plat INTEGER ); /* Nastavenia pristupovych prav * verejnost nevidi logovaciu tabulku * manazery spolocnosti maju pristup k tabulke */ REVOKE ALL ON vyplaty_zmeny_log FROM PUBLIC; /* Tato online sluzba nepovoluje vytvaranie uzivatelov GRANT ALL ON vyplaty_zmeny_log TO manazery; */ /* Funkcia spustena triggrom, stara sa o zapis udajov do tabulky zmien pri evente * INSERT * UpDATE * DELETE */ CREATE OR REPLACE FUNCTION log_vyplaty_zmeny() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, novy_plat) VALUES (TG_OP, NEW.meno_zamestnanca, NEW.plat); ELSIF TG_OP = 'UPDATE' THEN INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, stary_plat, novy_plat) VALUES (TG_OP, NEW.meno_zamestnanca, OLD.plat, NEW.plat); ELSIF TG_OP = 'DELETE' THEN INSERT INTO vyplaty_zmeny_log (druh_operacie, meno_zamestnanca, stary_plat) VALUES (TG_OP, NEW.meno_zamestnanca, OLD.plat); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; /* trigger ktory zavola funkciu po kazdej zmene ci pridani zaznamu do tabulky VYPLATY */ CREATE TRIGGER audit_vyplaty_zmena AFTER INSERT OR UPDATE OR DELETE ON vyplaty FOR EACH ROW EXECUTE PROCEDURE log_vyplaty_zmeny(); /* Pridanie vyplaty zamestnanca */ INSERT INTO vyplaty VALUES ('Rob', 1000); /* Zmena vyplaty zamestnanca */ UPDATE vyplaty SET plat = 1100 WHERE meno_zamestnanca = 'Rob'; INSERT INTO vyplaty VALUES ('Maria', 1000); UPDATE vyplaty SET plat = plat + 200; SELECT * FROM vyplaty; /* Vypis zmien */ SELECT * FROM vyplaty_zmeny_log;
run
|
edit
|
history
|
help
0
postgresql: working days
SQL social network practice by Han Wang 20200720 - 2
test
PostgreSQL: Inner Join
Branch
How i can get the difference of closest two date in two table join in postgresql
FlujoPadre
SQL primes, sqrt sieve
Q4
E-commerce Website SQL Analysis