September 26, 2005 at 2:55 pm
I am fairly new to SQL and not sure how to do this. I need to modify the trigger below to include a description. Do I have to add an additional select/update
Select @VDESC = (Select VendorDesc from VENDOR
Where Vendor_ID = @VID)
Update VENDFLDR set VendorDESC = @VDESC
Where VendorID = @VID
or can I select the Vendor name and Vendor description within the same select/update below
_______________________________________________________
CREATE TRIGGER [trg_VendFldrName] ON [dbo].[VENDFLDR]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @fld int
DECLARE @VName char(40)
DECLARE @VID char(10)
DECLARE @VDESC char(50)
If Update(FLD)
Begin
Select @fld = fld FROM inserted
Select @VID = VendorID FROM inserted
Begin
Select @VName = (Select VendorName from VENDOR
Where Vendor_ID = @VID)
Update VENDFLDR set VendorName = @VName
Where VendorID = @VID
End
End
______________________________________________
Changinagain
September 26, 2005 at 4:49 pm
You have a couple of things goin going on here.
When you construct a trigger you have to always make it accept data sets.
if you structure the trigger the way you have it, it will only update the last record in your batch.
Second you should structure a data retrieval operation
Not good
Select @VName = (Select VendorName from VENDOR
Where Vendor_ID = @VID)
Good
Select @VName = VendorName
from VENDOR
Where Vendor_ID = @VID
But you don't need this anyway.
Why do you have the Vendor Name ("Denormalized") in two different tablez? Then vendor name should only exist in the vendor table, and you pull the vendor name by performing an inner join. If you do that you don't even need this trigger. What if the vendor name changes in the vendor table? the vendor name field in the vendfldr never gets updated unless this trigger somehow fires.
CREATE TRIGGER [trg_VendFldrName] ON [dbo].[VENDFLDR]
FOR INSERT, UPDATE, DELETE
AS
Update VENDFLDR
set VendorName = b.VendorName
from VENDFLDR a
join Vendor b on a.VendorID = b.VendorID
join inserted c on a.pk = c.pk -- (pk = whatever pk in your vendfldr table)
September 30, 2005 at 9:39 am
The tables are inherited so I can't claim them. I just need to use and update what is there.
The VENDOR table is a reference table. The user interactively adds a new VendorID record to the VENDFLDR. The trigger needs to get the name and description from the reference table and update the VENDFLDR table.
So if I use your example and add
set VendorDesc = b.VendorDesc
will that work?
Changinagain
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply