Created
April 23, 2019 21:32
-
-
Save renerdias/792cf35d62f97198bb6f2fedb14ea700 to your computer and use it in GitHub Desktop.
Cria uma tabela de log para cada tabela no schema public
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace function auditoria.func_criar_tabela_auditoria(nNomeTabela name, bRecriar boolean) returns boolean as | |
$body$ | |
declare | |
nNomeTabelaAuditoria name; | |
begin | |
-- overlay substitui o tb no nome da tabela por ta | |
nNomeTabelaAuditoria := 'auditoria.' || overlay(nNomeTabela placing 'ta' from 1 for 2); | |
if bRecriar then | |
execute 'drop table if exists ' || nNomeTabelaAuditoria; | |
end if; | |
begin | |
execute 'create table ' || nNomeTabelaAuditoria || ' as (select * from auditoria.ta_padrao cross join ' || nNomeTabela || ' limit 0)'; | |
exception | |
when duplicate_table then | |
--Não faz nada | |
end; | |
begin | |
execute 'create sequence ' || nNomeTabelaAuditoria || '_seq'; | |
exception | |
when duplicate_table then | |
--Não faz nada; | |
end; | |
execute 'drop trigger if exists trig_auditoria on ' || nNomeTabela || ' cascade'; | |
execute 'create trigger trig_auditoria ' || | |
'after insert or update or delete on ' || nNomeTabela || ' for each row ' || | |
'execute procedure auditoria.trig_func_auditoria()'; | |
return true; | |
end; | |
$body$ | |
language 'plpgsql'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment