[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to detect instigating record from within a trigger

Posted on 2009-12-16
7
Medium Priority
?
288 Views
Last Modified: 2012-05-08
I am using a trigger in SQL Server 2005. The Trigger is initiated AFTER UPDATE each time a record in the specified database table is made.
I wish to know how I can identify the specific record in the table that was modified and resulted in the initiation of the trigger ?.

0
Comment
Question by:alcindor
7 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 26066688
Have a real good read here -> http://msdn.microsoft.com/en-us/library/cc917674.aspx
Covers lots of aspects of record versioning including the use in an AFTER UPDATE stmt
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26066897
Use the UPDATED temp table, then get the values for the columns that compose your primary key to identify the row.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26066908
you need to use the INSERTED and DELETED(containing new and old values of changed records respectively).
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 15

Expert Comment

by:jorge_toriz
ID: 26074436
Jejeje, yes, there was an error in my comment, the DELETED table is the name of the temp table where you can locate the modified row.
0
 
LVL 2

Author Comment

by:alcindor
ID: 26100495
I do not understand what you mean by the temp table referred to in your answers. The column that was modified is on no interest to me in this application, just the row (record) that was modified.

I did not have the time persue the matter during tha past 3 days, hence the lack of response.

Roger
0
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 2000 total points
ID: 26101782
I call the DELETED table as a temp table because it only lives during the trigger execution.
0
 
LVL 2

Author Closing Comment

by:alcindor
ID: 31667024
Thanks for the clarification, now I understand your original comments and the solution is exactly what I need.

Seasons greetings also,

Roger
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question