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 FNG <da...@googlemail.com> on 2010/01/05 21:55:04 UTC
triggers / referencing clause
--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.
--
View this message in context: http://old.nabble.com/triggers---referencing-clause-tp27026717p27026717.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: triggers / referencing clause
Posted by FNG <da...@googlemail.com>.
I will try the stored procedure version you suggest next...
Rick Hillegas-2 wrote:
>
> 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
>
>
--
View this message in context: http://old.nabble.com/triggers---referencing-clause-tp27026717p27056948.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: triggers / referencing clause
Posted by Rick Hillegas <Ri...@Sun.COM>.
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