May 10, 2016 at 12:43 pm
Hi All, Please can I get some assistance with this trigger. I created the trigger and it's working. However, when I insert a new record in the payment table, it reduces all the patient_accountbalance in the Patient table. I only want it to reduce the amount for the patient that makes a payment.
CREATE TRIGGER [dbo].[PATIENT_ACCTBAL]
ON [dbo].[Payment]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE PATIENT
set Patient_AccountBalance=Patient_AccountBalance - Payment.Payment_Amount
FROM PAYMENT
where PATIENT.Patient_ID=PAYMENT.Patient_ID
END
I have attached the data in my patient table and payment table.
May 10, 2016 at 12:50 pm
Use the Inserted pseudo table rather than the Payment table. Use the inserted and deleted tables
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 10, 2016 at 1:02 pm
This sure would be more efficient to do from the application (or a stored procedure) ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2016 at 1:13 pm
Drew, thanks for responding.....I'm not sure I understand what you mean.
May 10, 2016 at 1:26 pm
ettentrala (5/10/2016)
Drew, thanks for responding.....I'm not sure I understand what you mean.
Did you read the article that Drew referenced?
May 10, 2016 at 3:08 pm
Thank you. I have it. Thanks for the reference Drew.
Regards,
Artnette
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply