Update a Record Using a Trigger with SELECT Statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Update a Record Using a Trigger with SELECT...

Update a Record Using a Trigger with SELECT Statement

Rate Topic

Display Mode

Topic Options

Author

Message

Damion

Posted Thursday, October 11, 2012 2:31 AM

Forum 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

Eugene Elutin

Posted Thursday, October 11, 2012 2:42 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541, Visits: 4,370

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1371293

ard5karthick

Posted Thursday, October 11, 2012 7:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:51 AM
Points: 38, Visits: 129

Hi Damion,

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

Post #1371485

ChrisM@home

Posted Thursday, October 11, 2012 7:49 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 921, Visits: 3,741

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

CELKO

Posted Thursday, October 11, 2012 1:31 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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 »

Permissions