将版本/历史记录系统添加到数据库表 [已关闭]

你说不想要一个“单独的修订表”，不投票给 FractalizeR 的解决方案，因为这个。好的，这里有一个“一张桌子的解决方案”......但是，请简化/概括您的问题，以便所有访问者获得更好的答案并更好地使用此页面：我认为您的问题与SQL表的“版本控制”有关。

“ISO 2008 SQL”的解决方案，那么我认为它也适用于Microsoft SQL-Server。我在 PostgreSQL 9.1 上测试了它。

在此类问题中，我们可以使用 SQL 视图来“模拟”原始表，并将“版本化表”作为新表，并具有更多属性： * 用于对修订进行排序（排序）和时间登记的新属性; * “可追溯性”的新属性（并非真正必要）。momentcmd

假设您的原始（和常规）表是 。对于版本控制，您必须添加新属性，但其他程序员不需要看到这个新属性...解决方案是将表重命名为 SQL VIEW，并向其他程序员提供 SQL VIEW（作为查询）。ttt_histtt_hist

t是用于显示常规表的 VIEW：仅显示“当前元组”。 是带有“历史元组”的新表。t_hist

假设属性为 a，b。 PS：在我添加以获得更好的性能。tt_histisTopt

 -- ....
 CREATE TABLE  t_hist (
    -- the old attributes for t:
    id integer NOT NULL, -- a primary key of t
    a varchar(10),  -- any attribute
    b integer,      -- any attribute

    -- new attributes for revision control:
    isTop BOOLEAN NOT NULL DEFAULT true, -- "last version" or "top" indicator
    cmd varchar(60) DEFAULT 'INSERT',    -- for traceability
    moment timestamp NOT NULL DEFAULT now(), -- for sort revisions
    UNIQUE(id,moment)
);

CREATE VIEW t AS
  SELECT id,a,b FROM t_hist WHERE isTop;
   -- same, but better performance, as 
   -- SELECT id,a,b FROM t_hist GROUP BY id,a,b HAVING MAX(moment)=moment  

-- Verifies consistency in INSERT:
CREATE FUNCTION t_hist_uniq_trig() RETURNS TRIGGER AS $$
DECLARE
  aux BOOLEAN;
BEGIN
   SELECT true INTO aux FROM t_hist 
   WHERE id=NEW.id AND moment>=NEW.moment;
   IF found THEN -- want removes from top?
     RAISE EXCEPTION 'TRYING TO INCLUDE (ID=%) PREVIOUS TO %', NEW.id, NEW.moment;
    END IF;
    RETURN NEW;
END  $$ LANGUAGE plpgsql;
CREATE TRIGGER uniq_trigs BEFORE INSERT ON t_hist 
    FOR EACH ROW EXECUTE PROCEDURE t_hist_uniq_trig();   

CREATE FUNCTION t_reset_top(integer)  RETURNS BOOLEAN AS $BODY$
    UPDATE t_hist SET isTop=false WHERE isTop=true AND id=$1
    RETURNING true;  -- null se nao encontrado
$BODY$ LANGUAGE sql;

--------
-- Implements INSER/UPDATE/DELETE over VIEW t, 
-- and controls unique id of t:
CREATE OR REPLACE FUNCTION t_cmd_trig() RETURNS TRIGGER AS $$
DECLARE
  aux BOOLEAN;
BEGIN
  aux:=true;
  IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
    aux := t_reset_top(OLD.id); -- rets. true ou NULL
  ELSE
    SELECT true INTO aux FROM t_hist WHERE id=NEW.id AND isTop;
  END IF;
  IF (TG_OP='INSERT' AND aux IS NULL) OR (TG_OP='UPDATE' AND aux) THEN
    INSERT INTO t_hist (id,a,b,cmd) VALUES (NEW.id, NEW.a,NEW.b,TG_OP);
  ELSEIF TG_OP='DELETE' AND aux THEN -- if first delete
    UPDATE t_hist SET cmd=cmd||' AND DELETE AT '||now()
  ELSEIF TG_OP='INSERT' THEN -- fails by not-unique(id)
    RAISE EXCEPTION 'REGISTER ID=% EXIST', NEW.id;
  ELSEIF TG_OP='UPDATE' THEN -- .. redundance, a trigger not goes here
    RAISE EXCEPTION 'REGISTER ID=% NOT EXIST', NEW.id;
  END IF;
  RETURN NEW; -- discarded
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER ins_trigs INSTEAD OF INSERT OR UPDATE OR DELETE ON t 
    FOR EACH ROW EXECUTE PROCEDURE t_cmd_trig();

--  Examples:
INSERT INTO t(id,a,b) VALUES (1,'aaaaaa',3); -- ok
INSERT INTO t(id,a,b) VALUES (1,'bbbbbb',3); -- error
UPDATE t_hist SET a='teste' WHERE id=1;      -- ok
     -- SELECT * from t;        SELECT * from t_hist;
INSERT INTO t(id,a,b) VALUES 
  (2,'bbbbbb',22), -- ok
  (3,'bbbbbb',22), -- ok
  (4,'aaaaaa',2);  -- ok
DELETE FROM t WHERE id=3;
     -- SELECT * from t;        SELECT * from t_hist;

PS：我建议不要尝试将此解决方案用于一个没有视图的表，您的触发器会非常复杂;既不尝试适应继承，其中插入的所有内容都会复制到 .t_histtt_histt