TrialUser
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect
Consider yourself warned: SQL server does not support row-level triggers. Deploy one at your own risk.
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:
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