Solved

SQL trigger, how to get the old value for a cell before update in a trigger

Posted on 2012-04-11
4
575 Views
Last Modified: 2012-04-11
I have a AFTER update trigger on my SQL table. ? How do I get the value of a cell, before the update was done.

In the following  trigger is it possible to ge the @oldPrmvndr  , which is the old value of the column primvndr  for the row that was updated.

ALTER TRIGGER [dbo].[trItemQueueUpdate]
   ON  [dbo].[mytable]
   AFTER UPDATE
AS
IF UPDATE(PRIMVNDR)
BEGIN
declare @newPrmvndr  varchar(15)
declare @oldPrmvndr  varchar(15)
DECLARE @primvndr varchar(15)
DECLARE @ITEMNMBR varchar(31)


select  @ITEMNMBR = itemnmbr, @LOCNCODE = LOCNCODE,@newPrmvndr  = primvndr from inserted
ENd
0
Comment
Question by:TrialUser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 37833579
The old value is stored in the table deleted. Use a select ..... from deleted, the same way you have used a select ... from inserted.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37833670
Your question assumes that only one row is being updated, but consider that a trigger may apply to many rows.  

Generally, you would do like this to pull the old values associated with the new values

Select i.itemnmbr, i.LOCNCODE, i.primvndr as newPrmvndr, o.primvndr as oldPrmvndr
from inserted i inner join deleted d on i.primarykey = d.primarykey  (probably itemnmbr)


Or you would use a construct similar to that as the source for the actions of your trigger.

Just remember, you need to use set logic, not record logic. This construct:  
select  @ITEMNMBR = itemnmbr...
is not logically correct because it just retrieves one itemnmber, more-or-less arbitrarily.













 

ALTER TRIGGER [dbo].[trItemQueueUpdate]
   ON  [dbo].[mytable]
   AFTER UPDATE
AS
IF UPDATE(PRIMVNDR)
BEGIN
declare @newPrmvndr  varchar(15)
declare @oldPrmvndr  varchar(15)
DECLARE @primvndr varchar(15)
DECLARE @ITEMNMBR varchar(31)


select  @ITEMNMBR = itemnmbr, @LOCNCODE = LOCNCODE,@newPrmvndr  = primvndr from inserted
ENd
0
 

Author Closing Comment

by:TrialUser
ID: 37834462
Perfect
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37834807
Consider yourself warned: SQL server does not support row-level triggers.  Deploy one at your own risk.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

624 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