2 files changed, 93 insertions, 0 deletions
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index ac132b042d5..2f8029a2f73 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2257,6 +2257,58 @@ create trigger my_table_multievent_trig
   for each statement execute procedure dump_insert();
 ERROR:  Transition tables cannot be specified for triggers with more than one event
 drop table my_table;
+--
+-- Test firing of triggers with transition tables by foreign key cascades
+--
+create table refd_table (a int primary key, b text);
+create table trig_table (a int, b text,
+  foreign key (a) references refd_table on update cascade on delete cascade
+);
+create trigger trig_table_insert_trig
+  after insert on trig_table referencing new table as new_table
+  for each statement execute procedure dump_insert();
+create trigger trig_table_update_trig
+  after update on trig_table referencing old table as old_table new table as new_table
+  for each statement execute procedure dump_update();
+create trigger trig_table_delete_trig
+  after delete on trig_table referencing old table as old_table
+  for each statement execute procedure dump_delete();
+insert into refd_table values
+  (1, 'one'),
+  (2, 'two'),
+  (3, 'three');
+insert into trig_table values
+  (1, 'one a'),
+  (1, 'one b'),
+  (2, 'two a'),
+  (2, 'two b'),
+  (3, 'three a'),
+  (3, 'three b');
+NOTICE:  trigger = trig_table_insert_trig, new table = (1,"one a"), (1,"one b"), (2,"two a"), (2,"two b"), (3,"three a"), (3,"three b")
+update refd_table set a = 11 where b = 'one';
+NOTICE:  trigger = trig_table_update_trig, old table = (1,"one a"), (1,"one b"), new table = (11,"one a"), (11,"one b")
+select * from trig_table;
+ a  |    b    
+----+---------
+  2 | two a
+  2 | two b
+  3 | three a
+  3 | three b
+ 11 | one a
+ 11 | one b
+(6 rows)
+
+delete from refd_table where length(b) = 3;
+NOTICE:  trigger = trig_table_delete_trig, old table = (2,"two a"), (2,"two b")
+NOTICE:  trigger = trig_table_delete_trig, old table = (11,"one a"), (11,"one b")
+select * from trig_table;
+ a |    b    
+---+---------
+ 3 | three a
+ 3 | three b
+(2 rows)
+
+drop table refd_table, trig_table;
 -- cleanup
 drop function dump_insert();
 drop function dump_update();
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index b10159a1cf2..c6deb56c507 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1771,6 +1771,47 @@ create trigger my_table_multievent_trig
 
 drop table my_table;
 
+--
+-- Test firing of triggers with transition tables by foreign key cascades
+--
+
+create table refd_table (a int primary key, b text);
+create table trig_table (a int, b text,
+  foreign key (a) references refd_table on update cascade on delete cascade
+);
+
+create trigger trig_table_insert_trig
+  after insert on trig_table referencing new table as new_table
+  for each statement execute procedure dump_insert();
+create trigger trig_table_update_trig
+  after update on trig_table referencing old table as old_table new table as new_table
+  for each statement execute procedure dump_update();
+create trigger trig_table_delete_trig
+  after delete on trig_table referencing old table as old_table
+  for each statement execute procedure dump_delete();
+
+insert into refd_table values
+  (1, 'one'),
+  (2, 'two'),
+  (3, 'three');
+insert into trig_table values
+  (1, 'one a'),
+  (1, 'one b'),
+  (2, 'two a'),
+  (2, 'two b'),
+  (3, 'three a'),
+  (3, 'three b');
+
+update refd_table set a = 11 where b = 'one';
+
+select * from trig_table;
+
+delete from refd_table where length(b) = 3;
+
+select * from trig_table;
+
+drop table refd_table, trig_table;
+
 -- cleanup
 drop function dump_insert();
 drop function dump_update();