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?
Following is from ESP "Command Reference Guide", under "DB_TRIG Statement—Start a Database Trigger Job Definition":
Example: Monitor Table for UpdatesThis 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 DBTR1AGENT CYBDB1TRIG_TYPE UPDATETABLE_NAME INVENTORY_LIST CONTINUOUS(ALOW)TRIG_COND new.productA<100000ENDJOB
Hope it helps,
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.
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.
TRIG_COND 'new.STATUS=''PENDING'' AND new.AGE=23'
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.
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
WOBTRIG DB_TRIG Agent(AGENTU_DB) Trig_Type(UPDATE) -
Trig_Cond('NEW.STATUS=''PENDING'' AND NEW.AGE=23') -
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!
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.
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.
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.
Did you try it without the new. but using the updated quotes?
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
Thank you for sharing! Really appreciate it. I am sure others can benefit.