CODEWITHSUNDEEP
sqlitetriggersinsert
Paul Adams
Paul Adams

Reputation: 36

SQLite - Insert Trigger on TABLE 1 to conditional update TABLE 2

Background:

My trigger works but is broken. Because of the coding and the UNIQUE value on the target table [tbl_ccc_part], the trigger throws all of the inserted values into the table and silently drops the ones that violate the UNIQUE condition. This causes [tbl_ccc_part] to grow by 2,000 records instead of a couple of hundred.

Question:

How do I effectively limit this trigger so that I get back to the original intent.

Intended Code Walkthrough:

INSERT on [tbl_ccc_inventory] of [tbl_ccc_inventory.[business_level_supplier_id]],[tbl_ccc_inventory.[stock_number]], and other non-relevant records.

TRIGGER looks through [tbl_ccc_part.[business_level_supplier_id]] AND [tbl_ccc_part.[stock_number]]. If there is a match on both, do not INSERT. If there is not a match, INSERT record.

CODE:

  CREATE TRIGGER trg_insert_ccc_inventory AFTER INSERT ON tbl_ccc_inventory
  /*  This trigger automatically updates tbl_ccc_part after entries are
      inserted into the tbl_ccc_inventory.  These entries make several
      assumptions about the values needed for tbl_ccc_part and should
      be verified for accuracy by someone. */
    BEGIN
      INSERT OR IGNORE INTO tbl_ccc_part
        (
          record_id,
          business_level_supplier_id,
          stock_number,
          oem_part_number,
          part_type,
          assembly_indicator,
          insurer_program,
          warranty_type,
          warranty_length,
          shippable_part
        )
      VALUES (
          "A",
          new.business_level_supplier_id,
          new.stock_number,
          new.stock_number,
          "OD",
          "N",
          "N/A",
          "LIMITED",
          "LIMITED",
          "Y");
  END;

Upvotes: 1

Views: 172

Answers (1)

CL.
CL.

Reputation: 180040

To execute a trigger conditionally, use the WHEN clause:

CREATE TRIGGER xxx
AFTER INSERT ON tbl_ccc_inventory
WHEN NOT EXISTS (SELECT 1
                 FROM tbl_ccc_part
                 WHERE business_level_supplier_id = NEW.business_level_supplier_id
                   AND stock_number               = NEW.stock_number)
BEGIN
    INSERT ...;
END;

Upvotes: 1

Related Questions