Solved

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

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 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