Triggers with OLD and NEW Values

Question

I need to create a trigger which will update the same table column with the other coulmn of the tables if the other column had a NULL old vlaue .something like below

Create trigger count_Dept

ON Dept

after insert or update

for each row

begin

IF (:old.count1 is null and :new.count2 is not null)

update dept set count2= :new.count1

where  :old.count1 is null and :new.count2 is not null

end ;

We can do something like this in oracle how can we do it in SQL server ????

 

Answer 1

Hi,

For old column values, You can directly select values from "deleted". You can refer to the new values using "inserted", like

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
	declare @empid int;
	declare @empname varchar(100);
	declare @empsal decimal(10,2);
	declare @audit_action varchar(100);

	select @empid=i.Emp_ID from inserted i;	
	select @empname=i.Emp_Name from inserted i;	
	select @empsal=i.Emp_Sal from inserted i;	
	set @audit_action='Inserted Record -- After Insert Trigger.';

	insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
	values(@empid,@empname,@empsal,@audit_action,getdate());

	PRINT 'AFTER INSERT trigger fired.'
GO

Read

Sql server trigger

And the way to select from "deleted" is the same as the above one (that is to select from "inserted").

---

Many Thanks & Best Regards, Hua Min

Answer 2

I wan to compare OLD and NEW Values in Where Clause and IF condition too.So i need old values as well. and can i compare variables in wher clause ?

for example

where @OLDVALUE is null and @NEWVALUE IS NOT NULL

i want to check the above condition .and update the coulmn with the other column of the same table . 

Answer 3

Hi,

You need to select value from existing table into @oldvalue, and do the same to select value from inserted into @newvalue.

---

Many Thanks & Best Regards, Hua Min

Answer 4

But it will be triggered after insert right ? so how do we get the old data ?or it is running before the insert ?

Answer 5

Try to select from the current table for the old column value.

---

Many Thanks & Best Regards, Hua Min

Answer 6

For old column values, You can directly select values from the table.

That is incorrect.  In an after trigger, the columns in the affected rows are the after-images and contain the same values as those found in the virtual inserted table.  You can only access the "old" values via the virtual deleted table.  Worse for everyone (and OP especially) is that you post code that is fundamentally flawed since it assume a single row is affected by the insert or update statement.  You should know better.

Answer 7

While you can do what you describe, I'll suggest that you should not.  Your description sounds like it serves to only to overcome a schema problem.  It is better to fix the schema than to start writing code to work around it.

Answer 8

Thanks scott, i need one column to be updated only once when the value is changed for another column in the same table from NULL to any value .what can i do ?

Answer 9

Hi Mazhar,

OK, you get the value from "deleted" (that is giving you the old value of the column), and see if it is null like

isnull(@oldvalue,'')=''

You then compare it with new value (that can be retrieved in the way I showed above, from "inserted"), in the way like

isnull(@oldvalue,'')='' 
and isnull(@oldvalue,'')<>@newvalue

and based on such condition, you can then perform update if the condition is true.

---

Many Thanks & Best Regards, Hua Min

Answer 10

Hi Mazhar_Parkar,

I’m writing to follow up with you on this post. Does the workarounds above solve your issue? If the issue is resolved,  please help to close this thread.

Thanks,
Lydia Zhang

Answer 11

Yes this issue is resolved .Thank you all