SQL

  

-----------------------User Degine Function---------------

----------------------------------------------------------

-- Type 1 > Scaler Functions :  Returns single value.

--Ex: Factorial of Given No.

CREATE FUNCTION UDF_Factorial( @num int)

RETURNS int

AS

BEGIN

DECLARE @fact int

SET @fact=1

WHILE @num != 0

BEGIN

      SET @fact=@fact * @num

      SET @num=@num-1

END

RETURN @fact

END

-----------------------------------------------------------------

--Execute User Define Function

SELECT dbo.UDF_Factorial(5) as Factorial

------------------------------------------------------------------

--Ex: Function to Find Employee MAX Salary of Given Dept

CREATE FUNCTION UDF_getMaxSal(@Deptid int)

RETURNS NUMERIC(10,2)

AS

BEGIN

DECLARE @MaxSal NUMERIC(10,5)

 SET @MaxSal=(SELECT TOP 1 Salary FROM tblEmployee WHERE DeptId=@Deptid ORDER BY Salary DESC)

RETURN @MaxSal

END

GO

--Execution of UDF in SELECT Statment

SELECT EmpId,EmpName,Address,Deptid,Salary FROM tblEmployee WHERE Salary=dbo.UDF_getMaxSal(2)

-- Type 2 >  Inline Table Valued Function : Returns Table Object

--Ex: Function to Return Record of Emplyees whose Salary > given salry

CREATE FUNCTION UDF_getEmpRecordsAboveGivenSal(@Salary int)

RETURNS Table

AS

return (SELECT EmpId,EmpName,Address,Gender,Salary FROM tblEmployee WHERE Salary > @Salary)

GO

-----------------------------------------------------------------------------

-- Execution

SELECT * FROM dbo.UDF_getEmpRecordsAboveGivenSal(25000)

-- Type 3 >  Multi Statement Table Valued Function :

--                Explicitly defines the structure of the table to return.

--                Defines column names and datatypes in the RETURNS clause.

--Ex: Fuction to get Employee Records with Dept Name

CREATE FUNCTION getEmpByDeptName()

RETURNS @EmpWithDept Table

(

      EmpId int,

      EmpName varchar(50),

      DeptName varchar(50)   

)

AS

BEGIN

 INSERT INTO @EmpWithDept SELECT e.EmpId,e.Empname,d.DeptName FROM tblDepartment d INNER JOIN tblEmployee e ON d.DeptId=e.DeptId

 return

END

GO

-----------------------------------------------------------------------------

-- Execute Multi Statement Table Valued Function

SELECT * FROM dbo.getEmpByDeptName()

--Modify or Alter UDF like Follows

Alter FUNCTION getEmpByDeptName(@DeptName varchar(50))

RETURNS @EmpWithDept Table

(

      EmpId int,

      EmpName varchar(50),

      DeptName varchar(50)   

)

AS

BEGIN

 INSERT INTO @EmpWithDept SELECT e.EmpId,e.Empname,d.DeptName FROM tblDepartment d INNER JOIN tblEmployee e ON d.DeptId=e.DeptId WHERE DeptName=@Deptname

UPDATE @EmpWithDept SET DeptName='DOT NET' WHERE DeptName='.NET'

 return

END

GO

--------------------------------------------------------------------------------------------------------------------------------

-- Execute Multi Statement Table Valued Function

SELECT * FROM dbo.getEmpByDeptName('.NET')

----------------------------------Triggers-----------------------------------

-----------------------------------------------------------------------------

--Defination : A trigger is an action that is performed behind-the-scenes when an event occurs on a table.

-- Types of Trigger: 1) Instead of/Before  2) After/For

-- There are Two Tables with Field and Diffrent name one is tblPersone and another is tblPersonUpdate

SELECT *  FROM tblPerson

SELECT * FROM tblPersonUpdate

-----------------------------------------------------------------------------

--Inserting Records into tblPerson

INSERT INTO tblPerson VALUES('Vinay','Sayaji,Indore',Convert(Varchar,GETDATE(),114))

INSERT INTO tblPerson VALUES('Rahul','Vijay Nagar,Indore',Convert(Varchar,GETDATE(),114))

INSERT INTO tblPerson VALUES('Hitesh','Khargone',Convert(Varchar,GETDATE(),114))

SELECT *  FROM tblPerson

SELECT * FROM tblPersonUpdate

--Creating Trigger on Table tblPerson After Update will Insert Old Record into tblPersonUpdate

CREATE TRIGGER UDT_PersonUpdate

ON tblPerson

After UPDATE

AS

      DECLARE @id int;

      DECLARE @name varchar(50);

      DECLARE @address varchar(50);

      DECLARE @time varchar(50);

     

      select @id=U.id from deleted U;

      select @name=U.name from deleted U;

      select @address=U.address from deleted U;

      select @time=U.time from deleted U;

     

BEGIN

      INSERT INTO tblPersonUpdate(id,name,address,time)VALUES(@id,@name,@address,@time)

      PRINT 'Record Has been Inserted into tblPersonUpdate'

     

END

--Now Updating in Table tblPersone

UPDATE tblPerson SET address='Bhopal' WHERE id=3

-----------------------------------------------------------------------------

--Now Updated Records in tblPerson and tblPersonUpdate

SELECT *  FROM tblPerson

SELECT * FROM tblPersonUpdate