I have a Audit Trigger created which is as below. I'm using PostgreSQL 9.3.
The Trigger works perfectly if i execute from Backend but when I do the same from Frontend,It works fine for Insert & Delete but fails for Update..
Also in my code,I hav given inserts to two tables mapped by Foreign key relation.For Update event,I get wrongly updated columns and sometimes entry is there in 2nd table(query_relation) but for that there is no entry in 1st table(audit_track).
Here is mine trigger function
create or replace function audit_track() returns trigger
as $body$
begin
if tg_op = 'UPDATE' then
insert into audit_track (audit_id,table_name, audit_by,audit_date, action, old_values, new_values, updated_columns)
values (txid_current(),tg_table_name::text,new.audit_by,now(), 'Update', svals (hstore(old.*) - hstore(new.*)) , svals (hstore(new.*) - hstore(old.*)),
skeys (hstore(new.*) - hstore(old.*)));
insert into query_relation values(txid_current(),current_query(),now());
return new;
elsif tg_op = 'DELETE' then
insert into audit_track (audit_id,table_name, audit_by,audit_date, action, old_values,updated_columns)
values (txid_current(),tg_table_name::text, old.audit_by,now(), 'Delete', svals (hstore(old.*)),skeys (hstore(old.*)));
insert into query_relation values(txid_current(),current_query(),now());
return old;
elsif tg_op = 'INSERT' then
insert into audit_track(audit_id,table_name, audit_by,audit_date, action, new_values,updated_columns)
values (txid_current(),tg_table_name::text, new.audit_by,now(), 'Insert', svals (hstore(new.*)),skeys (hstore(new.*)));
insert into query_relation values(txid_current(),current_query(),now());
return new;
end if;
EXCEPTION
WHEN data_exception THEN
insert into exception_details values(current_query(),sqlerrm,now());
RETURN New;
WHEN unique_violation THEN
insert into exception_details values(current_query(),sqlerrm,now());
RETURN New;
WHEN OTHERS THEN
insert into exception_details values(current_query(),sqlerrm,now());
RETURN New;
end;
$body$
language plpgsql;
Am i doing something wrong for update event to fail from frontend but to work properly if done manually from backend???