Rick Hillegas <rick.hille...@oracle.com> writes:
> 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 )
Alternatively, if you don't want to install a stored procedure in the
database, something like this should work too:
CREATE TRIGGER update_location
AFTER UPDATE ON WASTE_ITEM
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
INSERT INTO WASTE_ITEM_LOCATION_HISTORY
SELECT * FROM (VALUES (OLD.waste_item_id, CURRENT DATE,
OLD.current_site_id, OLD.current_location)) AS v
WHERE OLD.current_location <> NEW.current_location
--
Knut Anders
