db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: triggers / referencing clause
Date Wed, 06 Jan 2010 13:39:20 GMT
FNG wrote:
> --This trigger compiles and works fine
> CREATE TRIGGER seq_table_x_trg AFTER INSERT ON TABLE_X REFERENCING NEW ROW
> AS new FOR EACH ROW MODE DB2SQL 
> UPDATE sequences SET currval = currval + 1 WHERE sequence_name = 'table_x'; 
>
> --If I add this additional clause on the end then it seems to complain about
> "new"
> SET new.PK = 'abc-' || SELECT currval FROM sequences WHERE sequence_name =
> 'table_x’;
>
> If anyone can comment would be appreciated.
>   
Hello,

 From this description I'm not sure what the problem trigger looks like. 
Could you share the full trigger declaration? Off the top of my head, it 
sounds like you are trying to use the trigger to update a row in 
sequences and a row in table_x. Remember that a trigger can only fire 
one sql statement as its action. The trigger can't fire two update 
statements, one against sequences and the other against table_x. If you 
need to update two tables then you should put the updates in a database 
procedure and have the trigger fire the procedure.

Hope this helps,
-Rick

Mime
View raw message