How to trigger a SQL stored procedure to run based on an event

I have written a stored procedure that creates a snapshot table of my company's accounts receivable (AR) balance by customer.  The customer AR balance changes in real time when charges, payments and adjustments are posted.  This happens 24/7.  We are a 24 hour business.  The only time to capture the "day end" balance is when the system's CLOSED flag is set to 'Y'.  I have a SQL view that captures the following fields:

DateTime -> Midnight of the Day Closed (2015-05-24 00:00:000)
ClosedDateTime -> Actual Time Midnight Close Completed (2015-05-25 00:23:000)

I know the exact time the system has closed for the previous day's business and creates a summary report.  I have to report on the actual account balances to match this summary report.

From the data above, you can see the day being closed 5/24 closed a 12:23 AM on 5/25/15.

What I want to do is kick off my stored procedure as soon as possible to the time the system closed.

Is there a way to start running the stored procedure at 1 minute intervals after Midnight to check the values of the VIEW, and if the previous day has closed run the stored procedure.  Of course the stored procedure should not run again after it has successfully run one time.

Can someone help with this?

Thank you.

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi GPSPOW,

Running a task every minute to see if another task is complete is not the best design.

Better would be to just sequence the CLOSE and REPORT tasks in the same job.


Kent
0
dsackerContract ERP Admin/ConsultantCommented:
Why don't you put an UPDATE trigger on that table that has the closed flag, and when that particular field is changed to 'Y', kick off the stored proc.
0
GPSPOWAuthor Commented:
How do I do this?

The source table for the VIEW is a table that gets updated once a day with the DateTime the system closes.  A new record is appended to this table every night the main system closes.  My database cannot access main system to monitor when the main system finishes its nightly close routine.  I can only read this information file.

If I do not run my SP the time the system closed within a minute or two, I will be out of balance.

Any suggestions?

Glen
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dsackerContract ERP Admin/ConsultantCommented:
You said you can't access the "main system" when it finishes its close. Is the source table on your database or on another database on this "main system"?
0
GPSPOWAuthor Commented:
The source table livedb.dbo.BarDailyStats is in the Db.  It gets updated once a day and contains all Midnight Close records (1 per day) since 2007.

glen
0
GPSPOWAuthor Commented:
Here is what I am going to try tonight:

create trigger TR_DAILY_STATS
on livedb.dbo.BarDailyStats
After Update
as
Begin
exec sp_Daily_ATB
End
Go


The trigger script above was accepted.  However, I could not find the Trigger in the SQL Db folders.  I know it created because when I envoked the code again, I got an error that the object existed.

Glen
0
dsackerContract ERP Admin/ConsultantCommented:
You have to right-click and refresh the table ion the SSMS folder.
0
GPSPOWAuthor Commented:
I tried that.


I will check with the DBA tomorrow.

Glen
0
dsackerContract ERP Admin/ConsultantCommented:
You may want to fine-tune that trigger, because if anything updates that table for ANY field, for ANY value, it will kick off your stored proc.

Also, to be sure, does the main system's "close" process add a new record each time? Or does it update an existing record?

And what is the name of the field it updates?

I ask because, you may need the trigger to be an AFTER INSERT, if it is a new record. Make it an AFTER UPDATE if it is an updated record.
0
dsackerContract ERP Admin/ConsultantCommented:
Also, you can add this to the top, so that your CREATE will never fail:
IF OBJECT_ID('TR_DAILY_STATS') IS NOT NULL
    DROP TRIGGER TR_DAILY_STATS
GO
CREATE TRIGGER TR_DAILY_STATS
ON livedb.dbo.BarDailyStats
...
...
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
I  changed it to AFTER INSERT since the table inserts a new record once a day when the main system closes.

I will see if this works tomorrow morning.

Thanks

Glen
0
Andrei FomitchevCommented:
I WOULD ADD CHECK:

create trigger TR_DAILY_STATS
on livedb.dbo.BarDailyStats
After INSERT,Update
as
Begin
	-- YOU CAN COMPARE DATE FROM INSERTED WITH GET_DATE() AS WELL
	IF (SELECT FIELD_YN FROM INSERTED) = 'Y'
	exec sp_Daily_ATB
End
Go

Open in new window

0
GPSPOWAuthor Commented:
I finally got this checked and the DBA on board.

It works now.

thanks

glen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.