0

Below are my example queries:

        """
CREATE TABLE IF NOT EXISTS projects (
    Project_ID  TEXT UNIQUE,
    Initial_Project_count   TEXT,
    Additional_Project_count    TEXT,
    Start_Date  TEXT,
    End_Date    TEXT,
    Total_Project_count       TEXT,
    Total_persons  TEXT
    )"""


"""
CREATE TABLE IF NOT EXISTS persons_tb(
    Project_ID  TEXT,
    Initial_count   TEXT,
    Additional_count    TEXT,
    Total_count TEXT,
    persons TEXT,
    is_count_done   TEXT,
        count_Started_Date TEXT,
        count_End_Date TEXT
        )""",

    """
CREATE TRIGGER IF NOT EXISTS after_update_on_projects
    AFTER UPDATE
    ON projects
BEGIN
    UPDATE persons_tb
    SET count_Started_Date = NEW.Start_Date, count_End_Date = NEW.End_Date,
    Initial_count = NEW.Initial_Project_count, Additional_count = NEW.Additional_Project_count, Total_count = NEW.Total_Project_count
    WHERE Project_ID = NEW.Project_ID;
END;
""",

    """
CREATE TRIGGER IF NOT EXISTS after_update_count_pro
    AFTER UPDATE
    ON persons_tb
BEGIN
    UPDATE projects
    SET Total_persons =  Total_persons + NEW.persons
    WHERE Project_ID = NEW.Project_ID and NEW.is_count_done ="count done";
END;
"""

I want to update some columns on two tables with using triggers with after update. For example: If I update Start_Date or End_Date or Initial_Project_count on projects(table) that should be updated on persons_tb(table). And if I update persons on persons_tb(table) that should be updated on projects(table). This is working. However, the second trigger(after_update_count_pro) is also running itself automatically if first trigger doing anything. How to avoid this so the triggers will run individually.

9
  • Table 1 is updated. Its trigger fires. It updates table 2. Its trigger fires. It updates table 1. Its trigger fires. It updates table 2. Its tri... and so on again and again. PS. All your cross-update looks as a mistake.
    – Akina
    Jan 6 at 9:21
  • @Akina. How to avoid this cross-updates. Is there any other way to create the triggers as of my requirement. Jan 6 at 9:27
  • Unrelated, but are you aware that there are other data types than text?
    – Lennart
    Jan 6 at 10:15
  • @ Lennart. I need to use text type only. And Is there any way to update the required column only with trigger. Jan 6 at 10:50
  • How to avoid this cross-updates. Is there any other way to create the triggers as of my requirement. Normalize. Maybe even by creating additional table.
    – Akina
    Jan 6 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.