Solved

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generate Weekly Schedule 15 28
SQL log file keeps growing despite getting successful log backups 4 48
SQL Server 2012 r2 - Sum totals 2 24
sql how to count case when 4 16
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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