ESP Workload Automation

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!

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

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!

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!

Thank you Don!  Your advise is always invaluable!

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!

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

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¢ 

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.

Hi Dave, 

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

Don

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

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

NOTE:  those are two single quotes around ARROUpload

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

Hi Dave,

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

Lucy