This topic contains examples of Database Trigger jobs that monitor Microsoft SQL Server database tables.
Example: Monitor a Microsoft SQL Server Database Table for a New or Deleted Row
Suppose that you want a job to monitor the stores table for an added row or a deleted row. The job runs on the DB_AGENT agent computer under the sa user. The job remains in a running state waiting for an added or deleted row. When a row is either added or deleted, the job completes.
AGENT DB_AGENT USER sa DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs' TRIG_TYPE 'INSERT, DELETE' TABLE_NAME stores
Example: Monitor a Microsoft SQL Server Database Table for Two Changes
Suppose that you want a job to monitor the sales table for changes to the ord_date and qty columns. The job runs under the sa user and completes only when both columns have changed.
AGENT DB_AGENT USER sa DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs' TRIG_TYPE UPDATE TABLE_NAME SALES TRIG_COND 'UPDATE(ord_date) and UPDATE(qty)'
Example: Monitor a Microsoft SQL Server Database Table for Added Rows with a Trigger Condition
Suppose that you want a job to monitor the sales table for added rows. When the quantity for inserted title ID TC7777 is greater than or equal to 20, the job completes.
AGENT DB_AGENT USER sa DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs' TRIG_TYPE INSERT TABLE_NAME sales TRIG_COND '(select QTY from INSERTED where TITLE_ID=''TC7777'')>=20'
Example: Monitor a Microsoft SQL Server Database Table for Deleted Rows
Suppose that you want a job to monitor the sales table for deleted rows. the job changes from a running state to a monitoring state. Each time a row is deleted from the table, the scheduling manager sends the alert named altr. The job remains in a monitoring state until it is forced complete or cancelled.
AGENT DB_AGENT USER sa DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs' TRIG_TYPE DELETE TABLE_NAME sales CONTINUOUS(altr)
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|