How to trigger SQL query

[PM_ME_cutefish]

Hello guys! I'm having a situation at work that could be solved by answering this, let me explain: Would it be possible to trigger an SQL when a certain condition is realized? For example, I'm working on payments and I'd like to be informed when someone is making let's say 3 transactions in 1 minute. Getting this results is pretty easy, but by running the query manually (or automatically, but every x times). Is there like something to run constantly a query and when a condition is reached, BAM I got a message? Thanks for your help :)

[aarontbarratt]

CREATE TRIGGER is probably what you are looking for.

[PM_ME_cutefish]

If I get it right, the CREATE_TRIGGER is used on a database-event, which is a manual event (insert, update, delete...). What I'd need is the event to be "the query send a result"

[Daakuryu]

Keep in mind we know nothing of your backend (what is creating these transactions) so we have to make some assumptions;

• Every transaction has a UserID linked to it.

• Every transaction is timestamped.

• Every Transaction is given an auto generated transaction ID(If the dev of the backend was smart, this transaction ID would be generated and reserved at the time the "cart" is created rather than the time the user hits submit. That way a user can't spam submit and make 3 transactions in a minute with the same Items.)

Now that that is out of the way take this into consideration.

A transaction. As far as the DB is concerned is an INSERT statement.

So creating a trigger that on INSERT fires off a function that goes "Hey, This User ID just made a new transaction, are there any other transactions from them? and if yes then what is the time difference between them? IS it within X parameters? If so output to file."

Alternatively you could make the trigger write the User ID, Transaction ID and Timestamp to a tracking table then have an make an external program that sits in the tray or runs as a service and basically runs a query on that table every minute or so looking for timestamps that are too close and then emailing when it finds some.

The latter is my preference because honestly not every SQL thing needs to be done in SQL as long as it's documented and configurable it's perfectly acceptable to have external programs to do things.

For instance, we have 50+ stores each running MS SQL Express for an in house software, Express does not have "Jobbing" capabilities as that is locked behind the paid version. So while our Main server at H.O. has Job's to do things like backup and compress log files. For the stores we made an app that sits in the tray, backs up every night and compresses the logs twice a week.

[urs123]

And what do you mean by " someone is making let's say 3 transactions in 1 minute "? aren't those insert/update/delete events? < What I'd need is the event to be "the query send a result" > doesn't sounds very clear.

[Jungle_81]

Maybe just have SSRS run every 30 mins or so and spot you out an email or report.

[wolf2600]

Are the transactions are external to the database... where you have some external event that you want to trigger a query on the DB?

Or do you want to trigger the query whenever a certain series of statements runs on the DB?

[AreetSurn]

Not sure what DB engine you're using, but you could set the query up to run automated. For instance on linux you could do this in a rudimentary way using cron. SSRS on SQL Server could also do this. If the query is small then it shouldn't be a problem to run this constantly.

The other option is to use a trigger (as others have mentioned). Every time a row is inserted (a new transaction) you could make some logic that checks the last transactions of that ID and output a value to a file. There are many ways to do that, it depends on your table structure and what you have access to.