OraFAQ Forum: SQL & PL/SQL » DDL in Trigger ?

Home » SQL & PL/SQL » SQL & PL/SQL » DDL in Trigger ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DDL in Trigger ? [message #203653]

Wed, 15 November 2006 23:47

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

Create or replace trigger triggername
before insert or update on connectinfo
for each row
begin
if inserting then
execute immediate 'alter...'
end if;
end

when i create the above trigger it gives the following error;
'trigger created with compilation error';

Can anybody give the correct syntax ?

regards muthu

Report message to a moderator

Re: DDL in Trigger ? [message #203659 is a reply to message #203653]

Thu, 16 November 2006 00:08

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Type

SHOW ERRORS

after you create it in SQL*Plus. It's probably because you are missing a semi-colon after the EXECUTE IMMEDIATE statement and the final END.

Ross Leishman

Report message to a moderator

Re: DDL in Trigger ? [message #203685 is a reply to message #203653]

Thu, 16 November 2006 00:53

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

You can NOT issue DDL intrigger because ANY DDL implies COMMIT.

Try calling a stored proc with PRAGMA AUTONOMOUS TRANSACTION and
performing DDL inside the proc.
However, if trigger fails - you will NOT be able to rollback the DDL.

HTH.

Report message to a moderator

Re: DDL in Trigger ? [message #203686 is a reply to message #203653]

Thu, 16 November 2006 00:53

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Quote:

From Documentaion:
Unlike regular triggers, autonomous triggers can execute DDL statements using native dymanic SQL.

Example:

CREATE OR REPLACE TRIGGER drop_temp_table
AFTER INSERT ON emp_audit
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE temp_audit';
    COMMIT;
END;
/

By
Vamsi

Report message to a moderator

Re: DDL in Trigger ? [message #203709 is a reply to message #203686]

Thu, 16 November 2006 01:57

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Why have you put a commit in that transaction?

Report message to a moderator

Re: DDL in Trigger ? [message #203723 is a reply to message #203709]

Thu, 16 November 2006 02:30

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Blindly copied from Doc.... Cool

Cool

Oracle� Database
PL/SQL User's Guide and Reference
10g Release 2 (10.2)
B14261-01
June 2005

209 of 496

Commit not required, as it is a DDL. Razz

Razz

By
Vamsi

Report message to a moderator

Re: DDL in Trigger ? [message #203754 is a reply to message #203723]

Thu, 16 November 2006 03:51

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

my actual trigger is as follows

connect system/manager@oracle9i
GRANT ADMINISTER DATABASE TRIGGER TO GKMNEW

create or replace trigger trig1
after insert or update on connectinfo
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'alter system set fixed_date=none';
end;

after creation of trigger
when i update the connectinfo table

the following error is comming

ORA-01031:insufficient privileges
ORA-06512: at "gkmnew.trig1", line4
ORA-04088: ERROR DURING EXECUTION
GENERAL SQL ERROR. TRIGGER "gkmnew.trig1"

WHAT WILL BE THE PROBLEM ?

Report message to a moderator

Re: DDL in Trigger ? [message #203765 is a reply to message #203754]

Thu, 16 November 2006 04:34

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Are you sure that 'gkmnew' user should be able to alter system?

If yes, did you grant ALTER SYSTEM to 'gkmnew'?

Report message to a moderator

Re: DDL in Trigger ? [message #203773 is a reply to message #203765]

Thu, 16 November 2006 04:50

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

now i have ginve grant alter system to gkmnew
and its working
thank you

Report message to a moderator

Re: DDL in Trigger ? [message #203775 is a reply to message #203773]

Thu, 16 November 2006 05:05

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

but the following trig2 is giving error

create or replace trigger trig2
before insert or update on connectinfo
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'alter system set fixed_date = '1980_01_01_00_00-00'';
end;

trigger created with compilation error .

[Updated on: Thu, 16 November 2006 05:06]

Report message to a moderator

Re: DDL in Trigger ? [message #203786 is a reply to message #203775]

Thu, 16 November 2006 05:30

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle complains because your EXECUTE IMMEDIATE ends when it finds a single quote after equation sign, while the rest is unknown.

When using strings with single quotes, you have to double them (not use double quotes ", but two consecutive single quotes '').

Report message to a moderator

Re: DDL in Trigger ? [message #204101 is a reply to message #203653]

Fri, 17 November 2006 16:28

Bill B
Messages: 1971
Registered: December 2004

Senior Member

In otherwords

create or replace trigger trig2
before insert or update on connectinfo
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'alter system set fixed_date = ''1980_01_01_00_00-00'' ';
end;

Report message to a moderator

Re: DDL in Trigger ? [message #204335 is a reply to message #203653]

Mon, 20 November 2006 03:39

kiran9i
Messages: 4
Registered: November 2006
Location: 1

Junior Member

hi
i think u can not write ddl in trigger.
check it out in manual

Report message to a moderator

Re: DDL in Trigger ? [message #204373 is a reply to message #204335]

Mon, 20 November 2006 06:49

Bill B
Messages: 1971
Registered: December 2004

Senior Member

kiran9i wrote on Mon, 20 November 2006 03:39

hi
i think u can not write ddl in trigger.
check it out in manual

Sure you can, it just has to be setup as an autonomous transaction. wither you should is a totally different issue.

Report message to a moderator

Re: DDL in Trigger ? [message #204548 is a reply to message #203653]

Tue, 21 November 2006 03:53

ananth.dikshit
Messages: 21
Registered: November 2006
Location: HYD

Junior Member

Try this.This should work
Create or replace trigger triggername
before insert or update on table
declare
v_sql_alter varchar2(2000);
begin
v_sql_alter := 'alter.......';
if inserting then
execute immediate 'v_sql_alter';
else
end if;
end;
/

Report message to a moderator

Re: DDL in Trigger ? [message #204593 is a reply to message #204548]

Tue, 21 November 2006 06:16

Frank
Messages: 7901
Registered: March 2000

Senior Member

Quote:

Try this.This should work

should work or does work?
And did you try this yourself?

Report message to a moderator

Previous Topic:	Recursive BOM
Next Topic:	Invalid username in sqlplus but can "connect" with same details

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 03:36:20 CDT 2024