db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: More trigger problems
Date Mon, 26 Aug 2013 12:52:52 GMT
On 8/25/13 2:40 AM, tim.wickett wrote:
> Hi again, I am having more trigger problems. This time I am trying to create
> a history when values are updated. This is my code
>
> CREATE TRIGGER update_location
>      AFTER UPDATE ON WASTE_ITEM
>      REFERENCING OLD AS old_waste_item
>      FOR EACH ROW
>      WHERE (old_waste_item.current_location
>             <>  current_location)
>      INSERT INTO WASTE_ITEM_LOCATION_HISTORY
>      VALUES (old_waste_item.waste_item_id, CURRENT DATE,
>              old_waste_item.current_site_id, old_waste_item.current_location)
>
> I get the following syntax error
>
> Error code -1, SQL state 42X01: Syntax error: Encountered "WHERE" at line 4,
> column 18.
>
> but as far as I can see the syntax is correct so please help. Hopefully I am
> missing something obvious.
>
> Thanks Tim
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/More-trigger-problems-tp133437.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Tim,

What you need is the WHEN clause of the CREATE TRIGGER statement. Derby 
does not support this clause yet, but it is tracked by the following 
issue: https://issues.apache.org/jira/browse/DERBY-534 You can help 
raise the priority of that issue by voting for it.

As a workaround, you could re-code your trigger to fire a database 
procedure. The procedure would be responsible for comparing the old and 
new values of current_location in order to determine whether a row 
should be inserted into waste_item_location_history. Something like the 
following:

CREATE TRIGGER update_location
     AFTER UPDATE ON WASTE_ITEM
     REFERENCING
         OLD AS owi
         NEW AS nwi
     FOR EACH ROW
         CALL updateWasteHistory( owi.wasteItemID, owi.siteID, 
owi.currentLocation, nwi.currentLocation )

Hope this helps,
-Rick

Mime
View raw message