Re: Oracle Trigger

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 03 Feb 2004 21:13:44 +0000
Message-ID: <ik3020lm0reihqnpecse357bd8vr87t3uo@4ax.com>

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.

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

Received on Tue Feb 03 2004 - 15:13:44 CST