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