Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to Call a SQL Script.
Trigger to Call a SQL Script. [message #238094] Wed, 16 May 2007 13:03 Go to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Hello.
I have a trigger that is currently working correctly. It is an AFTER DELETE OR INSERT OR UPDATE
...
FOR EACH ROW
...
EXCEPTION

END;

My question is, I need to run a sql script as soon that this trigger is done, basically it is a SQL script to up a summary table. So the UPDATE, DELETE or INSERT to my base table need to be complete (via the trigger) and then the call to the SQL script would be done... I do not know how to do that.

Can anyone help?
Thanks!!
Re: Trigger to Call a SQL Script. [message #238098 is a reply to message #238094] Wed, 16 May 2007 13:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Put the code you used in the SQL script into a stored procedure and call this SP from the trigger.
Re: Trigger to Call a SQL Script. [message #238197 is a reply to message #238098] Thu, 17 May 2007 01:35 Go to previous messageGo to next message
prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune
Member
If,

we have any formatting in that SQL script,
is it possible to do the same in Stored Procedure??????

Thanks & Regards,
Prachi
Re: Trigger to Call a SQL Script. [message #238229 is a reply to message #238197] Thu, 17 May 2007 02:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Formatting!!! Do you mean you want to generate a file or report from a trigger?

You might want to re-think this architecture. Generally speaking, this kind of thing is thought of as a "bad idea".

Surely there can be no valid business reason for generating a report for every single row that is inserted, updated, or deleted. The "consumer" of these reports cannot possibly be a human, it has to be another system or process.

If so, then what we are looking at is inter-process communication. Does this communication need to be real-time (ie. as the transaction happens) or can we just store a record of the transaction and then the consumer periodically collects the new transactions in a batch?

For real-time, you can use some kind of queueing technology. If the consumer is also Oracle, you could use Oracle Streams or Advanced Queues. If it is non-Oracle, you could use some other kind like JMS or MQ-Series.

For batch, you could get your triggers to write to another table and get the consumer to pick it up. Or you might want to use Change Data Capture.

It is possible to get a trigger to call an operating system command that will start a SQL*Plus session (on the Oracle server) and run your SQL*Plus script, but you really shouldn't so I'm not going to tell you how to do it (although I'm sure someone else will). Instead, think about what you are REALLY trying to do and choose an appropriate technology.

Ross Leishman
Re: Trigger to Call a SQL Script. [message #242640 is a reply to message #238094] Mon, 04 June 2007 08:59 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
what is the syntax to call a sql script from the trigger.
No parameters, just want to run the sql code.
Re: Trigger to Call a SQL Script. [message #242643 is a reply to message #242640] Mon, 04 June 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't but read:
Littlefoot wrote on Wed, 16 May 2007 20:13
Put the code you used in the SQL script into a stored procedure and call this SP from the trigger.

Regards
Michel
Re: Trigger to Call a SQL Script. [message #242648 is a reply to message #242643] Mon, 04 June 2007 09:28 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
i know I should have used SP, but is I did not, is there anyway to get around it?
I am running on a Unix box, so cron calls everything. Problem I have is that manual updates can be made to table "A", and when that is done I need to be able to fire off the scripts thaupdate the summary table "B". Any suggestions?
Thanks
Re: Trigger to Call a SQL Script. [message #242653 is a reply to message #242648] Mon, 04 June 2007 09:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes :

Quote:
Put the code you used in the SQL script into a stored procedure and call this SP from the trigger


Then you can even change the sql script that is run by cron to just call the same procedure.

And if it's doing the summary I would also loose the "for each row...." in the trigger, so the summary update is done only once when multiple rows are updated in one statement, not once per row.
Re: Trigger to Call a SQL Script. [message #242655 is a reply to message #242648] Mon, 04 June 2007 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Other ways, read:
calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL
AskTom Java procedure for host calls on Unix environment

Regards
Michel
Re: Trigger to Call a SQL Script. [message #242761 is a reply to message #242653] Tue, 05 June 2007 00:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ThomasG wrote on Mon, 04 June 2007 16:47
And if it's doing the summary I would also loose the "for each row...." in the trigger, so the summary update is done only once when multiple rows are updated in one statement, not once per row.

Or skip the update altogether and either select the summary from the source-table when needed or use an MV for that.

You have to believe us all when we tell you that calling a script from a trigger is a baaaad idea!
Previous Topic: function to check whether no is valid
Next Topic: Regarding Commit
Goto Forum:
  


Current Time: Sat Dec 10 05:08:08 CST 2016

Total time taken to generate the page: 0.24451 seconds