October 11, 2012 at 2:31 am
I'm trying to use a trigger to update the record that has been created with addtional details, however the statement below does not update the record as desired. I know the trigger is firing as I've modified the @condetails with the word 'Hello' and this worked. I think the problem is to do with the select statement - any ideas?
ALTER TRIGGER TR_Update_Interaction
ON dbo.cust_hist
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
DECLARE @interaction char(22)
DECLARE @loadnote char(12)
DECLARE @condetails char(100)
SELECT @interaction = tkey FROM inserted
SELECT @loadnote = primary_ref FROM inserted
SELECT @condetails = consignment FROM VLoadnoteCarr WHERE load_note = @loadnote
UPDATE cust_hist
SET notes = @condetails
WHERE tkey = @interaction
END
October 11, 2012 at 2:42 am
Your trigger has one major flow.
When INSERT operation is performed the TRIGGER is fired for an operation, not for every inserted record.
The way your trigger is written it will only process your logic for one of the inserted records.
You should never read from INSERTED or DELETED into variables in triggers.
Here is example of how it should be done properly:
ALTER TRIGGER TR_Update_Interaction
ON dbo.cust_hist
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
UPDATE cust_hist
SET notes = v.consignment
FROM cust_hist ch
JOIN inserted i
ON i.tkey = ch.tkey
JOIN VLoadnoteCarr v
ON v.load_note = i.primary_ref
END
Now, if it still doesn't update as expected, you need to analyse JOINs. Are they using relevant key columns?
October 11, 2012 at 7:42 am
Hi Damion,
Have you tried the select and update queries separately.
Run them and see if they are working fine.?
October 11, 2012 at 7:49 am
What's the UPDATE statement you are using, and is there any reason why it can't include VLoadnoteCarr?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply