Solved

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

Posted on 2012-04-11
4
573 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 5 58
Trying to understand why my Index is so large 12 52
grouping by date only 6 22
Set a variable value in SQL Procedure 3 26
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 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