问题描述
嗨，团队，
我有一张名为 “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;
这需要自行更新。
您能帮我创建脚本/触发器吗？
您的所有帮助将不胜感激。
我有一张名为 “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;
这需要自行更新。
您能帮我创建脚本/触发器吗？
您的所有帮助将不胜感激。
专家解答
你可以用它触发 (但请阅读整个答案)
因此，以上是最简单的触发解决方案-我们必须锁定整个表，以确保没有人添加新的经理等，而我们正在进行清理。但它会扫描整个表。如果需要，您可以添加一些复杂性以仅考虑有问题的manager id
但老实说，这更像是一个数据建模问题。在我看来，对主键的数据模型进行校正将是一个更好的选择。然后，一个简单的 “on delete set null' 加上一个虚拟列应该可以解决问题
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>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
