Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
loggin_trigger-audit
/* trigger na logy AUDIT TRIGGER */ CREATE TABLE vyplaty ( meno_zamestnanca TEXT PRIMARY KEY, plat INTEGER NOT NULL ); 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 ); REVOKE ALL ON vyplaty_zmeny_log FROM PUBLIC; /* Zakomentovane pretoze live databaza nepodporuje vytvaranie uzivatelov GRANT ALL ON vyplaty_zmeny_log TO managers; */ 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; CREATE TRIGGER audit_vyplaty_zmena AFTER INSERT OR UPDATE OR DELETE ON vyplaty FOR EACH ROW EXECUTE PROCEDURE log_vyplaty_zmeny(); INSERT INTO vyplaty VALUES ('Rob', 1000); UPDATE vyplaty SET plat = 1100 WHERE meno_zamestnanca = 'Rob'; INSERT INTO vyplaty VALUES ('Maria', 1000); UPDATE vyplaty SET plat = plat + 200; SELECT * FROM vyplaty; SELECT * FROM vyplaty_zmeny_log;
run
|
edit
|
history
|
help
0
Query integer in text field - Seemingly doesn't work as expected
Descriptor test
SQL social network practice by Han Wang 20200720
quiz 1
Mandelbrot
PostreSQL: Today's Date/Time
Branch
ECommerce Website SQL Analysis by Iggy Zhao
asd
SQL primes, sqrt sieve