Re: SQL trigger question

il 31/05/2007 17.06, Scrive Dr.UgoGagliardelli 43932392:
il 31/05/2007 16.41, Scrive Hal 40706744:
On May 31, 7:24 am, Graybeard <graybe...@xxxxxxxxxxxxxx> wrote:
To add or delete any triggers, you need exclusive access to the
file. Your attempt is waiting for file to be available, then times
out.

Okay I got the trigger added. Then I fired myself to test the
functionality of the trigger, and sure enough, my EID showed up in my
work table just like I wanted it to.

After quickly re-hiring myself I started working on the 2nd trigger,
which is going to disable the ACS user account of anyone who
terminates/resigns/whatever AND has a valid ACS user account. ACS is
just a higher level cover for os/400.

So I wrote a 2nd trigger and it does not work, and this one has me
stumped. PADATA will contain the name of the user ID assigned to an
employee for as/400 access, if they have one. So step one when a
record gets put into the work table via trigger one is to associate
the employee ID number with the as/400 user id(if there is one). Item
2 is to set the active flag to "N" on anyone who has an as/400 account
and has met the conditions defined by the first trigger.

BEGIN ATOMIC

select padata from store.peis480h where pa# = new.em# and patid =
'AS400';
if padata is not null then update store.pacs260 set usract = 'N'
where usrprf = padata;
END

The 2nd line errors out with a "Variable PADATA not defined or not
usable". If I comment out line 2 with a -- the syntax check completes
normally. What am I doing wrong here?
You should put padata somewhere, e.g.
define padata [same type as padata];
Should be:
declare padata [same type as padata];

select padata into padata from store.peis480h where pa# = new.em# and patid = 'AS400';

Now padata is defined.



--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñejoAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'
.

Relevant Pages

  • Re: SQL trigger question
    ... After quickly re-hiring myself I started working on the 2nd trigger, ... terminates/resigns/whatever AND has a valid ACS user account. ... PADATA will contain the name of the user ID assigned to an ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL trigger question
    ... functionality of the trigger, and sure enough, my EID showed up in my ... terminates/resigns/whatever AND has a valid ACS user account. ... and has met the conditions defined by the first trigger. ... if padata is not null then update store.pacs260 set usract = 'N' ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL trigger question
    ... Okay I got the trigger added. ... terminates/resigns/whatever AND has a valid ACS user account. ... PADATA will contain the name of the user ID assigned to an ... The 2nd line errors out with a "Variable PADATA not defined or not ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL trigger question
    ... Okay I got the trigger added. ... terminates/resigns/whatever AND has a valid ACS user account. ... PADATA will contain the name of the user ID assigned to an ... if padata is not null then update store.pacs260 set usract = 'N' ...
    (comp.sys.ibm.as400.misc)