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
