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: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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: Today @ 1:20 PM
Points: 1,090, Visits: 6,543
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
Posted Thursday, October 11, 2012 1:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:12 PM
Points: 1,945, Visits: 2,937
Triggers are a sign of bad SQL; the schema is such a mess that DRI actions cannot be used. So you go to 1970's Sybase procedural code to patch it on the fly.

Using local variables in a block of procedural code makes things worse. It says that you are still writing BASIC or COBOL instead of nesting SQL expressions in a declarative language. (I hope that the V_ prefix does not mean “VIEW”).

If you get rid of the local fake COBOL registers, and bring the name into ISO-11179 rules with guessing, the body looks like this:


ALTER TRIGGER TR_Update_Interaction
ON DBO.Cust_Hist
AFTER INSERT
AS
UPDATE Cust_Hist
SET note_txt
= (SELECT consignment_something
FROM V_Loadnote_Carr
WHERE load_note_txt
IN (SELECT primary_ref FROM INSERTED));

Do you see a problem when a set of “consignment_something” is returned? It will not fix into a column. Columns are scholars; that is one of many ways that they are not like records in the file system in your mindset.

This is also not the way we keep history tables, either.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1371752
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse