You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "tim.wickett" <ti...@nextech-technology.co.uk> on 2013/08/25 11:40:08 UTC

More trigger problems

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.

Re: More trigger problems

Posted by Knut Anders Hatlen <kn...@oracle.com>.
Rick Hillegas <ri...@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

Re: More trigger problems

Posted by Rick Hillegas <ri...@oracle.com>.
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

Re: More trigger problems

Posted by John English <jo...@gmail.com>.
On 25/08/2013 23:40, tim.wickett wrote:
> I still get the syntax error if i have the WHERE clause after the triggered
> statement.
>
> The idea of the code was to create a new entry in the
> WASTE_ITEM_LOCATION_HISTORY table every time the current_location value was
> updated in the WASTE_ITEM table. However I don't want an entry if any other
> value is updated. I want a history of each location an item of waste has
> been stored at.

OK, so your condition should be "OLD.current_location <> NEW.current_location".
However, INSERT statements don't have a WHERE clause either. Rick's suggestion 
to use a procedure to insert or not seems to be the only way to do what you want.

HTH,
-- 
John English

Re: More trigger problems

Posted by "tim.wickett" <ti...@nextech-technology.co.uk>.
I still get the syntax error if i have the WHERE clause after the triggered
statement. 

The idea of the code was to create a new entry in the
WASTE_ITEM_LOCATION_HISTORY table every time the current_location value was
updated in the WASTE_ITEM table. However I don't want an entry if any other
value is updated. I want a history of each location an item of waste has
been stored at.




--
View this message in context: http://apache-database.10148.n7.nabble.com/More-trigger-problems-tp133437p133441.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: More trigger problems

Posted by John English <jo...@gmail.com>.
On 25/08/2013 12:40, 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.

Yes, check the syntax for CREATE TRIGGER -- there is no provision for a WHERE 
clause before the triggered-SQL-statement.

That said, it's not clear what you're trying to do; in the condition 
"old_waste_item.current_location <> current_location", which current_location 
does the RHS of the condition refer to?
-- 
John English