SQL trigger question - IBM AS400

This is a discussion on SQL trigger question - IBM AS400 ; I'm running V5R3, using ops navigator to create a SQL trigger. Basically, what I want to do is watch an employee table, and when someone updates the file I just want to check and see if the employee location code ...

+ Reply to Thread
Results 1 to 12 of 12

Thread: SQL trigger question

  1. SQL trigger question

    I'm running V5R3, using ops navigator to create a SQL trigger.
    Basically, what I want to do is watch an employee table, and when
    someone updates the file I just want to check and see if the employee
    location code was changed to 99, which means they terminated/retired/
    resigned/etc. When that happens, I want to take the 'new' snapshot of
    the changed row and stick a couple of the columns into another table.

    I have the trigger ready to go, the "Check Syntax" says everything
    looks good but when I hit OK in ops nav it sits for a short time and
    then comes up with an error "Row or object type *FILE in use"

    Why can't I add a SQL trigger to a file regardless of any current
    record locks? If the trigger is going to run on updates that happen
    AFTER the trigger has been added it shouldn't matter if there are some
    people with locks on individual records at the time the trigger is
    created, should it? Is there any way around this?

    Chris


  2. Re: SQL trigger question

    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.


  3. Re: SQL trigger question

    On May 31, 7:24 am, Graybeard 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?

    Chris


  4. Re: SQL trigger question

    il 31/05/2007 16.41, Scrive Hal 40706744:
    > On May 31, 7:24 am, Graybeard 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];
    select padata into padata from store.peis480h where pa# = new.em# and
    patid = 'AS400';

    Now padata is defined.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    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'

  5. Re: SQL trigger question

    Presumably usrprf is never null, so couldn't you do something like
    this:
    UPDATE store.pacs260 SET usract='N'
    WHERE usrprf = (SELECT padata FROM store.peis480h
    WHERE pa#=new.em# and paid='AS400')


  6. Re: SQL trigger question

    On May 31, 8:26 am, "walker.l2" wrote:
    > Presumably usrprf is never null, so couldn't you do something like
    > this:
    > UPDATE store.pacs260 SET usract='N'
    > WHERE usrprf = (SELECT padata FROM store.peis480h
    > WHERE pa#=new.em# and paid='AS400')


    Well technically it COULD be null, becuase we have lots of employees
    who never use our as/400, so the first line of my query would return a
    null value.

    I've been trying to lookup how to use the define keyword to define the
    variable padata but I'm striking out so far. It is a character field,
    length of 10.

    BEGIN ATOMIC
    define padata char(10);
    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

    This doesn't work, but I'm still playing with it.

    Chris


  7. Re: SQL trigger question

    On May 31, 8:26 am, "walker.l2" wrote:
    > Presumably usrprf is never null, so couldn't you do something like
    > this:
    > UPDATE store.pacs260 SET usract='N'
    > WHERE usrprf = (SELECT padata FROM store.peis480h
    > WHERE pa#=new.em# and paid='AS400')


    Well technically it COULD be null, becuase we have lots of employees
    who never use our as/400, so the first line of my query would return a
    null value.

    I've been trying to lookup how to use the define keyword to define the
    variable padata but I'm striking out so far. It is a character field,
    length of 10.

    BEGIN ATOMIC
    define padata char(10);
    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

    This doesn't work, but I'm still playing with it.

    Chris


  8. Re: SQL trigger question

    What I meant was presumably usrprf (not padata) is never null. So even
    if your (S0ELECT padata...) returns null, no update takes place on
    pacs260 (because WHERE usrprf = null would not match any rows). Or do
    you mean that usrprf = null would give some row matches on pacs260?


  9. 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 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ñe joAlcoolInside
    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'

  10. Re: SQL trigger question

    il 31/05/2007 17.37, Scrive Hal 43932392:
    > On May 31, 8:26 am, "walker.l2" wrote:
    >> Presumably usrprf is never null, so couldn't you do something like
    >> this:
    >> UPDATE store.pacs260 SET usract='N'
    >> WHERE usrprf = (SELECT padata FROM store.peis480h
    >> WHERE pa#=new.em# and paid='AS400')

    >
    > Well technically it COULD be null, becuase we have lots of employees
    > who never use our as/400, so the first line of my query would return a
    > null value.
    >
    > I've been trying to lookup how to use the define keyword to define the
    > variable padata but I'm striking out so far. It is a character field,
    > length of 10.
    >
    > BEGIN ATOMIC
    > define padata char(10);
    > 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
    >
    > This doesn't work, but I'm still playing with it.


    In my opinion even if you "DECLARE PADATA CHAR(10)" and don't use any
    method to evaluate it, it will be always emty not null.
    So if your select returns a single row, you can use SELECT INTO,
    otherwise you shoud fetch a cursor. I do not know other ways.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    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'

  11. Re: SQL trigger question

    On May 31, 7:41 am, Hal wrote:
    > On May 31, 7:24 am, Graybeard 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... ;


  12. Re: SQL trigger question

    On May 31, 2:22 pm, Jonathan Ball wrote:
    > On May 31, 7:41 am, Hal wrote:
    >
    >
    >
    >
    >
    > > On May 31, 7:24 am, Graybeard 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... ) ;


+ Reply to Thread