Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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???
Posted
Comments
Vinit Shetye 25-May-15 0:52am    
Any help/suggestions will be appreciated.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900