CA Workload Automation ESP Edition

Expand all | Collapse all

ESP: db_trig Command Examples

  • 1.  ESP: db_trig Command Examples

    Posted 11-08-2016 08:15 AM

    Hello!

    I am working on creating my first db_trig job.  What I would like to do is monitor a table for change, and then run a job when the table has been updated.

     

    My question is two fold:  Can this only be done by using alerts?  Secondly, would anyone be willing to supply some sample db_trig definitions where they are doing this?

     

    Thanks!



  • 2.  Re: ESP: db_trig Command Examples

    Posted 11-09-2016 02:55 PM

    Following is from ESP "Command Reference Guide", under "DB_TRIG Statement—Start a Database Trigger Job Definition":

    Example: Monitor Table for Updates
    This example defines the Database Trigger job DBTR1. The Database Agent CYBDB1 continuously monitors the table INVENTORY_LIST for updates. When INVENTORY_LIST is updated, if the number of units of product A has fallen below 100000, CA WA ESP Edition triggers the predefined alert ALOW.
    DB_TRIG DBTR1
    AGENT CYBDB1
    TRIG_TYPE UPDATE
    TABLE_NAME INVENTORY_LIST CONTINUOUS(ALOW)
    TRIG_COND new.productA<100000
    ENDJOB

     

    Hope it helps,

     

    Lucy



  • 3.  Re: ESP: db_trig Command Examples

    Posted 11-09-2016 04:04 PM

    Lucy,

     

    That's just it. . . I was reviewing the command reference, and that is the only example that is given.  I am wondering if there are more options / flexability besides just using an alert.  For example, could you use an IF condition to release (or insert) a job if a table is updated in a similar way to a file watcher.

     

    Thanks!



  • 4.  Re: ESP: db_trig Command Examples

    Posted 11-09-2016 05:40 PM

    Hi,

    My example is below just to give you a different example.  This can also be done at the event level. There isn't an alert required. In the case below when the TRIG_COND is satisfied it releases the next job and does whatever is needed. If the trigger is at the event level it triggers the APPL every time the condition is satisfied. I can get you an example of an event level trigger if that is what you are looking for.

     

    DB_TRIG DBTRIG                                        

      AGENT AGENTU_DB                                     

      TABLE_NAME C##ESPADMIN.TESTTABLE1                   

      TRIG_TYPE UPDATE                                    

      DB_URL 'jdbc:oracle:thin:@powdo03-U107996:1521:orcl'

      TRIG_COND 'new.STATUS=''PENDING'' AND new.AGE=23'   

      RUN TODAY                                           

      RELEASE (DPTST001)                                  

    ENDJOB                                                 

                                                           



  • 5.  Re: ESP: db_trig Command Examples

    Posted 11-10-2016 08:20 AM

    Don,

     

    As usual, you have given me exactly what I am looking for.  I have a work session for this tomorrow, and would really appreciate a sample event we can work with as well, just so I can present multiple options.

     

    Thanks again!



  • 6.  Re: ESP: db_trig Command Examples

    Posted 11-10-2016 11:36 AM

    Hi Jonathan,

    I need to play with this some more but I believe it should look like the one below….

     

    /*************************************************/             

    /* DEFINED BY POWDO03  AT 10.34 ON THU 10NOV2016 */             

    /*            LAST RUN AT UNKNOWN                */             

    /*************************************************/             

    EVENT ID(POWDO03.DPDB0006)  SYSTEM(X114DPM)  REPLACE            

    INVOKE 'POWDO03.TEST.APPL(DPDB0006)'                            

    WOBTRIG DB_TRIG Agent(AGENTU_DB) Trig_Type(UPDATE) -             

            Trig_Cond('NEW.STATUS=''PENDING'' AND NEW.AGE=23') -    

            Table_Name('C##ESPADMIN.TESTTABLE1')                    

    ENDDEF                                                          



  • 7.  Re: ESP: db_trig Command Examples

    Posted 11-11-2016 03:39 PM

    Thank you Don!  Your advise is always invaluable!



  • 8.  Re: ESP: db_trig Command Examples

    Posted 04-15-2019 05:42 PM

    I've been working with ESP for years, but am not an expert.  I have a need to setup a database trigger, but am not finding a lot of examples (similar to what the person above experienced).  The reference manual is not real helpful, it just says to refer to SQL Server reference manual for syntax of IF statement.  I'm trying to monitor one column in a database table for one row -- it's an on/off ('0'/'1') flag.  Can someone give me some advice?

     

    I tried the following, but I get an error message stating "Invalid column" -- even though both of those are columns in the tblSystemStatus table.

     

    DB_TRIG T3K5555$.FM#TEST
    TAG 'Test Trigger'
    AGENT %PrimaryAgent
    TRIG_TYPE UPDATE
    TABLE_NAME tblSystemStatus
    TRIG_COND 'cStatusId="ARROUpload" AND iStatusValue=1'
    Run Daily
    Release Add(T3K2222$.EX#TEST)
    ENDJOB

     

    I can get the trigger to work with TRIG_COND 'UPDATE(iStatusValue)', but then it triggers when any row in the table has its iStatusValue changed.

     

    Please help!  There's like nothing on the internet about this TRIG_COND except this post!



  • 9.  Re: ESP: db_trig Command Examples

    Posted 04-16-2019 08:30 AM

    Hi Dave,

     

    Firstly please confirm that cStatusId and iStatusValue are both valid column names.

     

    Secondly could you try as below and see if it works?

    TRIG_COND 'new.cStatusId="ARROUpload" AND new.iStatusValue=1'

     

    Some agent logs that starting with db can show what has been passed to the SQL server, it may help to diagnose the problem.

     

    If any more help is needed, please open a support ticket.

     

    Thank you,

     

    Lucy



  • 10.  Re: ESP: db_trig Command Examples

    Posted 04-16-2019 08:32 AM

    Hi Dave, 

    Try the TRIG_COND statement below.  I changed the double tick marks to 2 single tick marks before and after ARROUpload. I also added "new" to the column names.  The new refers to the value AFTER they updated. In this case they should be ARROUpload and 1. 

     

    TRIG_COND 'new.cStatusId=''ARROUpload'' AND new.iStatusValue=1'

     

    Let me know how it works out. 

    2¢ 



  • 11.  Re: ESP: db_trig Command Examples

    Posted 04-16-2019 08:52 AM

    Thanks Lucy and 'powdo03'.  I tried your syntax, but received this error message ... LStatus("java.sql.SQLException: The multi-part identifier ""new.cStatusId"" could not be bound.").  I had tried something like that before and received the same thing.  Keep in mind this is monitoring a table on a SQL Server 2016 database.  Also, I'm not looking for a change to the cStatusId column; only the iStatusValue column.



  • 12.  Re: ESP: db_trig Command Examples

    Posted 04-16-2019 10:57 AM

    Hi Dave, 

    Did you try it without the new. but using the updated quotes? 

     

    Don



  • 13.  Re: ESP: db_trig Command Examples

    Posted 04-16-2019 11:56 AM

    TRIG_COND 'cStatusId=''ARROUpload'' AND iStatusValue=1' ...

     yields ... LStatus("java.sql.SQLException: Invalid column name 'cStatusId'.")

     

    NOTE:  those are two single quotes around ARROUpload



  • 14.  Re: ESP: db_trig Command Examples

    Posted 04-16-2019 02:57 PM

    Thanks for those who gave me feedback and suggestions.  I believe I found the correct syntax for the TRIG_COND that I was looking for ...

     

    DB_TRIG T3K5555$.FM#TEST
    TAG 'Test Trigger'
    AGENT %PrimaryAgent
    TRIG_TYPE UPDATE
    TABLE_NAME tblSystemStatus
    TRIG_COND '(SELECT iStatusValue FROM tblSystemStatus WHERE +
                            cStatusId=''ARROUpload'')=1'
    Run Daily
    ENDJOB

     

    Here's what was in the db.log file ...

     

    04/16/2019 14:49:45.818-0400 4 DatabasePlugin.database Internal Thread.DbTrigHandler.runSql[:339] - SQL: CREATE TRIGGER CYB_1555440585807
    ON tblSystemStatus
    FOR UPDATE AS
    IF ((SELECT iStatusValue FROM tblSystemStatus WHERE cStatusId='ARROUpload')=1)
    INSERT INTO dbo.CYB_TRIG_LOG (TRIG_NAME, WOB_ID, MGR_ID, ALERT_NAME, PROCESS_FLAG, CREATED)
    VALUES ('CYB_1555440585807','T3K5555$.FM#TEST/T3K1SSIS.25/MAIN','JavaAgent#tcpip@MCB_MANAGER','','N',GetDate());
    04/16/2019 14:51:33.052-0400 4 main.Thread-5.DbTrigHandler.runSql[:339] - SQL: DROP TRIGGER CYB_1555440585807



  • 15.  Re: ESP: db_trig Command Examples

    Posted 04-17-2019 10:05 AM

    Hi Dave,

     

    Thank you for sharing! Really appreciate it. I am sure others can benefit.

     

    Lucy