Hello @all!
i am trying to create a trigger in db2 databas v7.
my create trigger statement is as follows:
-
create trigger TRI_TUSER_PWCHANGE AFTER UPDATE
-
OF PASSWORD ON TUSER
-
REFERENCING NEW AS NEWVALUE OLD AS OLDVALUE
-
FOR each ROW
-
MODE db2sql
-
when ( OLDVALUE.ENCRYPTED = NEWVALUE.ENCRYPTED )
-
begin atomic
-
update tuser set LASTPASSWORDCHANGE = CURRENT TIMESTAMP
-
where id_user = OLDVALUE.ID_USER;
-
-
insert into TPASSWORDHISTORY (ID, ID_USER, CHANGEDATE, PASSWORD, ENCRYPTED) values
-
(NEXTVAL FOR S_ID_PASSWORDHISTORY, OLDVALUE.ID_USER, CURRENT TIMESTAMP, OLDVALUE.PASSWORD, OLDVALUE.ENCRYPTED);
-
-
delete from tpasswordhistory where ID in
-
(
-
select ID from
-
(
-
select ROW_NUMBER() OVER (ORDER BY changedate desc) as rnum, ID, id_user, changedate from tpasswordhistory
-
WHERE id_user = OLDVALUE.ID_USER -- <----- that does not work!?
-
) as tmp1,
-
(
-
select CAST(value AS DECIMAL) as VALUE from tparameter where name = 'PasswordHistoryLength'
-
) as tmp2
-
where tmp1.rnum > tmp2.value
-
);
-
end
-
/
-
when i execute the above statement, i get the following error:
"OLDVALUE.ID_USER" is not defined name. SQLSTATE=42704
when i replace OLDVALUE.ID_USER (in the third statement) with an absolute value for ID_USER, e.g. 1, then the trigger is created properly.
it seems that the scope of the NEW as ALIAS_NEW and OLD as ALIAS_OLD variables do not go beyond SubSubStatements, e.g.:
delete from (
select * from (
select * from ... where X = NEW.VALUE
i would appreciate any help or comments, best regards daniel
ps: sorry for my bad english ;)
