August 21, 2011 at 1:10 am
I want to send out an email to Users if they enter incorrect data into database table. The User will record hours of work done for a client company. Whether my company charges for this work depends on whether the work was prior to an appointment date or after it. If it is prior they should enter a PRE file type and if after a POST file type. The table the work is recorded in is called the TAT_Time table. However, the table that the appointment date is in is the HBM_matter table. Is writing a trigger on an INSERT the best way to notify Users and is it possible to compare data entered with data in another table?
Cheers,
AndySugs 😀
August 21, 2011 at 3:29 am
How do your users enter their data into these tables? Do they create new rows in the tables using T-SQL insert statements? I'm asking because it would seem a responsibility of the program they used to enter the data to check their input. It seems very unlikely to me they do enter it using insert statements themselves.
It is of course possible to check for "incorrect" data using triggers. Sending out a mail is somewhat more complicated, but still very well possible. The way you're proposing it seems a bit too complicated though. I'd say to have the end users presented a warning/error message when they enter the data, not afterwards in their mailbox. They may have long gone home already when the mail arrives... In other words: have a word with the developers of the data-entry program to see if they can check the entered data and give the end users a hint if what they entered is not correct.
edit: yes, it is possible to reference other tables from within trigger code. It can be hard though to get the logic completely right in such triggers if contents of both tables may be changed by the end user (as seems to be the case in your example). And No, writing a trigger on insert is not the best way: you'd need at least also a trigger on the update event. It is possible to combine the insert and update into one trigger but only at the cost of additional logic complication. Do you see where this is going?
August 21, 2011 at 9:30 pm
Thanks for your reply. The data is entered on a website front-end developed by 3rd party company so would have to go to their developers to get instant message set up. I was thinking about creating constraint that would prevent user from entering incorrect data and a trigger thgat would send out an email. Or I could create an Update trigger that would fire on the Insert and correct the data and send an email out to the user explaining what they had done wrong.
For future reference though, how could I create an Insert trigger that would compare value entered with that of another table e.g. date work done (Tat_time table) with date of appointment (hbm_matter table). If work was done after appointment date and user had entered a PRE file type (pre-appointment) then would be an error as file type should have been POST.
August 22, 2011 at 6:40 am
This seems like a double posting of this question. Shall we continue in the other thread?
August 23, 2011 at 5:39 am
You should definitely NOT allow incorrect data to be recorded in the database.
If you have enough info to determine what the proper data should be, then do so, and let the user know.
If you don't have enough information to ensure that what it written to the DB is correct, reject the request.
Once incorrect data is in, it may never be corrected, or not corrected by some deadline -- payday, for example.
With a properly designed system, it should be impossible to enter incorrect data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply