Re: "Execute immediate" in a trigger

NoName wrote:

> Hello,
>
> is it possible to write an 'execute immediate' command inside a "create
> trigger after insert" ?
> Oracle answers with a "ORA-04092: cannot COMMIT or ROLLBACK in a trigger",
> even if I do not explicitily commit anything, and it seems it is caused by
> the 'execute immediate' statement I wrote.
>
> The statement is a 'create or replace view'.
>
> Thanks

First off COMMIT and ROLLBACK never belong inside a trigger unless the trigger is an autonomous transaction. And in case you think using that pragma is the solution ... I'd advise you against it.

It appears that what you are doing in the trigger is DDL which always contains implicit two commits. The problem here is that you should not be performing DDL in a trigger as it is highly indicative of a terrible design most likely caused by previous experience with SQL Server or Sybase.

I'd suggest you state the problem you are trying to solve with the dynamic SQL and let someone help you with a better way to solve the problem without DDL.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)