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
cpana
stackoverflow example for 47702144
PostgreSQL Sandbox: General Resale
E-Commerce SQL Analysis by Ran Li
Joins and Case
Cross Tab With Totals
On conflict
SQL Moving Rating Practice by Han Wang 20200720
univesp
1258. Find the Start and End Number of Continuous Ranges