Skip to Main Content
  • Questions
  • How can we execute a SQL script file in SQL trigger and output of this SQL script execution into the log file?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Abhishek.

Asked: May 12, 2022 - 5:48 am UTC

Last updated: May 12, 2022 - 2:18 pm UTC

Version: Oracle19c

Viewed 100+ times

You Asked

How can we execute a SQL script file in SQL trigger and output of this SQL script execution into the log file?

We are automating one of the SQL script file execution.
We want to execute this SQL script file once the data will insert into the table and we want the SQL script file execution in the trigger.

Regards,
Abhishek Bhargava

and Chris said...

If you want to call SQL scripts on the database server, you can use DBMS_SCHEDULER with job type SQL_SCRIPT to run them. For details see:

https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1#sql-script

Buuuuuut...

I'm lost as to why you'd want to do this inside a trigger.

Firstly it's generally best to avoid putting complex logic inside a trigger. This makes your application harder to maintain and can lead to unexpected side-effects.

Assuming that a trigger really is your best solution at the moment, surely you can copy the code in the script into a PL/SQL procedure and call that?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library