How do I use a MSSQL trigger to add data to a record?

ivh05
ivh05 used Ask the Experts™
on
I have a Table that needs to be updated with new information when  new record is added; the additional information is stored in a View. The view contains many records and sometimes a field value will be in both the Table and the View.  Once that condition is met I need certain values from the View to populate the Table record.

I am hoping my code is close enough to explain what I am hoping to achieve.   I am also hoping that someone will be kind enough to show me how to fix my trigger attempt.
ALTER TRIGGER [dbo].[trigResolveTransactions2] ON [dbo].[tblTransferredItems] 
FOR  INSERT
AS
DECLARE @ResolveDate As DateTime
DECLARE @ItemBarcode As varchar
DECLARE @Description As varchar
 
IF @@ROWCOUNT > 0
BEGIN
SELECT @ItemBarcode = ItemBarcode From inserted
SELECT @ResolveDate = GETDATE()  
SELECT @Description = (Select Description FROM dbo.vResolveForTrigger1 where @ItemBarcode = ItemNumberID)
UPDATE tblTransferredItems
SET DateResolved = getdate(), HCPSCDescription = @Description
WHERE TranID IN ( SELECT TranID FROM INSERTED )
 
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
ALTER TRIGGER [dbo].[trigResolveTransactions2] ON [dbo].[tblTransferredItems]
FOR  INSERT
AS

UPDATE ab
SET DateResolved = getdate(),
HCPSCDescription = Description
from tblTransferredItems ab
INNER JOIN inserted i on ab.TranID = i.TranID
INNER JOIN dbo.vResolveForTrigger1 t on t.ItemNumberID = i.ItemBarCode

Author

Commented:
aneeshattingal: Very Nice.... Thank-you!
Additionally, to be thorough, you should only do that update if it's needed. For instance, if the resolved date is null, or if the ItemBarCode is being changed. Depends on the situation. In your's probably:

if update(ItemBarCode) then
    do mentioned update
end

This prevents the trigger from doing uneccessary work if you're updating some other column, and not the trigger column. Of course, if you're always updating ALL columns, this is a worthless check.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial