sql - 当 TRIGGER 需要在同一个表中选择和插入行时发生变异表问题

Question

我们有以下要求:

表1 : 复合主键 version, id

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'Y'
 1        124    'E'   'F'     'Y'

现在可以在 table1 上进行任何插入和更新对于给定的 id ，应使用以下属性(由触发器派生)创建一个新行:

Version对于给定的 id，应该增加 1和

大多数当前行应该变为事件状态( active 列设置为 Y )

例如

INSERT INTO table1(id, col1, col2) VALUES (123, 'X', 'Y');

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'N'
 3        123    'X'   'Y'     'Y' 
 1        124    'E'   'F'     'Y'

已创建第 3 行

UPDATE table1 SET col1 = 'F' WHERE id = 124;

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'N'
 3        123    'X'   'Y'     'Y' 
 1        124    'E'   'F'     'N'
 2        124    'F'   'F'     'Y'

创建最后一行

DELETE FROM dbo.table1 WHERE id = 124;

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'N'
 3        123    'X'   'Y'     'Y' 
 1        124    'E'   'F'     'N'
 2        124    'F'   'F'     'N'

id 124 的所有行都变为非事件状态。

这似乎是建模问题，但我们必须使用 TABLE1 和支持触发器来提供此功能。

我们无法绕过修改表格问题，因为我们需要 select max(version)然后插入同一张表，任何人都可以提出解决方法吗？

Answer 1

我不喜欢数据库设计，它很糟糕，但你说你被它卡住了。如何使用带有 INSTEAD OF 触发器的 View ？

1)将表重命名为例如TABLE1_BASE

2) 将 View TABLE1 创建为 SELECT * FROM TABLE1_BASE

3) 添加一个 INSTEAD OF 触发器，如下所示:

create trigger table1_io
instead of insert or update or delete on table1
for each row
begin
  if inserting or updating then 
      update table1_base
      set active = 'N'
      where id = :new.id
      and active = 'y';

      insert into table1_base...;
  elsif deleting then
      update table1_base
      set active = 'N'
      where id = :old.id
      and active = 'y';
  end if;
end;

(类似的东西)