问题描述:

I just started learning and reading about TRIGGERs in Derby which I use as Database for a project in Java. I used to have MSSQL as database in the past.

There's just one problem I have with triggers. I can't find a syntax and I don't know how to specify the ID of which row to update during an UPDATE trigger.

This is what I have.

CREATE TRIGGER COPY_UPDATED_USERNAME

AFTER UPDATE ON ALLUSERS

REFERENCING NEW AS NEWUSERNAME_ROW

FOR EACH ROW MODE DB2SQL

UPDATE ALLUSERS_MAINTABSPERMISSION SET USERNAME = NEWUSERNAME_ROW.USERNAME

It's able to update the USERNAME column but it affects/updates all rows instead of just one row. That's where I'm having problems with.

In MSSQL I can simply put SET @ID = (SELECT ID FROM INSERTED) but I don't know how to do it in Derby.

How to do this on Derby?

Thanks.

网友答案:

I was able to solve my problem. Just had to add the WHERE clause and put the Id.

I hope this helps new derby users.

CREATE TRIGGER COPY_UPDATED_USERNAME
AFTER UPDATE OF USERNAME ON ALLUSERS
REFERENCING NEW AS NEWUSERNAME_ROW
FOR EACH ROW MODE DB2SQL
    UPDATE ALLUSERS_MAINTABSPERMISSION SET USERNAME = NEWUSERNAME_ROW.USERNAME
    WHERE USERID = NEWUSERNAME_ROW.USERID;
相关阅读:
Top