时间戳

首页 > 解决方案 > 插入前的 SQLite 触发器

database - 插入前的 SQLite 触发器

问题描述

我正在尝试创建一个触发器，它首先过滤 itemID，然后检查该 itemID 的最高bidNo。bidNo 必须仅比上一个大 +1。投标是投标人。

CREATE TRIGGER new_bid_no
BEFORE INSERT ON BID
    BEGIN
        SELECT CASE
        WHEN (SELECT itemID
                FROM bid
                WHERE itemID = NEW.itemID
                GROUP BY itemID
        ) AND (SELECT MAX(bidNo) AS bidNo
                FROM bid
                WHERE NEW.bidNo < bidNo
                OR NEW.bidNo = (bidNo+1)
                GROUP BY itemID
        )THEN
        RAISE (ABORT, 'New bid number has to be 1 higher than the last')
    END;
END;

投标表

CREATE TABLE BID(
buyerUsername    VARCHAR(30) NOT NULL,
itemID            INTEGER NOT NULL,
bidNo             INTEGER NOT NULL,
bidAmount         INTEGER,
bidDate            VARCHAR(10),
bidTime            TIME,
PRIMARY KEY(buyerUsername, itemID, bidNo),
FOREIGN KEY (buyerUsername) REFERENCES BUYER(buyerUsername),
FOREIGN KEY (itemID) REFERENCES ITEM(itemID)
)
WITHOUT ROWID;

标签： databasesqlite

解决方案

首先，您必须定义一个UNIQUE约束itemID, bidNo，这只能在CREATE表的语句中完成，以后不能添加：

CREATE TABLE BID(
  buyerUsername    TEXT NOT NULL,
  itemID           INTEGER NOT NULL,
  bidNo            INTEGER NOT NULL,
  bidAmount        INTEGER,
  bidDate          TEXT,
  bidTime          TEXT,
  PRIMARY KEY(buyerUsername, itemID, bidNo),
  FOREIGN KEY(buyerUsername) REFERENCES BUYER(buyerUsername),
  FOREIGN KEY(itemID) REFERENCES ITEM(itemID),
  UNIQUE(itemID, bidNo)
)
WITHOUT ROWID;

我还将您定义为的列的数据类型更改为VARCHAR，TEXT因为 SQLite 中没有VARCHAR数据类型。即使您这样定义它也将是TEXT. 也没有数据类型TIME。

现在你的触发器可以这样写：

CREATE TRIGGER new_bid_no
BEFORE INSERT ON BID 
WHEN NEW.bidNo <> COALESCE((SELECT MAX(bidNo) FROM bid WHERE itemID = NEW.ItemID AND bidNo < NEW.bidNo), 0) + 1
BEGIN
   SELECT RAISE (ABORT, 'New bid number has to be 1 higher than the last');
END

推荐阅读