By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

DB2 Trigger Help

P: 5
Hello @all!

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

my create trigger statement is as follows:

Expand|Select|Wrap|Line Numbers
  1. create trigger TRI_TUSER_PWCHANGE AFTER UPDATE
  2. OF PASSWORD ON TUSER
  3. REFERENCING NEW AS NEWVALUE OLD AS OLDVALUE 
  4. FOR each ROW 
  5. MODE db2sql
  6. when ( OLDVALUE.ENCRYPTED = NEWVALUE.ENCRYPTED )
  7. begin atomic
  8.     update tuser set LASTPASSWORDCHANGE = CURRENT TIMESTAMP
  9.     where id_user = OLDVALUE.ID_USER; 
  10.  
  11.     insert into TPASSWORDHISTORY (ID, ID_USER, CHANGEDATE, PASSWORD, ENCRYPTED) values
  12.     (NEXTVAL FOR S_ID_PASSWORDHISTORY, OLDVALUE.ID_USER, CURRENT TIMESTAMP, OLDVALUE.PASSWORD, OLDVALUE.ENCRYPTED);
  13.  
  14.     delete from tpasswordhistory where ID in
  15.     (
  16.           select ID from 
  17.           (
  18.                 select ROW_NUMBER() OVER (ORDER BY changedate desc) as rnum, ID, id_user, changedate from tpasswordhistory
  19.                 WHERE id_user = OLDVALUE.ID_USER -- <----- that does not work!?
  20.           ) as tmp1,
  21.           (
  22.                 select CAST(value AS DECIMAL) as VALUE from tparameter where name = 'PasswordHistoryLength'
  23.           ) as tmp2
  24.           where tmp1.rnum > tmp2.value
  25.     );    
  26. end
  27. /
  28.  
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 ;)
Jul 4 '07 #1
Share this Question
Share on Google+
5 Replies


10K+
P: 13,264
Hello @all!

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

my create trigger statement is as follows:

Expand|Select|Wrap|Line Numbers
  1. create trigger TRI_TUSER_PWCHANGE AFTER UPDATE
  2. OF PASSWORD ON TUSER
  3. REFERENCING NEW AS NEWVALUE OLD AS OLDVALUE 
  4. FOR each ROW 
  5. MODE db2sql
  6. when ( OLDVALUE.ENCRYPTED = NEWVALUE.ENCRYPTED )
  7. begin atomic
  8.     update tuser set LASTPASSWORDCHANGE = CURRENT TIMESTAMP
  9.     where id_user = OLDVALUE.ID_USER; 
  10.  
  11.     insert into TPASSWORDHISTORY (ID, ID_USER, CHANGEDATE, PASSWORD, ENCRYPTED) values
  12.     (NEXTVAL FOR S_ID_PASSWORDHISTORY, OLDVALUE.ID_USER, CURRENT TIMESTAMP, OLDVALUE.PASSWORD, OLDVALUE.ENCRYPTED);
  13.  
  14.     delete from tpasswordhistory where ID in
  15.     (
  16.           select ID from 
  17.           (
  18.                 select ROW_NUMBER() OVER (ORDER BY changedate desc) as rnum, ID, id_user, changedate from tpasswordhistory
  19.                 WHERE id_user = OLDVALUE.ID_USER -- <----- that does not work!?
  20.           ) as tmp1,
  21.           (
  22.                 select CAST(value AS DECIMAL) as VALUE from tparameter where name = 'PasswordHistoryLength'
  23.           ) as tmp2
  24.           where tmp1.rnum > tmp2.value
  25.     );    
  26. end
  27. /
  28.  
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 ;)
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.
Jul 5 '07 #2

P: 5
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
Jul 5 '07 #3

P: 5
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.
Jul 5 '07 #4

10K+
P: 13,264
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 SubSubStatements in v7.

You might have to spilt the statements up with one or two views
Jul 5 '07 #5

P: 5
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 (TPasswordHistory).

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 PasswordHistoryLength 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
Jul 5 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.