CREATE OR REPLACE FUNCTION spot_items_trig () RETURNS TRIGGER AS $end$

	# get important data out of the TD dict
	record = TD["new"]

	if TD["event"] == "INSERT":

		return None

	# handle UPDATE
	if record["stat_active"] or record["stat_in_auction"]:

		# item is active or in auction, so make sure the zone is active
		plan = plpy.prepare("""
		                    UPDATE
		                        adult.zones
		                    SET
		                        stat_active = true
		                    WHERE
		                        zone_id = $1
		                    """,
		                    ["int8"])

		                    plpy.execute(plan, [record["zone_id"]])

	else:

		# item isn't active and isn't in an auction, so if the rest of the zone
		# items aren't active and aren't in an auction, we can set the zone to inactive
		plan = plpy.prepare("""
		                    SELECT
		                        1
		                    FROM
		                        adult.gallery_spot_items
		                    WHERE
		                        zone_id = $1
		                    AND
		                        (stat_active OR stat_in_auction)
		                    """,
		                    ["int8"])

		items = plpy.execute(plan, [record["zone_id"]])

		if len(items) == 0:

			# no other active or auctioned items, let's inactivate the zone
			plan = plpy.prepare("""
			                    UPDATE
			                        adult.zones
			                    SET
			                        stat_active = false
			                    WHERE
			                        zone_id = $1
			                    """,
			                    ["int8"])

			plpy.execute(plan, [record["zone_id"]])

	return None

$end$ LANGUAGE plpythonu;