January 29, 2008 at 9:43 am
I have a table that is used in our AP department. I needed to add a new field called "Date_Paid" and I wanted to update this field using a trigger.
The details are as follows.
The user enters in the account to be paid and includes all the information necessary to pay the bill. There is a field called "Status" that defaults to a value of "To be paid"
When the file is reviewed and the clerk usts a check, they change the status to "Paid" for that record. I want to then use a trigger to update the "Date_Paid" field with the systems date.
I am not new to programming, but I am new to Triggers. I was able to work an Update trigger but it updated all the records in the database. Anyone know how to help?
here is what I have:
ALTER TRIGGER [dbo].[UpdatePaidDate]
ON [dbo].[AP]
AFTER UPDATE
AS
update ap set date_paid=getdate() where status in (select status from inserted where status ='Paid')
BEGIN
SET NOCOUNT ON;
END
January 29, 2008 at 10:46 am
First - you might care to implement this as an INSTEAD OF trigger, since you're running an UPDATE in an UPDATE trigger (if anyone changes some of the database settings on triggers, your AFTER trigger might cause your trigger to start looping and then fail).
Second - you might care to double-check that the status was just flipped to PAID, otherwise your trigger will change the PAID date every time ANY update happens to a record that has been marked as paid. (for example - if someone goes in later to change a comment on a previously paid record, your current trigger would update it).
That being said - your SQL statement is telling the trigger to update every record in the DATABASE that has a status of PAID. Why? Your sub-query is stating:
SELECT status from inserted where status='PAID'
You need to match the records up to the outer query based on AP's primary key, not status.
Consider something like this:
update ap
set date_paid=getdate()
where ap_PrimaryKey --you will have to replace with your field's name
in (select i.ap_PrimaryKey
from inserted i
inner joint deleted d ON i.ap_PrimaryKey=d.ap_PrimaryKey
and i.status<>d.status --the status was just updated
where
i.status ='Paid'
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 29, 2008 at 12:49 pm
THANK YOU!!!
That worked perfectly. I love it when things work out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply