Postgres & DML Tracing

postgres-dml-tracing.md

create table debug.employee_trail (
	id serial PRIMARY KEY, 
	operation varchar(6) not null, 
	employee_id integer not null, 
	timestamp timestamp not null
);

create or replace function debug.f_employee_changed() returns trigger as 
$$
declare 
	arg text; 
begin
	if tg_argv[0] = 'delete' then 
		insert into debug.employee_trail (operation, employee_id, timestamp) values (tg_argv[0], old.id, now());
	else
		insert into debug.employee_trail (operation, employee_id, timestamp) values (tg_argv[0], new.id, now());
	end if;
	
	return new; 
end; 
$$ language plpgsql;

create trigger t_on_insert after insert  
	on employees for each row 
	execute procedure debug.f_employee_changed('insert');
	
create trigger t_on_update after update
	on employees for each row 
	execute procedure debug.f_employee_changed('update');

create trigger t_on_delete after delete  
	on employees for each row 
	execute procedure debug.f_employee_changed('delete');
		
insert into employees (first_name, last_name) values ('klaus', 'bommelmaier');
update employees set last_name = 'Maier' where last_name = 'bommelmaier';
delete from employees where last_name = 'Maier';