db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: More trigger problems
Date Mon, 26 Aug 2013 14:05:47 GMT
Rick Hillegas <rick.hillegas@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

Mime
View raw message