October 11, 2010 at 10:01 am
I have no formal education in SQL Server, so I apologize ahead of time if this question is stupid
We have a database that is part of a software package we bought from an outside vendor. I need to send an email alert to certain individuals whenever new rows appear in a certain table. I realize that a trigger could accomplish this, but I am concerned that updates from the vendor (which happend quarterly) could erase any new triggers I add to their database. Is there a mechanism I could use that would exist outside of the vendor's database? Or, is it a given that I will have to recreate the trigger after each program update?
October 11, 2010 at 10:20 am
First off, check with the vendor to see what database objects they typically drop during updates. Chances are good that they are mostly adding new columns, keys, constraints, views, etc.
In any case, it is a good idea to save the code for your triggers and other database objects as backup in case they do drop them. That way it would be simple to restore them.
October 11, 2010 at 10:48 am
How close to "when a new row is added" is close enough?
I'd tend to not mess with a vendor's database, because it often breaks terms and nullifies support contracts, and would go with having a scheduled job check the table against a log created in a separate database. Run it often enough, and it'll probably suffice for the business need.
On the other hand, if "instantaneous" is the only acceptable answer, then a trigger on the table, and a database-level DDL trigger that alerts you if the trigger is dropped, might be the way to go. That's really iffy when it comes to support contracts, though, so do take a look down the "scheduled job" path.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2010 at 11:08 am
So, you both agree that a trigger will do the job. But, I can't visulize the logic in a "scheduled job" that could monitor a table. We need to know within a few minutes of the insertion of new rows. I suppose I could create a new database with a view in it that would bring over the data I need to monitor, but I don't know if you can base a trigger on a view.
October 11, 2010 at 11:13 am
i would go with GSquared suggestion of creating a log table, insert how many rows are in the table right now,a nd then have a job compare the current rowcount to the log table on an hourly basis or whatever...then the scheduled job can send an email if the rows have increased/decreased.
if you need to know instantly that the rows have changed, then the trigger and DDL audit GSquared suggested is pretty much your only recourse.
@GSquared: your avatar? is it a closeup of a crab's claw or something?
Lowell
October 11, 2010 at 11:14 am
No. I think that a trigger is most likely not the best solution.
Can you build a separate database with a log table in it? Is there an identity-type column on the table you want to send reports on? Will waiting 1 minute (or 10 minutes or even an hour) be acceptable to the business, or does the alert really need to be immediate?
Also, have you contacted the vendor about getting them to add an alerting feature to their product?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2010 at 11:22 am
GSquared, getting the vendor to add a custom alert feature will not get approved right now. We are over budget already on billing hours from the vendor. It seems that my best approach would be to create another database, bring over the info I need, and write a trigger or a scheduled job there. I'll have to do a bit of research to uncover the details of how to get it done in my environment.
Thanks for the input.
October 11, 2010 at 12:15 pm
here's a skeleton to help you visualize.
I'm assuming:
1. a tracking table in the MASTER database...not the Vendors database.
2. a snippet to initialize the data...you have to change the database and the tables to babysit.
3. a procedure to detect changes and send an email
4. a DIFFERENT procedure(which does not exist!!) which updates the table in master to the current row counts.
I hope this gets your thoughts around how I might tackle the process...hopefully you can improve it and
USE MASTER
CREATE TABLE [dbo].[TRACKROWCHANGES] (
[CHANGEID] INT IDENTITY(1,1) NOT NULL,
[DBNAME] SYSNAME NOT NULL,
[SCHEMANAME] SYSNAME NOT NULL,
[TBLNAME] SYSNAME NOT NULL,
[CURRENTROWCOUNT] INT NULL,
CONSTRAINT [PK__TRACKROWCHANGES__568200B3] PRIMARY KEY CLUSTERED (CHANGEID))
USE SANDBOX --change to the vendors database
INSERT INTO master.dbo.TRACKROWCHANGES
SELECT
db_name() as db,
S.name as schemaname,
T.name as tablename,
P.rows
FROM
sys.partitions P Join
sys.tables T On
P.object_Id = T.object_id Join
sys.schemas S On
T.schema_id = S.schema_id
WHERE
P.index_id in (0,1)
--change to the right table names
AND T.name IN('TheTableToTrack','AnotherTable')
GO
--this procedure finds the non matches, and sends an email.
CREATE PROCEDURE TRACKIFCHANGESOCCURRED
AS
BEGIN
IF EXISTS
(
SELECT *
FROM master.dbo.TRACKROWCHANGES TheLog
LEFT OUTER JOIN
(SELECT
db_name() as db,
S.name as schemaname,
T.name as tablename,
P.rows
FROM
SANDBOX.sys.partitions P Join
SANDBOX.sys.tables T On
P.object_Id = T.object_id Join
SANDBOX.sys.schemas S On
T.schema_id = S.schema_id
WHERE
P.index_id in (0,1)
--change to the right table names
AND T.name IN('TheTableToTrack','AnotherTable')
) CurrentRows
ON TheLog.DBNAME = CurrentRows.db
AND TheLog.DBNAME = CurrentRows.schemaname
AND TheLog.DBNAME = CurrentRows.tablename
WHERE TheLog.CURRENTROWCOUNT <> CurrentRows.rows)
BEGIN --data differences were found
declare @body1 varchar(4000)
set @body1 = 'Table Row Difference Alert ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' The Scheduled Job has detected that at least one protected table has had the number of rows changed since the last row verification.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='stormrage as scripts',
@recipients='lowell@hdsoftware.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML'
END
END --PROC
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply