SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update a Record Using a Trigger with SELECT Statement


Update a Record Using a Trigger with SELECT Statement

Author
Message
Damion
Damion
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 44
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
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12818 Visits: 5478
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?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
ard5karthick
ard5karthick
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 162
Hi Damion,

Have you tried the select and update queries separately.
Run them and see if they are working fine.?
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5346 Visits: 10608
What's the UPDATE statement you are using, and is there any reason why it can't include VLoadnoteCarr?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search