SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DDL statement in oracle database Trigger

user651239Feb 13 2011 — edited Feb 14 2011

Dear all:

can we write DDL statement in oracle database trigger? If yes,please give me some idea?

Exp: T1 and T2, i required a trigger on "T1" table. when i insert a row in T1 table trigger make some changes (alter table T2) for table "T2".

Could you please help me?

Thanxs in advance.

Abhishek

Hitesh Nirkhey

hi there

when any insert is done on t1 , the DDL statement is fired.

create or replace trigger trigger_name
after insert  on t1
declare
 pragma AUTONOMOUS_TRANSACTION;
begin
 execute immediate 'alter table t2 ......';

end;

Regards
Hitesh

Saubhik

It's a very bad practice and you should avoid using DDLs in PL/SQL blocks (including triggers). It has many drawbacks and may produce an un-scalable application.
You can use NDS to fire an DDL in an pl/sql block.

Achyut K

Hi,
Hi Abhishek,

user651239 wrote:
Dear all:

can we write DDL statement in oracle database trigger? If yes,please give me some idea?

Exp: T1 and T2, i required a trigger on "T1" table. when i insert a row in T1 table trigger make some changes (alter table T2) for table "T2".

Could you please help me?

Thanxs in advance.

Abhishek

Yes.. Its possible to fire DDL statement using pragma autonomous transaction.

CREATE TRIGGER trig_t1
BEFORE INSERT ON T1
DECLARE
pragma autonomous_transaction;
BEGIN
EXECUTE IMMEDIATE 'rename t2 to t2_temp';
END;
END trig_t1;

Can I know the reason,why do you want to fire DDL statements in a trigger?? Does it make any sense to any business logic..??

Regards,
Achyut

user651239

Thanks Hitesh !!!

user651239

Thanks Achyut !!! yes u r right, this is not good practice. i'll let know !!!!
thanks for your help bro !!!!

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 14 2011

Added on Feb 13 2011

5 comments

1,540 views

SQL & PL/SQL

DDL statement in oracle database Trigger

Comments

Post Details