Home » SQL & PL/SQL » SQL & PL/SQL » ALTER TABLE in a trigger
ALTER TABLE in a trigger [message #231906] Wed, 18 April 2007 17:08 Go to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hi all,

Is it possible to alter a table in a trigger in Oracle 9.2.0.1?

My trigger looks generically like this:

CREATE OR REPLACE TRIGGER trigger_name
     AFTER INSERT OR UPDATE OR DELETE
     ON table_name
     FOR EACH ROW
DECLARE
     <variables>;
BEGIN
     IF inserting
     THEN
          <code>;
     END IF;

     IF updating
     THEN
          ALTER TABLE other_table DISABLE CONSTRAINT constraint_name; /*<----- Here's my problem*/
          <more code>;
     END IF;

     <more code>;
END;


When I attempt to compile this trigger Oracle throws error PLS-00103: Encountered symbol "ALTER" when expecting..."

I attempted putting the ALTER TABLE command within a nested BEGIN/END; block with no success.

How can I get the ALTER TABLE to execute? I haven't found anything suggesting that I can't.

Thanks!

[Updated on: Wed, 18 April 2007 17:19]

Report message to a moderator

Re: ALTER TABLE in a trigger [message #231907 is a reply to message #231906] Wed, 18 April 2007 17:19 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>How can I get the ALTER TABLE to execute? I haven't found anything suggesting that I can't.
BAD idea!
One can NOT do DDL directly from PL/SQL.
It can be done by using EXECUTE IMMEDIATE, but should be avoided like the plague.
You've been warned!
Re: ALTER TABLE in a trigger [message #231908 is a reply to message #231907] Wed, 18 April 2007 17:22 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
What makes it a bad idea?

Can you recommend some other, more safer, routes to follow?

My goal is to update some tables that have constraints on them. So I have to lift the constraints, update the tables and then re-apply the constraints (I didn't put that part in the code).

Thanks!
Re: ALTER TABLE in a trigger [message #231909 is a reply to message #231906] Wed, 18 April 2007 17:29 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
BAD & especially BAD in your case.
DDL results in COMMIT; whether your transaction is done or not.
With constraint disabled BAD data can be entered by other sessions.
EXECUTE IMMEDIATE does NOT scale.
Just say NO to EXECUTE IMMEDIATE.
Pretend it does NOT exist.
Others can add their own reasons why it should be avoided.
Re: ALTER TABLE in a trigger [message #231910 is a reply to message #231909] Wed, 18 April 2007 17:32 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Ahhh I see... yes I definitely do not want to use Execute Immediate then.

Does anyone have any other suggestions about how I can alter my table?

Thanks!
Re: ALTER TABLE in a trigger [message #231911 is a reply to message #231906] Wed, 18 April 2007 17:38 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Does anyone have any other suggestions about how I can alter my table?
Do NOT disable the constraint! It exists for a good & valid reason.
What is gained by disabling the constraint other than allowing dirty data into the application?
Re: ALTER TABLE in a trigger [message #231913 is a reply to message #231911] Wed, 18 April 2007 17:51 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
It wouldn't be a permanent disabling (I certainly don't want to do that), just temporary.

I am trying to code for occasions when updating is needed. Temporarily lifting the constraints is the easiest method I can think of but from your earlier post I can see that it is not the safest.

I guess I will have to do some outside the box thinking to solve this one.

Thanks!
Re: ALTER TABLE in a trigger [message #231914 is a reply to message #231906] Wed, 18 April 2007 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
What is gained by disabling the constraint other than allowing dirty data into the application?
>Temporarily lifting the constraints is the easiest method
Easiest to accomplish WHAT?
Why do you want to enter DIRTY data into the the application?
How do you plan on removing the dirty data after it has been commited?
Re: ALTER TABLE in a trigger [message #231915 is a reply to message #231914] Wed, 18 April 2007 18:14 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
I want to insert/update some of the data in T1 into T2. T2 is tied to 2 other tables (T3 and T4) via foreign key constraints. Temporarily lifting the constraints is the easiest method I can think of to update T2 and then subsequently T3 and T4 in order to maintain conformity.

I am confused by what you mean by "dirty" data. Certainly I do not want inconsistency in the database via a crash or other statements executed while the constraints are off. And of course you make a good point of the problems removing inconsistent data when and if it is ever discovered.

Food for thought...
Re: ALTER TABLE in a trigger [message #231916 is a reply to message #231906] Wed, 18 April 2007 18:44 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
If the "new" data already has referential integrity, then simply INSERT in the proper order so the referential integrity is maintained.
INSERT parent records before INSERT child records.
Lather, Rinse, Repeat.

Dirty data is when referential integrity does not exist or is not maintained (i.e. orphaned child record without a parent record).

[Updated on: Wed, 18 April 2007 18:46] by Moderator

Report message to a moderator

Re: ALTER TABLE in a trigger [message #231917 is a reply to message #231916] Wed, 18 April 2007 18:47 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Thanks,

Inserting is what I do for new data in T1, but what about when data is updated in T1? This is when I need to update T2 and is when the constraints with T3 and T4 are enforced.
Re: ALTER TABLE in a trigger [message #231918 is a reply to message #231906] Wed, 18 April 2007 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
I have no idea about the source of your data or your business rules.
I contend that for any data that fails to UPDATE; somebody needs to decide whether new rows should exist in T3 and/or T4 (or NOT).
The PK/FK exist to enforce referential integrity (RI).
Changes (INSERT/UPDATES) which fail due to RI constraints are a Good Thing(TM)! It means some dirty data was kept out of the application.
If the changed data is supposed to get into the application, then somebody needs to INSERT 1 or more parent records into T3 or T4.
Disabling constraints againt T3 & T4 is a recipe for troubles, IMO.
Re: ALTER TABLE in a trigger [message #231954 is a reply to message #231906] Wed, 18 April 2007 22:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

seems you want to set the foreign key constraint DEFERRABLE INITIALLY DEFERRED.
As stated in the documentation
Quote:
This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

However it does not seem to me a good idea to join tables using key which can be changed. Read about this (with some solutions also) on AskTom here and here.
Re: ALTER TABLE in a trigger [message #232142 is a reply to message #231954] Thu, 19 April 2007 11:02 Go to previous message
clintonf
Messages: 82
Registered: May 2006
Member
Thanks, you two, for the discussion.

It gave me some things to think about and likely saved me from some headaches.
Previous Topic: Total sum with Count in same query
Next Topic: set that init.ora file(utl_file_dir).
Goto Forum:
  


Current Time: Tue Dec 06 04:24:44 CST 2016

Total time taken to generate the page: 0.41810 seconds