Re: More trigger problems

[John English]

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