November 1, 2004 at 5:22 pm
I have this Trigger
CREATE TRIGGER dbo.CardU ON dbo.Card
FOR UPDATE
NOT FOR REPLICATION
AS
SET NOCOUNT ON
IF UPDATE (Password)
OR UPDATE (PublisherMerchantId)
OR UPDATE (IssuerMerchantId)
OR UPDATE (CardIssueStatusInd)
BEGIN
UPDATE Card
SET EnrolmentUpdateDateTime = GetDate()
FROM Card
INNER JOIN INSERTED ON Card.CardBinNumber = INSERTED.CardBinNumber
AND Card.CardNumber = INSERTED.CardNumber
END
Which updates a field called EnrolmentUpdateDateTime if one of the above fields are updated. This works fine except when in my C# code I update everything even if it is the same. I would like to get the trigger to only populate the EnrolmentUpdateDateTime if the value is different than what it was. I known this can be done with the INSERTED and DELETED tables but am unaware on how to go about it.
Any help would be appreciated.
Thanks Brian
You are never an expert, you are always learning!November 1, 2004 at 8:29 pm
To quote from BOL: "An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table."
So ... after an UPDATE, the deleted table will contain the records as they were before the update. So all you need in your code is to use 'deleted' instead of 'inserted' and add a suitable WHERE clause:
...
from dbo.card c
INNER JOIN deleted d ON c.CardBinNumber = d.CardBinNumber
AND c.CardNumber = d.CardNumber
WHERE
(c.password d.password) or
(c.PublisherMerchantId d.PublisherMerchantId) or
(c.IssuerMerchantId d.IssuerMerchantId) or
(c.CardIssueStatusInd d.CardIssueStatusInd)
end
November 4, 2004 at 4:25 pm
Thanks for the help
Thanks Brian
You are never an expert, you are always learning!Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply