Forums PostgreSQL.fr

Michel Depiesse

Membre

trigger before delete et foreign key constraint on delete cascade

Bonjour,

Quand je delete le pk de app_table, les records correspondants de app_table_ref sont délétés mais le trigger pre delete n'est pas déclenché et il n'y a donc pas de records écrits dans record_tracking.

Que faire ?

Merci pour votre attention

---------------------------------------------------------------------------------------------------------------------------------------------
-- this script must be played on the tablet just after the database has been copied
---------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------
-- create db objects
---------------------------------------------------------------------------------------------------------------------------------------------
drop table if exists record_tracking;
create table record_tracking (
	 id_record_tracking	serial	
	,table_name		text
	,operation		text
	,pk_value		bigint
	,user_name		text
	,insertion_date		timestamp 	default now()
);

drop sequence if exists record_tracking_seq;
create sequence record_tracking_seq;

drop table if exists app_table_ref;
drop table if exists app_table;

create table app_table (
         pk     	integer         primary key
        ,info   	varchar
	,audit_id	bigint
);

create table app_table_ref (
         pk     	integer         primary key
        ,info   	varchar
        ,fk     	integer         references app_table(pk)        on delete cascade
	,audit_id	bigint
);


---------------------------------------------------------------------------------------------------------------------------------------------
-- generic function for all triggers
---------------------------------------------------------------------------------------------------------------------------------------------
create or replace function record_tracking() returns trigger as $$
begin

	if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then
		
		insert into record_tracking
			(table_name, operation, pk_value, user_name)
		values 
			(TG_TABLE_NAME, TG_OP, new.audit_id, current_user);

		return new;

	else
		insert into record_tracking
			(table_name, operation, pk_value, user_name)
		values 
			(TG_TABLE_NAME, TG_OP, old.audit_id, current_user);

		return old;

	end if;
end; 
$$ language 'plpgsql'; 

---------------------------------------------------------------------------------------------------------------------------------------------
-- create triggers
---------------------------------------------------------------------------------------------------------------------------------------------
create or replace function create_audit_triggers() returns void as $$
declare
	r	record;
	stmt	varchar;
begin
	for r in select table_name from information_schema.tables where table_name like 'app_table%' loop

		stmt :=    'create trigger ' 
			|| quote_ident(r.table_name || '_audit_after_iu')
			|| ' after insert or update on ' 
			|| quote_ident(r.table_name)
			|| ' for each row when (pg_trigger_depth() < 1) execute procedure record_tracking();';

		execute stmt;

		stmt :=    'create trigger ' 
			|| quote_ident(r.table_name || '_audit_before_d')
			|| ' before delete on ' 
			|| quote_ident(r.table_name)
			|| ' for each row when (pg_trigger_depth() < 1) execute procedure record_tracking();';

		execute stmt;

	end loop;
end; 
$$ language 'plpgsql'; 

select create_audit_triggers();

insert into app_table values(23,'record 23');
insert into app_table values(56,'record 56');
insert into app_table values(71,'record 71');
insert into app_table values(82,'record 82');
insert into app_table values(85,'record 85');
insert into app_table values(91,'record 91');
insert into app_table values(94,'record 94');
insert into app_table values(97,'record 97');
insert into app_table values(99,'record 99');
update app_table set info = 'modified' where pk = 23;
update app_table set info = 'modified' where pk = 56;
update app_table set info = 'modified' where pk = 97;
delete from app_table where pk = 71;
insert into app_table values(101,'record 101');
insert into app_table values(121,'record 121');
insert into app_table values(167,'record 167');
update app_table set info = 'modified' where pk = 101;
delete from app_table where pk = 121;

insert into app_table_ref values(1, 'ref1', 23);
insert into app_table_ref values(2, 'ref2', 23);
insert into app_table_ref values(3, 'ref3', 82);

delete from app_table where pk = 23;


select * from app_table order by pk;
select * from app_table_ref order by pk;
select * from record_tracking;

Michel Depiesse

Membre

Re : trigger before delete et foreign key constraint on delete cascade

oops : audit_id doit être bigserial

mais ça ne change rien au problème

Michel Depiesse

Membre

Re : trigger before delete et foreign key constraint on delete cascade

j'ai trouvé

when (pg_trigger_depth() < 1)

merci

excusez-moi d'avoir écrit si vite...

j'ai un client un peu stressant ;-))