DB2 Trigger Help

[Mulder]

Hello @all!

i am trying to create a trigger in db2 databas v7.

my create trigger statement is as follows:

[CODE=SQL]
create trigger TRI_TUSER_PWCHA NGE AFTER UPDATE
OF PASSWORD ON TUSER
REFERENCING NEW AS NEWVALUE OLD AS OLDVALUE
FOR each ROW
MODE db2sql
when ( OLDVALUE.ENCRYP TED = NEWVALUE.ENCRYP TED )
begin atomic
update tuser set LASTPASSWORDCHA NGE = CURRENT TIMESTAMP
where id_user = OLDVALUE.ID_USE R;

insert into TPASSWORDHISTOR Y (ID, ID_USER, CHANGEDATE, PASSWORD, ENCRYPTED) values
(NEXTVAL FOR S_ID_PASSWORDHI STORY, OLDVALUE.ID_USE R, CURRENT TIMESTAMP, OLDVALUE.PASSWO RD, OLDVALUE.ENCRYP TED);

delete from tpasswordhistor y where ID in
(
select ID from
(
select ROW_NUMBER() OVER (ORDER BY changedate desc) as rnum, ID, id_user, changedate from tpasswordhistor y
WHERE id_user = OLDVALUE.ID_USE R -- <----- that does not work!?
) as tmp1,
(
select CAST(value AS DECIMAL) as VALUE from tparameter where name = 'PasswordHistor yLength'
) as tmp2
where tmp1.rnum > tmp2.value
);
end
/
[/CODE]

when i execute the above statement, i get the following error:
"OLDVALUE.ID_US ER" is not defined name. SQLSTATE=42704

when i replace OLDVALUE.ID_USE R (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 SubSubStatement s, 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 ;)

[r035198x]

Hello @all!

i am trying to create a trigger in db2 databas v7.

my create trigger statement is as follows:

[CODE=SQL]
create trigger TRI_TUSER_PWCHA NGE AFTER UPDATE
OF PASSWORD ON TUSER
REFERENCING NEW AS NEWVALUE OLD AS OLDVALUE
FOR each ROW
MODE db2sql
when ( OLDVALUE.ENCRYP TED = NEWVALUE.ENCRYP TED )
begin atomic
update tuser set LASTPASSWORDCHA NGE = CURRENT TIMESTAMP
where id_user = OLDVALUE.ID_USE R;

insert into TPASSWORDHISTOR Y (ID, ID_USER, CHANGEDATE, PASSWORD, ENCRYPTED) values
(NEXTVAL FOR S_ID_PASSWORDHI STORY, OLDVALUE.ID_USE R, CURRENT TIMESTAMP, OLDVALUE.PASSWO RD, OLDVALUE.ENCRYP TED);

delete from tpasswordhistor y where ID in
(
select ID from
(
select ROW_NUMBER() OVER (ORDER BY changedate desc) as rnum, ID, id_user, changedate from tpasswordhistor y
WHERE id_user = OLDVALUE.ID_USE R -- <----- that does not work!?
) as tmp1,
(
select CAST(value AS DECIMAL) as VALUE from tparameter where name = 'PasswordHistor yLength'
) as tmp2
where tmp1.rnum > tmp2.value
);
end
/
[/CODE]

when i execute the above statement, i get the following error:
"OLDVALUE.ID_US ER" is not defined name. SQLSTATE=42704

when i replace OLDVALUE.ID_USE R (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 SubSubStatement s, 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 ;)

I'm really in a lazy mood this afternoon, so I'll ask you to test it for other aliase variables besides the old and new and see if it has anything to do with the aliases aliasing old and new.

[Mulder]

you mean that i should try something like this:

change
REFERENCING NEW AS NEWVALUE OLD AS OLDVALUE
to e.g.
REFERENCING NEW AS N OLD AS O
?

i tried that before i post my problem here, but thanks for the suggestion.

regards, daniel

[Mulder]

i tried the trigger in an DB2 Version 8 Database and it worked fine....

the problem is that i have to find a solution which is working for both versions.

[r035198x]

i tried the trigger in an DB2 Version 8 Database and it worked fine....

the problem is that i have to find a solution which is working for both versions.

I meant to test if the scope of any other aliases( besides NEW and OLD aliases) variables do go beyond SubSubStatement s in v7.

You might have to spilt the statements up with one or two views

[Mulder]

ah, you mean the NEW_TABLE variable for example?

will check that tomorrow.

the reason for this trigger, especially the third statement in it (that one with the error) is used to delete the old passwords in the history table (TPasswordHisto ry).

everytime a user changed his password, for example the password is expired, the old password will automaticaly put to that table by a trigger.

by the parameter PasswordHistory Length in the table TParameter, which users or better the application administrator, can edit in the webapplication, the HistoryTable will always be cutted, so that there should never be more stored passwords for one user than defined with the parameter.

the trigger then should delete older records than the new XXX ones.

possibly there could be a better way to solve that.

which statement do you mean i can put into a view?
the selected data depends on the user currently changing its password, how to realize this with a view?

i thought about using a stored procedure?!

best regards daniel