Home » SQL & PL/SQL » SQL & PL/SQL » DDL in Trigger ?
DDL in Trigger ? [message #203653] Wed, 15 November 2006 23:47 Go to next message
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

Re: DDL in Trigger ? [message #203659 is a reply to message #203653] Thu, 16 November 2006 00:08 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: DDL in Trigger ? [message #203685 is a reply to message #203653] Thu, 16 November 2006 00:53 Go to previous messageGo to next message
michael_bialik
Messages: 611
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.
Re: DDL in Trigger ? [message #203686 is a reply to message #203653] Thu, 16 November 2006 00:53 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: DDL in Trigger ? [message #203709 is a reply to message #203686] Thu, 16 November 2006 01:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why have you put a commit in that transaction?
Re: DDL in Trigger ? [message #203723 is a reply to message #203709] Thu, 16 November 2006 02:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Blindly copied from Doc.... 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

By
Vamsi
Re: DDL in Trigger ? [message #203754 is a reply to message #203723] Thu, 16 November 2006 03:51 Go to previous messageGo to next message
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 ?
Re: DDL in Trigger ? [message #203765 is a reply to message #203754] Thu, 16 November 2006 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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'?
Re: DDL in Trigger ? [message #203773 is a reply to message #203765] Thu, 16 November 2006 04:50 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
now i have ginve grant alter system to gkmnew
and its working
thank you
Re: DDL in Trigger ? [message #203775 is a reply to message #203773] Thu, 16 November 2006 05:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 '').
Re: DDL in Trigger ? [message #204101 is a reply to message #203653] Fri, 17 November 2006 16:28 Go to previous messageGo to next message
Bill B
Messages: 1482
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;
Re: DDL in Trigger ? [message #204335 is a reply to message #203653] Mon, 20 November 2006 03:39 Go to previous messageGo to next message
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
Re: DDL in Trigger ? [message #204373 is a reply to message #204335] Mon, 20 November 2006 06:49 Go to previous messageGo to next message
Bill B
Messages: 1482
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.
icon3.gif  Re: DDL in Trigger ? [message #204548 is a reply to message #203653] Tue, 21 November 2006 03:53 Go to previous messageGo to next message
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;
/
Re: DDL in Trigger ? [message #204593 is a reply to message #204548] Tue, 21 November 2006 06:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
Try this.This should work

should work or does work?
And did you try this yourself?
Previous Topic: Recursive BOM
Next Topic: Invalid username in sqlplus but can "connect" with same details
Goto Forum:
  


Current Time: Sat Dec 03 01:31:13 CST 2016

Total time taken to generate the page: 0.12734 seconds