SQL Trigger prevents insert

Question

User1215529056 posted

I made this trigger to send an email once the PO was received and it prevents some POs from being received. about 75% of the time it works great and 25% of the time it prevents a row from being inserted.

Any suggestions on how to fix it?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Email_Recpt] ON [dbo].[POReceipt]

AFTER INSERT
AS
DECLARE @PONum NVARCHAR(15) = N'';

BEGIN
SET NOCOUNT ON;

SELECT @PONum = PONumber FROM inserted;

EXEC sp_send_PO_Receipt_Email @PONum
End

Answer 1

User2053451246 posted

Since this trigger runs after the record gets successfully inserted this trigger is not what is causing the row to not be inserted.  You have something wrong with the creation of the record, which upon successful creation, will then execute this trigger.

Answer 2

User1215529056 posted

I wish that were true. Enable trigger, can't receive a po. disable trigger can receive a PO.

What I want to do is to have an email sent out to the CSR that created the PO

Answer 3

User753101303 posted

Hi,

More likely sending the mail fails (incorrect mail address or whatever ?). AFAIK the trigger IS part of the transaction and so it DOES prevent to insert data (or more precisely to commit the change if the trigger fails).

My personal preference is to avoid this kind of work from within a trigger. Instead I would schedule a job to send mails on a regular basis (it could also allow to send a summary rather than multiple mails).

Edit: BTW you also assume that the trigger is processing only one row each time which is perhaps true depending on your app but if one day you need to do something "by hand" it could cause the trigger to work only on the last row.

Answer 4

User1215529056 posted

I am leaning towards a schedule. The issue with that is if I run it every 15 minutes there could be more than one PO received and each one could go to a different CSR.

The need to notify the CSR that the PO was received is they need to inspect and give the OK to send the final product out to the customer, PO is often for an outside service.

The longer the time between the PO being received and the OK to send out the longer the delay to the customer. Sometimes the schedule is that tight.

Answer 5

User-2082239438 posted

I made this trigger to send an email once the PO was received and it prevents some POs from being received. about 75% of the time it works great and 25% of the time it prevents a row from being inserted.

Any suggestions on how to fix it?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Email_Recpt] ON [dbo].[POReceipt]

AFTER INSERT
AS
DECLARE @PONum NVARCHAR(15) = N'';

BEGIN
SET NOCOUNT ON;

SELECT @PONum = PONumber FROM inserted;

EXEC sp_send_PO_Receipt_Email @PONum
End

As mentioned earlier, trigger is part of the transaction & if trigger failed then the insert statement will also get failed. Note that trigger will fire for insert statement ir-respective of the number of records inserted. If you will insert more than 1 records then your approach will get failed as you are assigning the variable for sending the mail (Assignment of the variable will work for only one records).

You will use the SQL JOB  to send the mail as well as create some flag in the database for successful sending of the mail.

You will define the SQL JOB interval based on your preference. It will run minimum 10 seconds.

You will find job details here

Answer 6

User1215529056 posted

I gave up on the trigger and went with a scheduled job that runs once every minute.

Thanks