oracle禁启用外键和触发器

最新推荐文章于 2022-02-25 18:19:43 发布

最新推荐文章于 2022-02-25 18:19:43 发布 · 145 阅读

文章标签：

#Oracle #SQL #C #C++ #C#

oracle 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中批量启用和禁用外键约束及触发器的方法，通过PL/SQL脚本实现了对外键和触发器状态的自动化管理。

-----启用外键和触发器--

BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R')
loop
begin
EXECUTE IMMEDIATE c.v_sql;

end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop

begin
execute immediate c.v_sql;

end;
end loop;
end;

--禁用外键和触发器----

BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop

begin
EXECUTE IMMEDIATE c.v_sql;

end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop

begin
execute immediate c.v_sql;

end;
end loop;
end;