0

Is it possible to automate the creation of triggers in Oracle SQL, like if a drop table command is ran, not all of your triggers have to be recreated? I didn't find anything online to solve this problem. Thanks in advance for your answers

David
  • 15
  • 3
  • 4
    Don't `drop` the table. `truncate` it instead. – Gordon Linoff Nov 17 '19 at 22:01
  • This seems the same as following question: https://stackoverflow.com/questions/48117959/does-dropping-a-table-drop-its-dependent-trigger?noredirect=1&lq=1 – Popeye Nov 18 '19 at 02:27
  • Why are you dropping and re-creating tables? This is not normal behaviour, especially with the kind of tables which have triggers. So perhaps you should explain the business problem you're trying to solve and maybe we can suggest a better approach. But perhaps all you need to know is: **put all your DDL scripts into source control**; then, if you need to re-create a table you'll have all the necessary statements to create it and its dependent objects. – APC Nov 18 '19 at 06:42

1 Answers1

1

Put simply, no. When a table is dropped, everything associated with it goes away, including all indexes, triggers, and etc. If the table is then recreated, all the triggers must be recreated. This is hard-wired into the database, and there's no DDL statement for DROP TABLE XYZ123 EXCEPT FOR ALL THE TRIGGERS WHICH YOU CAN JUST LEAVE FLOATING AROUND IN SPACE UNTIL AND IF THE TABLE IS RECREATED;

As someone else mentioned, you might want to consider using TRUNCATE TABLE, which blows the data away but leaves everything else intact. Another option is to use a global temp table - see this article at Oracle-Base

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110