| Author |
Topic |
|
dmarsden
Starting Member
13 Posts |
 Posted - 2010-04-08 : 22:16:31
|
| I am having a problem with an INSERT TRIGGER I have been trying to create. I was hoping to use the trigger to create a table that would be available in Crystal for me to report off of. I was also hoping to use it to send out a notification Email. Right now, if I include the where clause, the table will show up null. Without it, it will include all of the records from every service. Please tell me if you see something inherently wrong with my script. Thanks.create trigger CheckInEmailon dbo.ApptStatusafter updateasset nocount on;begininsert into CheckIn( Client_ID, Client_FName, Client_LName, Prov_FName, Prov_LName, Prov_UserName, Appt_Status, Service_Item, Last_Modified)select Client_ID=dbo.Client.ID, Client_FName=dbo.Client.FName, Client_LName=dbo.Client.LName, Prov_FName=dbo.Provider.FName, Prov_LName=dbo.Provider.LName, Prov_UserName=dbo.Provider.UserName, Service_Item=dbo.Service_Item.Service_Item_Description, Appt_Status=dbo.Service_Status.FullName, Last_Modified=dbo.ApptStatus.LastModifiedFROM CLIENTAPPT_TO_APPTSTATUS_COLLECTION LEFT OUTER JOIN APPTSTATUS LEFT OUTER JOIN SERVICE_STATUS ON APPTSTATUS.SERVICE_STATUS_MONIKER = SERVICE_STATUS.OID ON CLIENTAPPT_TO_APPTSTATUS_COLLECTION.OID_LINK = APPTSTATUS.OID RIGHT OUTER JOIN CLIENTAPPT LEFT OUTER JOIN Client ON CLIENTAPPT.CLIENT_MONIKER = Client.OID LEFT OUTER JOIN CLIENT_PLANNED_SERVICE LEFT OUTER JOIN SERVICE_ITEM ON CLIENT_PLANNED_SERVICE.SERVICE_ITEM_MONIKER = SERVICE_ITEM.OID RIGHT OUTER JOIN CLIENTAPPT_TO_CLIENT_PLANNED_SERVICE_COLLECTION ON CLIENT_PLANNED_SERVICE.OID = CLIENTAPPT_TO_CLIENT_PLANNED_SERVICE_COLLECTION.OID_LINK ON CLIENTAPPT.OID = CLIENTAPPT_TO_CLIENT_PLANNED_SERVICE_COLLECTION.OID ON CLIENTAPPT_TO_APPTSTATUS_COLLECTION.OID = CLIENTAPPT.OID RIGHT OUTER JOIN APPOINTMENT_TO_CLIENTAPPT_COLLECTION RIGHT OUTER JOIN APPOINTMENT LEFT OUTER JOIN PROVIDERAPPT LEFT OUTER JOIN Provider ON PROVIDERAPPT.PROVIDER_MONIKER = Provider.OID RIGHT OUTER JOIN APPOINTMENT_TO_PROVIDERAPPT_COLLECTION ON PROVIDERAPPT.OID = APPOINTMENT_TO_PROVIDERAPPT_COLLECTION.OID_LINK ON APPOINTMENT.OID = APPOINTMENT_TO_PROVIDERAPPT_COLLECTION.OID ON APPOINTMENT_TO_CLIENTAPPT_COLLECTION.OID = APPOINTMENT.OID ON CLIENTAPPT.OID = APPOINTMENT_TO_CLIENTAPPT_COLLECTION.OID_LINKwhere ClientAppt_To_ApptStatus_Collection.OID_Link = (select OID from inserted) and Service_Status.FullName = 'Checked In'order by ApptStatus.LastModified declare @provider varchar(200); declare @bodytext varchar(200); declare @MaxID int; set @MaxID = (select max(ID) from CheckIn) set @provider = (select Prov_UserName from Checkin where ID = @MaxID)+'@penndelmhc.org' set @bodytext = (select Client_Fname from CheckIn where ID = @MaxID) + ' ' + select Client_LName from CheckIn where ID = @MaxID) + ' ' + 'has arrived for their appointment. Thank you.' exec msdb.dbo.sp_send_dbmail @recipients= @provider, @body= @bodytext, @subject = 'Appointment has arrived.', @profile_name = DBMailProfiledelete from Checkin where datediff(d,Last_Modified, getdate()) > 180 end |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-08 : 23:32:00
|
| You need to JOIN to the inserted trigger table.INNER JOIN insertedON inserted.SomeColumn = ApptStatus.SomeColumnBy the way this is a very bad idea. If sp_send_dbmail ever hangs up for whatever reason, the user's transaction will never complete which could cause blocking. You should instead modify the code that performs the insert so that the email is sent asynchronously and not part of the update transaction.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
dmarsden
Starting Member
13 Posts |
Posted - 2010-04-09 : 00:00:27
|
| "You should instead modify the code that performs the insert so that the email is sent asynchronously and not part of the update transaction."I am not exactly sure what you mean. Should I set the trigger to call on a stored procedure that sends the mail? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dmarsden
Starting Member
13 Posts |
Posted - 2010-04-09 : 10:08:16
|
| Hey Tara. Thanks for the help. The update is in the application. I am not sure how I would set the Email up there. Any ideas? As for the trigger, I joined the inserted table in the select statement but still had the same problem. The trigger is not returning any data. It looks like the inserted.oid is not the same as apptstatus.oid. I am updating the apptstatus table so I thought apptstatus.oid = inserted.oid. When I modify the script, I can get the trigger to return inserted.oid, which I can then find in the apptstatus table. It just doesn't work at the time the trigger is executed. At that time it looks like there is no apptstatus.oid or apptstatus.oid <> inserted.oid so the query returns no data. Thanks again. This is just killing me. Have a good one. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-09 : 19:19:47
|
| You can call sp_send_dbmail from your application just like you would any other stored procedure. From an application though, you don't need Database Mail to send email. Whatever application language you are using should have the ability to send email natively without SQL Server's help.As for the join issue, I'm not sure. You'll need to debug by checking what's inside the inserted table in order to know how to proceed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dmarsden
Starting Member
13 Posts |
Posted - 2010-04-13 : 15:16:50
|
| Thanks again Tara. Any hints on where to start looking for the problem? I have tried checking the tables but I'm not sure what to look for exactly. Do you know of any tutorials or discussions on debugging querries? Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-13 : 15:27:48
|
| We put Emails into a table (rather than sending them "there-and-then") [table has ToName, ToEMailAddress, FromName, FromEMailAddress, Ditto for CC / BCC, Subject, PlainTextMessage, HTMLTextMessage] and then we have a background task that takes rows from this table and sends them.We also have columns for Status (Waiting, Failed, Sent) and for Priority (we want a "Forgotten password" Email to jump the queue ahead of "Marketing" emails) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-13 : 18:50:59
|
| Have the trigger print out "select * from inserted" so that you can visually inspect it.What Kristen mentioned is the preferred way to do emails as it is asynchronous. Can you have a SQL Agent job do the email instead of the application? You can even have the job run every minute so that it happens near real-time. And if you need an immediate email sent, you could call sp_start_job instead of waiting for the next scheduled run.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dmarsden
Starting Member
13 Posts |
Posted - 2010-04-15 : 17:28:19
|
| Ok, I tried to take a look at what the query is really doing. What I believe is happening is when the trigger is activated, it runs the query but at this point the OID I am looking for doesn't exist. Therefore the ClientAppt_To_ApptStatus_Collection.OID_Link = (select OID from inserted)reference returns null values. I was hoping that using AFTER INSERT would let the insert, and any subsequent actions take place before the trigger activated. It appears that the OID I am looking for is created after the insert statement takes place. I can find it in the tables, but when I set the trigger to show both OIDs, only the INSERTED.OID was there. Does this sound realistic?If so, I was going to have the INSERT trigger create a table of OIDs I could use in a sp to link back to the other info after it is complete. Then the sp could be set up to create the table I need for reporting. I could schedule it to run every 5 minutes or so. One question, any ideas on how taxing this would be on the server?As for the Email I see what you are saying. Create a table with the mail info and set up a job to be called to start by the sp above to.Let me know if I am way off base. Thanks again for the help. Have a good one! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 03:01:04
|
| " I was hoping that using AFTER INSERT would let the insert, and any subsequent actions take place before the trigger activated."Your trigger operates on the ApptStatus. The new Update data will be present in that table when the trigger fires (and the same data will be present in the INSERTED table).However, maybe your query is relying on data in ClientAppt_To_ApptStatus_Collection which is added after the UPDATE to the ApptStatus table. If so, yes, you won't be able to see that data in the trigger - 'coz it doesn't exist yet!All you can really do is either store some data in a "TODO" table (from within this trigger) and then handle (and delete) the TODO tasks latter in the process, or modify the actual process to do the task you are trying to add to the Trigger itself.I use a Trigger when ANY action on the table must perform some collateral action - that includes a DBA performing actions directly on the table.For business logic that does not have to be done on every table action then I do that outside the Trigger (normally in a Stored Procedure) |
|
|
|
dmarsden
Starting Member
13 Posts |
Posted - 2010-04-16 : 13:57:49
|
| Sorry about the confusion. I originally used ApptStatus.OID = Inserted.OID for the testing. That's why I don't really understand why the Inserted.OID would not also be present in the ApptStatus table as ApptStatus.OID. When I query the table directly, after the insert, I can find it. But, when I set the trigger to return the Inserted.OID and the ApptStatus.OID, it could only find the Inserted.OID. I just don't get it. I tried Emailing our application vendor to see if I was missing anything but I haven't heard anything.Anyway, I took your advice and set up a process where the Email is sent outside of the trigger. I have two stored processes that I would like to set up as jobs, running every 5 min. First the trigger updates a new table leaving the inserted.OID and last modified date. The first SP would then use that info to create the "Check In" table I needed for reporting. The second could then use the info from the "Check In" table to send the Emails. The only thing that worries me is setting these jobs up to run so often. Any ideas on what effect this may have on our server? Thanks again to you both for the help. It really is much appreciated. Have a good one. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-16 : 15:01:32
|
A frequently running job is not a problem IMHO if:The query runs efficiently - i.e. it has suitable indexes etc. to find the records to be processed efficientlyThere are not huge numbers of rows to process. You were planning to do the Emails in a trigger, moving them to a batch process means that the number to-be-send is exactly the same, so the same amount of work for the server, so "no difference"So we are just down to whether the emails-to-be-sent query can quickly locate the records that need processing. Such a query can be optimised with indexes etc. to be as efficient as possible  |
|
|
|
dmarsden
Starting Member
13 Posts |
Posted - 2010-04-16 : 23:24:22
|
| Nice. Sounds like it should work then. The queries aren't very complex and the number of rows to process should never be more then five. Thanks again to both of you for the help! Have a great weekend!! |
|
|
|
|
Insert Trigger