Trigger Help Needed

Question

Trigger Help Needed

Rheal Dugas-326915

SSC Enthusiast

Points: 139
More actions
May 10, 2006 at 5:50 am

#114140

I hope I have posted this in the proper thread.
I am in need of a trigger that will check a table for new entries and be triggered if the entry is 2 hours old and there is no entry in the accepteddate field.
There is a field in my table called datecreated that has the date time information in it when the record was created.
Once it has found a record It will send a email out to a address and update a field in that record called sent with a 1 and of course not be triggered again until the next 2 hours have past or is 4 hours old and so on.
I have the email script already.
Now the final part, once there is a entry into the accepteddate field the sent field is filled with a 2 and no further emails are sent
This may have to be 2 seperate triggers.
I woulld really appreciate some help with this Thank You
Rheal Dugas
Xerox Canada

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Marshall Smith SSCrazy Points: 2041 More actions · Answer 1

You don't actually want a trigger, since a trigger is activated by table activity, not time. What you want is probably a scheduled job.

Rheal Dugas-326915 SSC Enthusiast Points: 139 More actions · Answer 2

Never used Jobs before.

So basicly I would create a job to check and see if the Datecreated field is 2 hours old and the Accepeted date field is NULL and than exec my email stored Proceedure.

Would I have to have this job running all the time? New records are being created constantly and I dont think that have the job running only every 2 hours would work right,I may be wrong though.

Would you or someone be able to provide me with a example on what the code would be that I would put into the steps of the job to do this?

Thank You

Mike C SSC-Insane Points: 23224 More actions · Answer 3

Would I have to have this job running all the time? New records are being created constantly and I dont think that have the job running only every 2 hours would work right,I may be wrong though.

First thing to do is define what you want exactly before you waste a bunch of time on a solution that turns out to be useless to you: Do you want to check the table once every two hours, once every 10 minutes or once every time a new row is inserted?

Rheal Dugas-326915 SSC Enthusiast Points: 139 More actions · Answer 4

I guess I would want to check it every 2 hours to see if a entry is 2 hours old and the person it was dispatched to has not accepted it yet

Mike C SSC-Insane Points: 23224 More actions · Answer 5

Then you need to set up a scheduled job to run every two hours. A good place to start is to go into Enterprise Manager, right click on "Management" and select "New Job...". You can enter the steps, SQL commands, schedules and notification information there. That's the easy way to get started and get acquainted with jobs. SQL Server Agent will need to be running as well to kick off your job on schedule.

Rheal Dugas-326915 SSC Enthusiast Points: 139 More actions · Answer 6

Ok..Found that section. Are you able to give me a example of the type of commands I would use to check my table for records that are over 2 hours old and when I find one how do I update that record and put a value of 1 into the field name "sent"

Thank You ...I can see that this way may be better than a trigger.

schleep SSChampion Points: 12585 More actions · Answer 7

UPDATE

SET sent = 1

WHERE sent IS NULL

AND Received(?) < DATEADD(HOUR, -2, GETDATE())

Rheal Dugas-326915 SSC Enthusiast Points: 139 More actions · Answer 8

I am getting a Syntax error with this.

error 156..Incorrect syntax near keyword set

Can anyone help

UPDATE

SET sent = 1

WHERE accepteddate IS NULL

AND datecreated < DATEADD(HOUR, -2, GETDATE())

Marshall Smith SSCrazy Points: 2041 More actions · Answer 9

You need to specify your table. E.g.,

UPDATE myTable

SET sent = 1

...

Rheal Dugas-326915 SSC Enthusiast Points: 139 More actions · Answer 10

Here is the Step 1 that I created.

UPDATE dbo.ONSA_NSS

SET sent = 1

WHERE sent IS NULL

AND (datecreated BETWEEN DATEADD(hh, - 2, GETDATE()) AND DATEADD(hh, - 1, GETDATE())) AND (accepteddate IS NULL)

This is the error I get when the job fails...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Can someone have a look and tell me why I get the error.

Thank You

Marshall Smith SSCrazy Points: 2041 More actions · Answer 11

I don't see anything in that statement involving a subquery. Do you have other code wrapped around it, perhaps error checking code?

schleep SSChampion Points: 12585 More actions · Answer 12

That code does not contain a subquery, therefore the error message refers to something else.

Rheal Dugas-326915 SSC Enthusiast Points: 139 More actions · Answer 13

Could it be because this code is a step inside a job?

Could I possiably create a stored procedure and have the job exec the stored procedure maybe?

And if I do that is the command "exec "Procedure Name"

schleep SSChampion Points: 12585 More actions · Answer 14

Absolutely put it in sproc.

And correct, the code to call it is

EXECUTE sproc_name