Creating database triggers through CFQUERY?

[joeDangelo]

Greetings fellow CF'ers. I'm trying to create a web interface into our Oracle
database for some of our developers to use. I intended for them to be able to
Add a table and my app would take care of the table script along with the
sequence and trigger needed to autoincrement the primary key of their table.

I can create/alter tables and add sequences, but for some reason I'm having
problems with the trigger. The trigger will be created in the database if the
script is valid, but when I try to run an insert on that table I get an error
saying that the trigger is invalid. I can see the trigger exists in the
USER_TRIGGERS table, and the trigger is active.

I've tried creating the script with and without the semicolon and forward
slash at the end with no luck.

Any suggestions ?

[paross1]

An example of your code might help identify specific problems. Also, you could
be having problems with the schema of the table that you are creating verses
the trigger owner, and what userID is creating it, and the ownership of the
tables referenced in the trigger, etc.

Phil

[joeDangelo]

I'm not permitted to paste any of our code here, but the jist of the question
is 'Is there a syntax I should be using to create a trigger in Oracle through
Coldfusion?' I know the trigger syntax Im using is correct for SQL+ because
the trigger works just fine when I create it like that.

[paross1]

I don't think that you can use the CREATE OR REPLACE TRIGGER syntax from within
a CFQUERY. I tried this with one of my known good triggrers, using the syntax
below:

<cfquery NAME="Q1" DATASOURCE="dsn">
CREATE OR REPLACE TRIGGER xxx
AFTER DELETE OR INSERT OR UPDATE
ON zzz
FOR EACH ROW
DECLARE
BEGIN
trigger body
END;
</cfquery>

And when I updated the table with the trigger, I got the following error:

ERROR at line 1:
ORA-04098: trigger 'xxx' is invalid and failed re-validation

Once I re-compiled the trigger from SQL Plus, it worked OK. When I listed the
contents of the trigger_body stored in user_triggers, I noticed that there were
some "extra" garbage characters at the end of each line when I attempted to
create the trigger via a ColdFusion CFQUERY.

Sorry, don't know what you can use to stop that from happenning.

Phil

[joeDangelo]

Ah... thanks Phil. Thats what I was looking for... someone to tell me I'm not crazy.

Did you try that in MX 7 ? We havent had a chance to upgrade to that yet...

- J

[paross1]

Yup, tested with CFMX 7.

Phil

[Unregistered]

It could be the line breaks.

per.djurner.net/creating-oracle-triggers-from-coldfusion