Sunday 30 June 2013

Is it possible to set trigger priority in sql server?

Yes.
    We know trigger is event which is implicitly called on any kind of DML or DDL operation occurred. It may be interview question Suppose I have one table and I created three trigger on that table for update. So which trigger will be fired when?
    If you dont know this concept you will say it will be fiired  randomly.But we can fire it as per our requirement.
  In sql server we have  sp_settriggerorder which will help us to set triggger priority.

sp_settriggerorder 'triggername','value', 'statement_type' 
 
Argument :
trigger Name : Name of trigger
 
value  : It has three value 
First Trigger is fired first.
Last Trigger is fired last.
None Trigger is fired in undefined order.

 
 statement_type:
 
 Specifies the SQL statement that fires the trigger.
like  INSERT, UPDATE, DELETE.

USE  Your_Database; 
GO 
sp_settriggerorder @triggername= 'TriggerName', @order='Type', @stmttype = 'UPDATE';




Saturday 1 June 2013

SQL query to find second maximum salary of Employee

  Hi,
   Yesterday my friend attended inteview in I B M  for database devloper
 They asked him simple question .Check it out.
           
       Tell me three ways I can get above result .....

Table  -create table Employee (id int,salary money)




1.
SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);


2.
SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee)



3.In sql server - using top  keyword
SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp 
 ORDER BY salary ASC

 

First Database In Sql Server