Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update a Record Using a Trigger with SELECT Statement Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 17, 2013 1:33 PM
Points: 6, 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
Post #1371284
Posted Thursday, October 11, 2012 2:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:11 AM
Points: 2,926, Visits: 5,408
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1371293
Posted Thursday, October 11, 2012 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:05 AM
Points: 43, Visits: 162
Hi Damion,

Have you tried the select and update queries separately.
Run them and see if they are working fine.?

Post #1371485
Posted Thursday, October 11, 2012 7:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 1,086, Visits: 7,982
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
Post #1371494
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse