Em 25 de outubro de 2010 22:46, Fábio Gibon - Comex System <
[email protected]> escreveu:
> Pessoal,
> preciso montar uma trigger que vai comparar se o conteúdo antigo
> de um campo que foi alterado é diferente do novo. Algo tipo:
>
> if new.campox <> old.campox then
> ...
>
> Porém preciso fazer isto para cada campo dinamicamente, recuperar
> o nome dos campos em cursor eu já fiz, porém como fazer algo do tipo:
>
> if new.registro.campo <> old.registro.campo then
> ...
>
>
Veja se o anexo te ajuda.
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
create or replace function tabelaExiste(text) returns boolean as
$$
select exists (select tablename from pg_tables where tablename = $1) ;
$$
language sql;
create or replace function typeAsText(text, anyelement) returns text as
$$
declare
sType alias for $1;
xValue alias for $2;
sReturn text;
begin
if sType = 'bool' then
if xValue is true then
sReturn := 'true';
else
sReturn := 'false';
end if;
else
sReturn := cast(xValue as text);
end if;
return sReturn;
end;
$$
language plpgsql;
--drop trigger tg_verificaalteracaocampos on teste;
--
-- Tabela de Log
drop table if exists logtabela;
create table logtabela (operacao char(1), tabela text, campo text, valorantes text, valordepois text);
create or replace function verificaAlteracaoCampos() returns trigger as
$$
declare
sExpressao text;
sSQL text default '';
sOperacao text;
sTabela text;
rCampos record;
lDiferente boolean;
sLogTabela varchar default 'logtabela';
sUnion varchar default '';
sSelect varchar default '';
begin
sOperacao := trim(upper(TG_OP));
sTabela := TG_RELNAME;
-- Objeto NEW existe somente em INSERT e UPDATE
if sOperacao in ('INSERT', 'UPDATE') then
if exists (select tablename from pg_tables where tablename = 'newrecordtable') then
drop table newrecordtable;
end if;
create temporary table newrecordtable as select new.*;
end if;
-- Objeto OLD existe somente em INSERT e UPDATE
if sOperacao in ('UPDATE', 'DELETE') then
if exists (select tablename from pg_tables where tablename = 'oldrecordtable') then
drop table oldrecordtable;
end if;
create temporary table oldrecordtable as select old.*;
end if;
-- Percorre Campos da Tabela afetada pela Trigger
for rCampos in select *
from information_schema.columns
where table_name = sTabela
order by ordinal_position
loop
--
-- Executa comparacao entre NOVO e VELHO somente no UPDATE
--
if sOperacao = 'UPDATE' then
-- Monta Expressao de Comparacao
sExpressao := 'SELECT CASE WHEN (SELECT '||quote_ident(rCampos.column_name)||' FROM newrecordtable) IS NULL
AND (SELECT '||quote_ident(rCampos.column_name)||' FROM oldrecordtable) IS NULL THEN FALSE
WHEN (SELECT '||quote_ident(rCampos.column_name)||' FROM newrecordtable) IS NULL
OR (SELECT '||quote_ident(rCampos.column_name)||' FROM oldrecordtable) IS NULL THEN TRUE
ELSE (SELECT '||quote_ident(rCampos.column_name)||' FROM newrecordtable) <>
(SELECT '||quote_ident(rCampos.column_name)||' FROM oldrecordtable)
END;';
execute sExpressao into lDiferente ;
--raise notice '%', sExpressao;
if lDiferente is true or lDiferente is null then
--raise notice 'Novo conteudo do campo % é DIFERENTE do anterior', rCampos.column_name;
sSQL := 'INSERT INTO "'||sLogTabela||'" ("operacao", "tabela", "campo", "valorantes", "valordepois")
VALUES ('||quote_literal('A')||', '||quote_literal(sTabela)||', '||quote_literal(rCampos.column_name)||',
CAST((SELECT '||quote_ident(rCampos.column_name)||' FROM oldrecordtable LIMIT 1) AS TEXT),
CAST((SELECT '||quote_ident(rCampos.column_name)||' FROM newrecordtable LIMIT 1) AS TEXT)';
--raise notice '%', sSQL;
--else
--raise notice 'Novo conteudo do campo % é IGUAL ao anterior', rCampos.column_name;
end if;
--
-- Codigo pra gerar LOG de um INSERT
--
elsif sOperacao = 'INSERT' then
--sSQL := 'INSERT INTO "'||sLogTabela||'" ("operacao", "tabela", "campo", "valorantes", "valordepois")
-- VALUES ('||quote_literal('I')||', '||quote_literal(sTabela)||', '||quote_literal(rCampos.column_name)||',
-- null, typeAsText('||quote_literal(rCampos.udt_name) ||',(SELECT '||quote_ident(rCampos.column_name)||' FROM newrecordtable LIMIT 1)) )';
sSelect := 'SELECT '||quote_literal('I')||', '||quote_literal(sTabela)||', '||quote_literal(rCampos.column_name)||',
null, typeAsText('||quote_literal(rCampos.udt_name) ||',(SELECT '||quote_ident(rCampos.column_name)||' FROM newrecordtable LIMIT 1)) ';
--raise notice '%', sSelect;
--
-- Codigo pra gerar LOG de um DELETE
--
elsif sOperacao = 'DELETE' then
sSQL := 'INSERT INTO "'||sLogTabela||'" ("operacao", "tabela", "campo", "valorantes", "valordepois")
VALUES ('||quote_literal('E')||', '||quote_literal(sTabela)||', '||quote_literal(rCampos.column_name)||',
null, CAST((SELECT '||quote_ident(rCampos.column_name)||' FROM oldrecordtable LIMIT 1) AS TEXT))';
--raise notice '%', sSQL;
end if;
sSQL := sSQL || sUnion || sSelect;
sUnion := ' UNION ALL ';
--execute sSql;
end loop;
if sSQL <> '' then
sSQL := 'INSERT INTO "'||sLogTabela||'" ("operacao", "tabela", "campo", "valorantes", "valordepois") '||sSQL;
--raise notice '%', sSQL;
execute sSql;
end if;
if sOperacao in ('INSERT', 'UPDATE') then
return new;
end if;
return old;
end;
$$
language plpgsql;
--create trigger tg_verificaalteracaocampos
--after insert or update or delete on teste
--for each row execute procedure verificaAlteracaoCampos();
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
