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?
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;