Question 266968 in PostgreSQL Question posted in
The official documentation can be found here.

How to compare two columns from different tables in trigger postgresql

I’m trying to create a trigger, where inserted certain value in column if one value "currentAttemptCount" becomes equal or more to another "attemptCountLimit" from another table

i have two tables

@Table(name = "course_items")
public class CourseItem {
    @Id
    @Column(name = "course_item_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "attempt_count_limit")
    private Integer attemptCountLimit;

@Table(name = "work_course_items")
public abstract class WorkCourseItem {
    @Id
    @Column(name = "work_course_item_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Integer id;
    @Enumerated(EnumType.STRING)
    protected CourseItemState state;
    @ManyToOne
    @JoinColumn(name = "course_item_id", referencedColumnName = "course_item_id")
    protected CourseItem courseItem;
    @Column(name = "current_attempt_count")
    protected Integer currentAttemptCount;

and how should i compare those values? trying something like next, because INNER JOIN doesn’t seem necessary in trigger

    IF ((SELECT attempt_count_limit
         FROM "ts".course_items
         WHERE course_items.course_item_id = new.course_item_id) <=
        (SELECT current_attempt_count
         FROM "ts".work_course_items
         WHERE work_course_items.work_course_item_id = new.work_course_id))
    THEN
      UPDATE "ts".work_course_items
      SET state = 'YEAP_IT_IS'
      WHERE work_course_item_id = new.work_course_item_id;

    END IF;
    RETURN new;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE TRIGGER quiz_attempt_count
   BEFORE UPDATE OF state
   ON "ts".work_course_items
   FOR EACH ROW
EXECUTE PROCEDURE change_work_course_state();
Tags:

2

Answers


  1. 0

    Here is how you can compare two col from diff tbs

    PL/pgSQL Example:

    CREATE OR REPLACE FUNCTION change_work_course_state() RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM "ts".course_items ci
    JOIN "ts".work_course_items wci
    ON ci.course_item_id = new.course_item_id
    WHERE ci.attempt_count_limit <= wci.current_attempt_count
  ) THEN
    UPDATE "ts".work_course_items
    SET state = 'YEAP_IT_IS'
    WHERE work_course_item_id = new.work_course_item_id;
  END IF;

  RETURN new;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER quiz_attempt_count
BEFORE UPDATE OF state
ON "ts".work_course_items
FOR EACH ROW
EXECUTE PROCEDURE change_work_course_state();
    Login or Signup to reply.
  2. 0

    You have an update event that happens when a work_course_items record is being updated. So, you have a single record from this table to operate with inside your trigger. Hence, your IF is superfluous:

        IF ((SELECT attempt_count_limit
         FROM "ts".course_items
         WHERE course_items.course_item_id = new.course_item_id) <=
        (SELECT current_attempt_count
         FROM "ts".work_course_items
         WHERE work_course_items.work_course_item_id = new.work_course_id))

    Instead of the second query, you could simply pass new.current_attempt_count to your `procedure, because you already have that value in the trigger.

    As about your first query, its logic is correct.

    You will need to actually try anything that you implement and make sure that it works properly though.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search