Using IF UPDATE() in SQL Trigger

At times you have to update a particular record only when particular field has been updated. In that case you don’t necessary have to handle that on front end, you can handle that in SQL as well.

If you have a SQL Trigger and you need to make sure SQL Trigger fires only if value of particular field/column has been modified then you can use “IF UPDATE()” in trigger.

Following is the syntax of using the “IF UPDATE()” in SQL trigger,

create trigger trgUpdate 
on employees 
for update 
    if update (Dept) 
        update employees 
        set updated=1 
        where id=(select id from inserted) 

The following line check if the Dept of particular employee has been updated, If it has, then only it executes the further query,

  if update (Dept) 

Hope this helps 🙂

SQL Query to calculate top nth salary Interview Question

Previously I have written a post on Find out Nth highest value in SQL but that is not a good solution. You should try to avoid using “In” clause in your queries. The “In” clause uses lot of memory and should be avoided. So this post tells how to do the same using “Joins” rather than “In” clause.

The below query will fetch the 3rd highest salary,

select top 1 e.firstname, e.lastname, e.salary from employees e
left outer join (select top 2 employeeid from employees order by salary desc) b on e.employeeid=b.employeeid where b.employeeid is null
order by e.salary desc

If you want to fetch some other number change the “2” in the following line in the above query

 (select top 2 employeeid from employees order by salary desc) b

If you know a better way, please let us know.
Hope this helps 🙂