Perhaps last question: how to pass as parameter also the table? Also, where can I look to understand why int a would be translated as the column a? This doesn't help: http://developers.sun.com/docs/javadb/10.5.3.0/ref/index.html
2010/2/26, Rick Hillegas <[email protected]>: > Hi Gabriele, > > Here is a vetting procedure which the trigger could call: > > public static void vetRow( int a ) throws SQLException > { > Connection conn = DriverManager.getConnection( > "jdbc:default:connection" ); > PreparedStatement ps = conn.prepareStatement( "select count(*) > from t where a = ?" ); > ps.setInt( 1, a ); > ResultSet rs = ps.executeQuery(); > rs.next(); > int result = rs.getInt( 1 ); > > rs.close(); > ps.close(); > > if ( result >= 4 ) { throw new SQLException( "Too many copies of > value " + a + " in t." ); } > } > > And here again is the sql script which exercises the trigger. I had to > modify the trigger declaration slightly: instead of saying that it > "contains sql" this version says "reads sql data": > > connect 'jdbc:derby:memory:dummy;create=true'; > > create table t( a int ); > > create procedure vetRow( in arg int ) > language java parameter style java reads sql data > external name 'z.vetRow'; > > create trigger t_trig > no cascade before insert on t > referencing new as newRow > for each row call vetRow( newRow.a ); > > insert into t( a ) values ( 1 ); > insert into t( a ) values ( 1 ); > insert into t( a ) values ( 1 ); > insert into t( a ) values ( 1 ); > > -- fails > insert into t( a ) values ( 1 ); > > Depending on your usage pattern, it might make more sense to declare an > AFTER trigger which invokes a vetting procedure once per INSERT/UPDATE > rather than per row. > > Hope this helps, > -Rick > > Gabriele Kahlout wrote: >> The procedure would run the sanity check query and raise an exception >> if the sanity check failed. >> Can you show me an example of how this exception could be thrown? >> >> About the user-coded function, how may I code what I asked for? A loop >> that counts for each distinct value it's occurences, and returns >> true/false if some count is greater than 5. >> >> 2010/2/26, Rick Hillegas <[email protected]>: >> >>> Hi Gabriele, >>> >>> Here's an example of how Derby triggers invoke database procedures. If >>> this is still not clear, please keep asking questions. We will get to >>> the bottom of this: >>> >>> connect 'jdbc:derby:memory:dummy;create=true'; >>> >>> create table t( a int ); >>> >>> create procedure vetRow( in arg int ) >>> language java parameter style java contains sql >>> external name 'z.vetRow'; >>> >>> create trigger t_trig >>> no cascade before insert on t >>> referencing new as newRow >>> for each row call vetRow( newRow.a ); >>> >>> insert into t( a ) values ( 1 ); >>> >>> Another comment inline... >>> >>> Gabriele Kahlout wrote: >>> >>>> That's what i do in SQLite. The question is how to do it in java DB sql >>>> syntax. >>>> In sqlite it is SELECT( RAISE, 'error) WHERE ... >>>> in java db?? >>>> >>>> Also, I was wondering if I could find a way to implement the >>>> constraint as a column constraint. Unfortunately the details , are far >>>> from complete. >>>> http://developers.sun.com/docs/javadb/10.5.3.0/ref/index.html >>>> >>>> >>> I don't think so. The only kind of constraint you could use here would >>> be a CHECK constraint which invokes a user-coded function. The function >>> would not be allowed to issue SQL. >>> >>> Hope this helps, >>> -Rick >>> >>>> Basically I'm trying to check that no more than 4 rows have the same >>>> value for the column. In SQL that would be the count -group by query, >>>> as mentioned below, but since that counts as a not allowed subquery, >>>> is there some other way in java db to achieve exactly that? >>>> >>>> 2010/2/26, Rick Hillegas <[email protected]>: >>>> >>>> >>>>> Hi Gabriele, >>>>> >>>>> One way to solve this problem would be to have your trigger call a >>>>> database procedure, passing in whatever parameters you need from the >>>>> triggering row. The procedure would run the sanity check query and >>>>> raise >>>>> an exception if the sanity check failed. >>>>> >>>>> Hope this helps, >>>>> -Rick >>>>> >>>>> Gabriele Kahlout wrote: >>>>> >>>>> >>>>>> Hello, >>>>>> >>>>>> I'm porting my application from SQLite to Java DB, but don't know how >>>>>> to enforce an assertion, achieved in SQLite by: >>>>>> >>>>>> st.execute("CREATE TRIGGER _fk_meanings_update_1 BEFORE UPDATE ON " + >>>>>> batTable.getName() + " FOR EACH ROW BEGIN SELECT RAISE(FAIL, 'error') >>>>>> WHERE (EXISTS (SELECT COUNT(*) FROM " + batTable.getName() + " GROUP >>>>>> BY " + lastPubColumn.getName() + " HAVING COUNT(*)>"+maxPub+")); >>>>>> END;"); >>>>>> >>>>>> However the constraint couldn't be added as a table constraint, and >>>>>> I'm not finding how to raise an exception with Java DB, although it's >>>>>> described here: >>>>>> >>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/devguide/index.html >>>>>> >>>>>> One way was to set the identity column to null, but that was not >>>>>> allowed at compile time too. I guess I'd have to use some dirty way. >>>>>> >>>>>> >>>>>> >>>>>> >>>> >>>> >>> >> >> >> > > -- Regards, K. Gabriele --- unchanged since 25/1/10 --- P.S. Unless a notification (LON), please reply either with an answer OR with " ACK" appended to this subject within 48 hours. Otherwise, I might resend. In(LON, this) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x, this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧ ¬IsAnswerTo(x,this)) ⇒ ¬IResend(this). Also note that correspondence may be received only from specified a priori senders, or if the subject of this email ends with a code, eg. -LICHT01X, then also from senders whose reply contains it. ∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y. In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).
