Re: SQL trigger question
- From: Jonathan Ball <notgenx32@xxxxxxxxx>
- Date: 31 May 2007 14:26:39 -0700
On May 31, 2:22 pm, Jonathan Ball <notgen...@xxxxxxxxx> wrote:
On May 31, 7:41 am, Hal <hala...@xxxxxxxxx> wrote:
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?
First, you need to create a variable to hold the result:
declare w_padata char(50);
[change the data definition to match whatever it actually is in
peis480h]
Then, change your select to:
set w_padata = select padata from store.peis480h where... ;
Sorry...you need to put the select statement in parentheses:
set w_padata = (select padata from store.peis480h where... ) ;
.
- References:
- Re: SQL trigger question
- From: Jonathan Ball
- Re: SQL trigger question
- Prev by Date: Re: SQL trigger question
- Next by Date: Re: Outputting Job Logs and messages to a file.
- Previous by thread: Re: SQL trigger question
- Next by thread: Re: Outputting Job Logs and messages to a file.
- Index(es):
Relevant Pages
|
