Oracle 触发清理关联行

嗨，团队，

我有一张名为 “t” 的表。请找到表格的结构:

创建表t
(t_id号 (5)，
身份证号码 (5)，
名称字符 (20)，
Mgr_id号 (5)，
约束pk_t主键 (t_id，s_id)
);


插入t值 (1,12，'a'，0);
插入t值 (2,12，'b'，1);
插入t值 (3,11，'c'，1);
插入t值 (4,11，'aa'，2);
插入t值 (5,11，'d'，4);
插入到t值 (6,10，'e'，null);
插入t值 (7,11，'ab'，3);
提交;


我有一个要求，其中每当删除任何行/更新t_id列时，则t_id列具有的任何值 (如果该值在任何行中可用)
mgr_id列，那么该列mgr_id需要更新为0 (零)。
例如:
如果t_id = 2的行被删除

从t_id = 2的t中删除;

然后脚本将在表中查找并找到具有mgr_id = 2的任何行，如果它存在，则需要将其更新为0，即mgr_id = 0

更新设置mgr_id = 0，其中mgr_id = 2;

这需要自行更新。
您能帮我创建脚本/触发器吗？

您的所有帮助将不胜感激。

你可以用它触发 (但请阅读整个答案)

SQL>
SQL> CREATE TABLE t
  2  ( t_id number(5) ,
  3  s_id number(5),
  4  name char(20),
  5  mgr_id number(5),
  6  CONSTRAINT pk_t PRIMARY KEY(t_id, s_id)
  7  );

Table created.

SQL>
SQL> insert into t values(1,12,'a',0);

1 row created.

SQL> insert into t values(2,12,'b',1);

1 row created.

SQL> insert into t values(3,11,'c',1);

1 row created.

SQL> insert into t values(4,11,'aa',2);

1 row created.

SQL> insert into t values(5,11,'d',4);

1 row created.

SQL> insert into t values(6,10,'e',null);

1 row created.

SQL> insert into t values(7,11,'ab',3);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> create or replace
  2  trigger after_delete
  3  after delete
  4  on t
  5  begin
  6    lock table t in exclusive mode;
  7    update t
  8    set mgr_id = 0
  9    where mgr_id not in ( select t_id from t );
 10  end;
 11  /

Trigger created.
SQL>
SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         2         12 b                             1
         3         11 c                             1
         4         11 aa                            2
         5         11 d                             4
         6         10 e
         7         11 ab                            3

7 rows selected.

SQL> delete from t where t_id = 2;

1 row deleted.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         3         11 c                             1
         4         11 aa                            0
         5         11 d                             4
         6         10 e
         7         11 ab                            3

6 rows selected.

复制

因此，以上是最简单的触发解决方案-我们必须锁定整个表，以确保没有人添加新的经理等，而我们正在进行清理。但它会扫描整个表。如果需要，您可以添加一些复杂性以仅考虑有问题的manager id

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> drop trigger after_delete;

Trigger dropped.

SQL>
SQL> create or replace trigger after_delete
  2    for delete on t
  3      compound trigger
  4
  5    mgr_list sys.odcinumberlist := sys.odcinumberlist();
  6
  7    before each row is
  8    begin
  9      mgr_list.extend;
 10      mgr_list(mgr_list.count) := :old.t_id;
 11    end before each row;
 12
 13    after statement is
 14    begin
 15       lock table t in exclusive mode;
 16       update t
 17       set mgr_id = 0
 18       where mgr_id in ( select column_value from table(mgr_list) );
 19
 20    end after statement;
 21
 22  end ;
 23  /

Trigger created.

SQL>
SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         2         12 b                             1
         3         11 c                             1
         4         11 aa                            2
         5         11 d                             4
         6         10 e
         7         11 ab                            3

7 rows selected.

SQL> delete from t where t_id = 2;

1 row deleted.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a                             0
         3         11 c                             1
         4         11 aa                            0
         5         11 d                             4
         6         10 e
         7         11 ab                            3

6 rows selected.
复制

但老实说，这更像是一个数据建模问题。在我看来，对主键的数据模型进行校正将是一个更好的选择。然后，一个简单的 “on delete set null' 加上一个虚拟列应该可以解决问题

SQL>
SQL>
SQL> drop table t cascade constraints purge;

Table dropped.

SQL>
SQL> CREATE TABLE t
  2  ( t_id number(5) ,
  3  s_id number(5),
  4  name char(20),
  5  mgr_id number(5),
  6  CONSTRAINT pk_t PRIMARY KEY(t_id),
  7  constraint fk foreign key ( mgr_id ) references t ( t_id ) on delete set null
  8  );

Table created.

SQL>
SQL>
SQL>
SQL> insert into t values(1,12,'a',null);

1 row created.

SQL> insert into t values(2,12,'b',1);

1 row created.

SQL> insert into t values(3,11,'c',1);

1 row created.

SQL> insert into t values(4,11,'aa',2);

1 row created.

SQL> insert into t values(5,11,'d',4);

1 row created.

SQL> insert into t values(6,10,'e',null);

1 row created.

SQL> insert into t values(7,11,'ab',3);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a
         2         12 b                             1
         3         11 c                             1
         4         11 aa                            2
         5         11 d                             4
         6         10 e
         7         11 ab                            3

7 rows selected.

SQL> delete from t where t_id = 2;

1 row deleted.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID
---------- ---------- -------------------- ----------
         1         12 a
         3         11 c                             1
         4         11 aa
         5         11 d                             4
         6         10 e
         7         11 ab                            3

6 rows selected.

SQL> alter table t add mgr_disp number as ( nvl(mgr_id,0));

Table altered.

SQL> select * from t;

      T_ID       S_ID NAME                     MGR_ID   MGR_DISP
---------- ---------- -------------------- ---------- ----------
         1         12 a                                        0
         3         11 c                             1          1
         4         11 aa                                       0
         5         11 d                             4          4
         6         10 e                                        0
         7         11 ab                            3          3

6 rows selected.

SQL>
SQL>
SQL>
复制