Re: Oracle Trigger

From: Rajesh Patel <rpatel_at_rajix.com>
Date: Tue, 3 Feb 2004 22:25:08 GMT
Message-ID: <HsJ4xx.7Js@news.boeing.com>

Okay, I know what the problem is:
I was running in a sql file that used to contain only create statements. So I was tokenizing the whole file based on semicolons and sending each individual Create statement to the database.

The trigger:
CREATE OR REPLACE TRIGGER TI_MDA_PART

	   INSTEAD OF INSERT
	   ON V_MDA_PART
	   REFERENCING NEW AS N OLD AS OLD
	   FOR EACH ROW
	   BEGIN
			INSERT INTO MDA_PART (MATERIAL_CODE,PART_NUMBER) 		 
VALUES(:n.MATERIAL_CODE, :n.PART_NUMBER, :n.NAME);
	   END;

So breaking it up by semicolons is going to cause me lots of grief....

Raj

Andy Hassall wrote:

> On Tue, 03 Feb 2004 19:58:53 +0000, Andy Hassall <andy_at_andyh.co.uk> wrote:
>
>

>>On Tue, 3 Feb 2004 16:39:21 GMT, Rajesh Patel <rpatel_at_rajix.com> wrote:
>>
>>
>>>I had a sql file that just had Creates for
>>>tables/views.  Then I tried to add a trigger.
>>>
>>>I was using a jdbc connection to send the sql
>>>file to the database.  That worked great until
>>>I added the trigger.  I kept getting errors from
>>>oracle.
>>
>>Presumably you have ':new' or ':old' in the trigger. This would get
>>interpreted as a bind variable from most interfaces. You may be able to
>>suppress this depending on your interface, or maybe not. I don't know whether
>>you can in JDBC, haven't worked with it much.
>>
>>But anyway - post the error message, as it may be something entirely
>>different.

>
>
> Can create triggers with JDBC from here, anyway. :new doesn't seem to cause a
> problem, even with a PreparedStatement.
>
> import java.sql.*;
> import java.math.*;
> import java.io.*;
> import java.awt.*;
>
> class JdbcTest {
> public static void main (String args []) throws SQLException {
> // Load Oracle driver
> DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
> // Connect to the local database
> Connection conn = DriverManager.getConnection
> ("jdbc:oracle:oci:@dev92lnx", "test", "test");
>
> Statement stmt = conn.createStatement();
>
> try {
> stmt.executeUpdate("drop table t");
> }
> catch (SQLException e) {
>
> }
> stmt.executeUpdate("create table t (c1 number)");
>
> String trig = "create or replace trigger testtrigger\n" +
> "after insert on t\n" +
> "for each row\n" +
> "declare\n" +
> " v t.c1%type;\n" +
> "begin\n" +
> " v := :new.c1;\n" +
> "end;";
>
> System.out.println(trig);
>
> stmt.executeUpdate(trig);
> System.out.println("Trigger created.");
>
> PreparedStatement prepstmt = conn.prepareStatement(trig);
> prepstmt.executeUpdate();
> System.out.println("Trigger created.");
>
> stmt.close();
> conn.close();
> }
> }
>
> Outputs:
>
> create or replace trigger testtrigger
> after insert on t
> for each row
> declare
> v t.c1%type;
> begin
> v := :new.c1;
> end;
> Trigger created.
> Trigger created.
>
> So... post some errors and/or some code.
>
Received on Tue Feb 03 2004 - 16:25:08 CST