Home » SQL & PL/SQL » SQL & PL/SQL » Create a trigger in a procedure
Create a trigger in a procedure [message #341835] Wed, 20 August 2008 08:53 Go to next message
parmaksiza
Messages: 1
Registered: August 2008
Junior Member
Hi everybody,

I want to create a trigger in a procedure. For ex;

create or replace procedure pr
as

create or replace trigger tr
.......

end tr;

end pr;



I want to create a trigger in my procedure code after i execute

my procedure.

Can you help me?




Re: Create a trigger in a procedure [message #341836 is a reply to message #341835] Wed, 20 August 2008 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: Create a trigger in a procedure [message #341838 is a reply to message #341835] Wed, 20 August 2008 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you help me?

Yes, just use Search field, this has been asked many times.

Regards
Michel
icon10.gif  Re: Create a trigger in a procedure [message #341839 is a reply to message #341836] Wed, 20 August 2008 09:03 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi,

Try creating triggers inside procedures using dynamic creation of triggers using.

EXECUTE IMMEDIATE 'CREATE OR REPLACE trigger ......';

Thanks,
Priya.



Re: Create a trigger in a procedure [message #341841 is a reply to message #341835] Wed, 20 August 2008 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>EXECUTE IMMEDIATE 'CREATE OR REPLACE trigger ......';
If any duhveloper put that in any code I reviewed, would be reassigned, retrained or terminated.

Such a practice is bad, BAD, BAD for a number of reasons!
icon10.gif  Re: Create a trigger in a procedure [message #341843 is a reply to message #341841] Wed, 20 August 2008 09:10 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi anacedent,

As stated, it would be a bad practice, would like to get the methodology which we could use.

Thanks,
Priya.

Re: Create a trigger in a procedure [message #341853 is a reply to message #341843] Wed, 20 August 2008 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the same way the original question has been asked and answered many times, your question has been asked and answered many times, more in the same topics.

Regards
Michel
Re: Create a trigger in a procedure [message #341855 is a reply to message #341843] Wed, 20 August 2008 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
priyamalhotra wrote on Wed, 20 August 2008 07:10


Hi anacedent,

As stated, it would be a bad practice, would like to get the methodology which we could use.

Thanks,
Priya.




http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1141

Trigger, like any other database object, should be created once from simple SQL which is application DDL maintained under source control.

In my opinion, there is never any justification for doing DDL on the fly from a PL/SQL procedure.
Doing DDL from Pl/SQL is explicit admission of a flawed design.
Re: Create a trigger in a procedure [message #341872 is a reply to message #341835] Wed, 20 August 2008 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

Some consider triggers to be "evil" & should be avoided.
Re: Create a trigger in a procedure [message #341884 is a reply to message #341872] Wed, 20 August 2008 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tom wrote a wrap-up on this issue at last!
Now maybe we will be heard when we'll say "don't do that!"

Regards
Michel
Re: Create a trigger in a procedure [message #341885 is a reply to message #341835] Wed, 20 August 2008 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You can lead some folks to knowledge but you can't make them think!
icon6.gif  Re: Create a trigger in a procedure [message #341977 is a reply to message #341885] Wed, 20 August 2008 23:46 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member

Hi,

>By anacedent
>EXECUTE IMMEDIATE 'CREATE OR REPLACE trigger ......';
If any duhveloper put that in any code I reviewed, would be reassigned, retrained or terminated.

Such a practice is bad, BAD, BAD for a number of reasons! 

and the link provided is

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1141

which don't state any idea about "Such a practice is bad, BAD, BAD for a number of reasons! ".

Would be more helpful if you provide the correct link or method to create the trigger using procedures other than "EXECUTE IMMEDIATE".

Thanks,
Priya.

Re: Create a trigger in a procedure [message #341979 is a reply to message #341835] Thu, 21 August 2008 00:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Quote:

Would be more helpful if you provide the correct link or method to create the trigger using procedures other than "EXECUTE IMMEDIATE".



What part of

Quote:

Trigger, like any other database object, should be created once from simple SQL which is application DDL maintained under source control.

In my opinion, there is never any justification for doing DDL on the fly from a PL/SQL procedure.
Doing DDL from Pl/SQL is explicit admission of a flawed design.



do you NOT understand?

Please explain why EXECUTE IMMEDIATE is in this discussion.
Please explain why is it justified & necessary to ABuse PL/SQL to create database object at run time.
Why were these objects NOT designed, tested & optomized before the PL/SQL was ever conceived?
Do you create ALL & EVERY database objects with PL/SQL?
If not, why not?
If so, how do you create the PL/SQL to create the other objects?
How do you differentiate those objects created with PL/SQL to those not created with PL/SQL???????????????????
Re: Create a trigger in a procedure [message #342050 is a reply to message #341977] Thu, 21 August 2008 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are missing the point. It's not that this way of using a procedure to create a trigger is bad, but there are other, good ways of creating triggers in procedure.

It's that Creating triggers in procedures (or functions, or packages) is bad. Period.
Re: Create a trigger in a procedure [message #342053 is a reply to message #341884] Thu, 21 August 2008 03:25 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Now maybe we will be heard when we'll say "don't do that!"


I really doubt it as you could see in the discussions and the justification in the very same thread.

Regards

Raj
Previous Topic: I Need Select Query....
Next Topic: Can i do this in a Single query
Goto Forum:
  


Current Time: Sat Dec 03 01:19:41 CST 2016

Total time taken to generate the page: 0.23407 seconds