How to disable and enable foreign keys and triggers in batch in database
This article mainly shows you "how to disable and enable foreign keys and triggers in batches in the database". The content is simple and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "how to disable and enable foreign keys and triggers in a batch in the database".
SET SERVEROUTPUT ON SIZE 100000
BEGIN
For c in (select 'alter table "' | | t.owner | |''.'| | t.table_name | | 'disable constraint' | | t.constraint_name as v_sql
From dba_constraints t where t.constraint_type ='R 'and owner in (' CCOD','RECORD','UCDS')
) loop
DBMS_OUTPUT.PUT_LINE (C.V_SQL)
Begin
EXECUTE IMMEDIATE c.v_sql
Exception when others then
Dbms_output.put_line (sqlerrm)
End
End loop
For c in (select 'alter trigger "' | | t.owner | |''.'| | t.trigger_name | |
'disable' as v_sql
From dba_triggers t
Where owner in ('CCOD','RECORD','UCDS')) loop
Dbms_output.put_line (c.v_sql)
Begin
Execute immediate c.v_sql
Exception when others then
Dbms_output.put_line (sqlerrm)
End
End loop
End
/
-enable scripting
SET SERVEROUTPUT ON SIZE 100000
BEGIN
For c in (select 'alter table "' | | t.owner | |''.'| | t.table_name | | 'enable constraint' | | t.constraint_name as v_sql
From dba_constraints t where t.constraint_type ='R 'and owner in (' CCOD','RECORD','UCDS')) loop
DBMS_OUTPUT.PUT_LINE (C.V_SQL)
Begin
EXECUTE IMMEDIATE c.v_sql
Exception when others then
Dbms_output.put_line (sqlerrm)
End
End loop
For c in (select 'alter trigger "' | | t.owner | |''.'| | t.trigger_name | |
'enable' as v_sql
From dba_triggers t
Where owner in ('CCOD','RECORD','UCDS')) loop
Dbms_output.put_line (c.v_sql)
Begin
Execute immediate c.v_sql
Exception when others then
Dbms_output.put_line (sqlerrm)
End
End loop
End
/
The above is all the contents of the article "how to batch disable and enable foreign keys and triggers in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
