元ネタ→http://blogs.wankuma.com/episteme/archive/2008/03/22/129054.aspx
別回答→http://blogs.wankuma.com/hatsune/archive/2008/03/22/129070.aspx
SQLLiteは手元にないのでOracle11gでやってみた。
もともとincome/outgoがあって、この制約が”後だしジャンケン”的にくっつけざるを得ないときという前提で、今回はトリガーを使って制限をかけています。
ただ、本当にやるとしたら、許されるとしたらテーブル定義変えてしまいます。
だから、これは本当に暫定的な緊急処置です。
DROP TABLE cashbox;
CREATE TABLE cashbox
( key NUMBER PRIMARY KEY,
setdate DATE NOT NULL,
summary VARCHAR2(20),
income NUMBER,
outgo NUMBER,
note VARCHAR2(20)
)
/
CREATE SEQUENCE autonumber
/
CREATE OR REPLACE TRIGGER cashbox_BI
BEFORE INSERT ON cashbox FOR EACH ROW
BEGIN
IF (:NEW.income IS NULL AND :NEW.outgo IS NULL) OR
(:NEW.income IS NOT NULL AND :NEW.outgo IS NOT NULL)THEN
RAISE VALUE_ERROR;
END IF;
-- Oracle 11g
:NEW.key := autonumber.NEXTVAL;
-- Oracle 10g
-- SELECT autonumber.NEXTVAL INTO :NEW.key FROM DUAL;
END;
/
CREATE OR REPLACE TRIGGER cashbox_BU
BEFORE UPDATE ON cashbox FOR EACH ROW
BEGIN
IF (:NEW.income IS NULL AND :NEW.outgo IS NULL) OR
(:NEW.income IS NOT NULL AND :NEW.outgo IS NOT NULL)THEN
RAISE VALUE_ERROR;
END IF;
END;
/
CREATE INDEX XIE_cashbox ON cashbox ( setdate )
/
-- レコードをいくつか挿入
INSERT INTO cashbox
( setdate, summary, outgo )
VALUES ( '2008/02/29', 'leftover', 380);
INSERT INTO cashbox
( setdate, summary, income )
VALUES ( '2008/03/01', 'carryover', 380);
INSERT INTO cashbox
( setdate, summary, income )
VALUES ( '2008/03/01', 'from-mama', 5000);
INSERT INTO cashbox
( setdate, summary, outgo )
VALUES ( '2008/03/03', 'comic', 410);
INSERT INTO cashbox
( setdate, summary, outgo )
VALUES ( '2008/03/02', 'potato-chips', 147);
INSERT INTO cashbox
( setdate, summary, income )
VALUES ( '2008/03/04', 'from-granpa', 1000);
-- 金額を入れない
INSERT INTO cashbox ( setdate, summary )
VALUES ( '2008/03/04', 'from-granpa');
行2でエラーが発生しました。:
ORA-06502: PL/SQL: 数値または値のエラーが発生しました
-- 両方にいれちゃう
INSERT INTO cashbox ( setdate, summary, income, outgo )
VALUES ( '2008/03/04', 'from-granpa', 50, 50);
行1でエラーが発生しました。:
ORA-06502: PL/SQL: 数値または値のエラーが発生しました
-- 全部出力してみる
SELECT * FROM cashbox ORDER BY setdate,key;
KEY SETDATE SUMMARY INCOME OUTGO
---------- -------- -------------------- ---------- ----------
21 08-02-29 leftover 380
22 08-03-01 carryover 380
23 08-03-01 from-mama 5000
25 08-03-02 potato-chips 147
24 08-03-03 comic 410
26 08-03-04 from-granpa 1000
-- 三月の収支
SELECT sum(income),sum(outgo),sum(income)-sum(outgo)
FROM cashbox
WHERE TRUNC(setdate,'MM')=TO_DATE('20080301','YYYYMMDD')
;
SUM(INCOME) SUM(OUTGO) SUM(INCOME)-SUM(OUTGO)
----------- ---------- ----------------------
6380 557 5823