Link to home
Start Free TrialLog in
Avatar of TrialUser
TrialUserFlag for Afghanistan

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of TrialUser

ASKER

Perfect
Consider yourself warned: SQL server does not support row-level triggers.  Deploy one at your own risk.