Rocket U2 | UniVerse & UniData

 View Only
Expand all | Collapse all

TRIGGERS

  • 1.  TRIGGERS

    Posted 05-16-2022 16:37
    Hi - as ive mentioned in previous threads, I just switched from unidata to universe.  A coworker mentioned a trigger but I do not see a version of my unidata commands:

    CREATE-TRIGGER, LIST-TRIGGER, DELETE-TRIGGER

    I do see the LIST.SICA command, which lists universe triggers on SQL files....do we not have triggers on regular PICK files in universe?

    ------------------------------
    Kathleen Hambrick
    Programmer at Colwell
    ------------------------------


  • 2.  RE: TRIGGERS

    Posted 05-16-2022 16:47
    Hi Kathleen!

    In Universe, it's actually two words - CREATE TRIGGER.

    Try HELP SQL CREATE for more info.

    Brian




  • 3.  RE: TRIGGERS

    PARTNER
    Posted 05-16-2022 16:48
    Hi

    Yes on UV, triggers are documented/part of SQL engine. 

    look at HELP SQL CREATE TRIGGER / DROP TRIGGER. 

    You can use it against standard non SQL  uv datafile. 

    be carefull, if you change the subr after create trigger you must drop/create. 

    Regards

    ------------------------------
    Manu Fernandes
    ------------------------------



  • 4.  RE: TRIGGERS

    Posted 05-16-2022 17:37
    SO... cant find the catalog table - im used to CTLGTB.... if there a table of catalogued programs in universe?


    ------------------------------
    Kathleen Hambrick
    Programmer at Colwell
    ------------------------------



  • 5.  RE: TRIGGERS

    Posted 05-16-2022 17:49
    The only example in the manuals i see is specific to an sql file. Trying this at tcl gives me an 'SQL+' prompt. Can someone show me the syntax to put a trigger on a non sql file please?


    ------------------------------
    Kathleen Hambrick
    Programmer at Colwell
    ------------------------------



  • 6.  RE: TRIGGERS

    PARTNER
    Posted 05-17-2022 03:27
    Edited by Manu Fernandes 05-17-2022 03:27
      |   view attached
    hello kathleen,

    yes, triggers are member of universe 'SQL engine'... you miss the semi-colon at then end of the phrase ... 
    CREATE TRIGGER TRIGGER1 AFTER INSERT OR UPDATE OR DELETE ON MYFILE FOR EACH ROW CALLING '*MYTRIGGER' ; ​

    i'm used to create a VOC PARAGRAPH to set/update a trigger on a file 

    first, basic/catalog globally 

    BASIC BP TRIG.LOG.b
    CATALOGI BP *TRIG.LOG TRIG.LOG.b FORCE
    

    next, define the paragraph

    ED VOC SET.TRIG.LOG
    PA 
    DROP TRIGGER <<I2,filename>> TRIGLOG ;
    CREATE TRIGGER TRIGLOG AFTER INSERT OR UPDATE OR DELETE ON <<I2,filename>> FOR EACH ROW CALLING '*TRIG.LOG';
    
    next, use on file 
    SET.TRIG.LOG MYFILE 


    ------------------------------
    Manu Fernandes
    ------------------------------

    Attachment(s)

    txt
    TRIG.LOG.txt   1 KB 1 version


  • 7.  RE: TRIGGERS

    PARTNER
    Posted 05-17-2022 03:30
    oops do not forget semin-colon at the end of DROP TRIGGER filename triggername ;

    ------------------------------
    Manu Fernandes
    ------------------------------



  • 8.  RE: TRIGGERS

    PARTNER
    Posted 05-17-2022 03:57
    some notes : 

    1/ Trigger must be dropped and created again. 
    Problem
    If trigger program is changed after creation, the trigger must be dropped and created again. This presents problems, for example, when a new release of application software is deployed. Typically, program libraries will be reloaded and recataloged. If trigger programs have changed, all files will need to be checked, triggers dropped and recreated.
    Cause
    The pointer to the trigger program object code resides in the SICA of the file header. 
    Solution
    Workaround: Make the trigger program a simple call to an external subroutine. The external subroutine may now be modified without affecting the trigger

    2/ triggers execution are part of  an implicit  'TRANSACTION' processing
    2.1/ the TRANSACTION BEGin is started by the 'WRITE' verb of the parent execution.  Then all BEGIN TRANSACTION restriction occurs inside triggers.
    2.2 / you must get the lock (READU / RECORDLOCKU) before update another file 
    2.3/ you can't execute verb which require a new level. 
    2.4/ be carefull if the parent already have the lock of a record, the on-file record can be wrong value 
    2.5/ if the parent start a explicit BEGIN TRANSACTION, all locks set by the triggers are held by the session (as part of the parent TRANSACTION)  until TRANSACTION COMMIT/ROLLBACK ... 
    2.6/ if the parent use explicit BEGIN TRANSACTION and perform ROLLBACK triggers update are ROLLBACK'ed too, the only way to have the trace of the triggers call is via OPENSEQ/WRITESEQ which are not part of TRANSACTION processing.

    3/ check trigger infinite loop
    sometime, you can create infinite loop, if triggers update file where triggers are fired.
    you can use the system(9001) to check if the trigger is in the parent call stack. 

    4/ triggers must be created on 'closed file' 
    if  session-A have the file opened on  runtime filevariable/common and session-B CREATE TRIGGER on the file, the session-A does not known the trigger, session-A must reopen the file.  because, as for indices, triggers info/p-code are in the header of the file - loaded on the filevariable. 

    I hope this help



    ------------------------------
    Manu Fernandes
    ------------------------------