Create separate ON INSERT and ON UPDATE triggers on tables with foreign

Neil Conway · Neil Conway · commit f99b75b0a0ee · 2005-05-30T06:52:38.000Z
keys, rather than a single trigger for both events. This should not change
functionality, but it is more consistent: previously, there were trigger
functions for both "check_insert" and "check_update", but the former was
used for both events.

Bump catalog version number (not strictly necessary, but best to be
cautious).
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.157 2005/05/10 13:16:26 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.158 2005/05/30 06:52:38 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -4380,52 +4380,33 @@ validateForeignKeyConstraint(FkConstraint *fkconstraint,
 	pfree(trig.tgargs);
 }
 
-/*
- * Create the triggers that implement an FK constraint.
- */
 static void
-createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
-						 Oid constrOid)
+CreateFKCheckTrigger(RangeVar *myRel, FkConstraint *fkconstraint,
+					 ObjectAddress *constrobj, ObjectAddress *trigobj,
+					 bool on_insert)
 {
-	RangeVar   *myRel;
 	CreateTrigStmt *fk_trigger;
 	ListCell   *fk_attr;
 	ListCell   *pk_attr;
-	ObjectAddress trigobj,
-				constrobj;
-
-	/*
-	 * Reconstruct a RangeVar for my relation (not passed in,
-	 * unfortunately).
-	 */
-	myRel = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
-						 pstrdup(RelationGetRelationName(rel)));
 
-	/*
-	 * Preset objectAddress fields
-	 */
-	constrobj.classId = ConstraintRelationId;
-	constrobj.objectId = constrOid;
-	constrobj.objectSubId = 0;
-	trigobj.classId = TriggerRelationId;
-	trigobj.objectSubId = 0;
-
-	/* Make changes-so-far visible */
-	CommandCounterIncrement();
-
-	/*
-	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the
-	 * CHECK action.
-	 */
 	fk_trigger = makeNode(CreateTrigStmt);
 	fk_trigger->trigname = fkconstraint->constr_name;
 	fk_trigger->relation = myRel;
-	fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
 	fk_trigger->before = false;
 	fk_trigger->row = true;
-	fk_trigger->actions[0] = 'i';
-	fk_trigger->actions[1] = 'u';
-	fk_trigger->actions[2] = '\0';
+
+	/* Either ON INSERT or ON UPDATE */
+	if (on_insert)
+	{
+		fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
+		fk_trigger->actions[0] = 'i';
+	}
+	else
+	{
+		fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd");
+		fk_trigger->actions[0] = 'u';
+	}
+	fk_trigger->actions[1] = '\0';
 
 	fk_trigger->isconstraint = true;
 	fk_trigger->deferrable = fkconstraint->deferrable;
@@ -4453,13 +4434,54 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
 		fk_trigger->args = lappend(fk_trigger->args, lfirst(pk_attr));
 	}
 
-	trigobj.objectId = CreateTrigger(fk_trigger, true);
+	trigobj->objectId = CreateTrigger(fk_trigger, true);
 
 	/* Register dependency from trigger to constraint */
-	recordDependencyOn(&trigobj, &constrobj, DEPENDENCY_INTERNAL);
+	recordDependencyOn(trigobj, constrobj, DEPENDENCY_INTERNAL);
 
 	/* Make changes-so-far visible */
 	CommandCounterIncrement();
+}
+
+/*
+ * Create the triggers that implement an FK constraint.
+ */
+static void
+createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
+						 Oid constrOid)
+{
+	RangeVar   *myRel;
+	CreateTrigStmt *fk_trigger;
+	ListCell   *fk_attr;
+	ListCell   *pk_attr;
+	ObjectAddress trigobj,
+				constrobj;
+
+	/*
+	 * Reconstruct a RangeVar for my relation (not passed in,
+	 * unfortunately).
+	 */
+	myRel = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
+						 pstrdup(RelationGetRelationName(rel)));
+
+	/*
+	 * Preset objectAddress fields
+	 */
+	constrobj.classId = ConstraintRelationId;
+	constrobj.objectId = constrOid;
+	constrobj.objectSubId = 0;
+	trigobj.classId = TriggerRelationId;
+	trigobj.objectSubId = 0;
+
+	/* Make changes-so-far visible */
+	CommandCounterIncrement();
+
+	/*
+	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the
+	 * CHECK action for both INSERTs and UPDATEs on the referencing table.
+	 */
+	CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, true);
+	CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, false);
 
 	/*
 	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.270 2005/05/30 01:20:50 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.271 2005/05/30 06:52:38 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200505291
+#define CATALOG_VERSION_NO	200505301
 
 #endif

Original file line number	Diff line number	Diff line change
`@@ -37,7 +37,7 @@`
`37`	`37`	`* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group`
`38`	`38`	`* Portions Copyright (c) 1994, Regents of the University of California`
`39`	`39`	`*`
`40`		`- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.270 2005/05/30 01:20:50 tgl Exp $`
	`40`	`+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.271 2005/05/30 06:52:38 neilc Exp $`
`41`	`41`	`*`
`42`	`42`	`*-------------------------------------------------------------------------`
`43`	`43`	`*/`
`@@ -53,6 +53,6 @@`
`53`	`53`	`*/`
`54`	`54`
`55`	`55`	`/* yyyymmddN */`
`56`		`-#define CATALOG_VERSION_NO 200505291`
	`56`	`+#define CATALOG_VERSION_NO 200505301`
`57`	`57`
`58`	`58`	`#endif`