Ramblings from a Bihari!

Just another WordPress.com weblog

Utilizing JAVA’s power in Derby Database Triggers

In oracle we know that we can do a hell lot of things inside a trigger which is database specific. For example, just consider the following trigger statement….

create or replace trigger <table_name>_u_t after update on <table_name>

referencing new as newRow

for each row

declare counts integer:=0;

begin

select count(*) into counts from <table_name>_dash where  primary_key_column_name = :newRow.primary_key_column_name  ;

if(counts > 0) then update <table_name>_dash set time = sysdate, flag = ‘U’, user_id = ‘error’ where  primary_key_column_name = :newRow.primary_key_column_name  ;

else

insert into <table_name>_dash values( :newRow.primary_key_column_name, sysdate, ‘U’, null, ‘error’); end if;

end;

You can see that almost a procedure is executed inside the trigger statement after update on the table. But these things u can’t do in derby database. Also, it is not that readable code. So here comes using the power of java and getting the above functionality of the trigger in derby database…..

1. First u need to write a method which u want to execute when some update is happening on table.

package com.deepak.test;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

public class TriggerClass {

 

public static int createTrigger(String primaryKeyColumnName, int primaryKeyValue, String tableName, String flag) {

String updateDashTableQry = “update “

+ tableName

+ “_dash set time = current_timestamp, flag = ‘”

+ flag

+ “‘, user_id = ‘error’ “

+ (flag.equalsIgnoreCase(“I”) ? “, inserted_time = current_timestamp”

: “”) + ” where “ + primaryKeyColumnName + ” = “

+ primaryKeyValue;

String insertIntoDashTableQry = “insert into “ + tableName

+ “_dash values( “ + primaryKeyValue + “, current_timestamp, ‘”

+ flag + “‘, “

+ (flag.equalsIgnoreCase(“I”) ? “current_timestamp” : null)

+ “, ‘error’)”;System.

out.println(“update query ::” + updateDashTableQry);

try {Class.forName(

“org.apache.derby.jdbc.EmbeddedDriver”);

Connection conn = DriverManager

.getConnection(“jdbc:derby:deepakdb”);

String query = “select count(*) from “ + tableName + “_dash where “

+ primaryKeyColumnName + ” = “ + primaryKeyValue;int count = 0;

PreparedStatement ps = conn.prepareStatement(query);

ResultSet rs = ps.executeQuery();

if (rs.next()) {

count = rs.getInt(1);

}

if (count > 0) {

PreparedStatement ps1 = conn

.prepareStatement(updateDashTableQry);

ps1.executeUpdate();

} else {

PreparedStatement ps2 = conn

.prepareStatement(insertIntoDashTableQry);

ps2.executeUpdate();

}

conn.close();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

 

return 1;

}

 

}

2. Now u need to create a Function or procedure which will call this createTrigger method written above.

CREATE FUNCTION FUNC_TRIGGER(PK_COLUMN_NAME VARCHAR(50), PK_COLUMN_VALUE INT, TABLE_NAME VARCHAR(50), FLAG VARCHAR(2)) RETURNS INT LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME ‘com.deepak.test.TriggerClass.createTrigger’;

3. And finally you can call this database function created above in a trigger like the following code snippet.

create trigger <TABLE_NAME>_u_t after update on <TABLE_NAME>

 referencing new as newRow 

for each row

MODE DB2SQL

SELECT FUNC_TRIGGER(‘ID’, newRow.ID, ‘TEST_TRIGGER’, ‘U’) from sysibm.sysdummy1; 

I have used sysibm.sysdummy1 because i dint want to pass any of the table data inside the function.

Advertisements

September 17, 2007 - Posted by | techie stuff

4 Comments »

  1. Yositsunez

    Yositsunez

    Trackback by Yositsunez | October 13, 2007 | Reply

  2. Good day!,

    Comment by name | September 1, 2008 | Reply

  3. Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

    Comment by sandrar | September 10, 2009 | Reply

  4. Nice, except for SQL code which does java String concatenation with SQL injection vulnerability and potential connection leaks.

    Comment by critic | March 24, 2011 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: