Queries assignment udf_and_triggers

Queries assignment udf_and_triggers

• 1.
  -----------------------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 ------------------------------------------------------------------
• 2.
  --Ex: Function toFind 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)
• 3.
  -- 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)
• 4.
  -- 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()
• 5.
  --Modify or AlterUDF 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')
• 6.
  ----------------------------------Triggers----------------------------------- ----------------------------------------------------------------------------- --Defination : Atrigger 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))
• 7.
  SELECT * FROMtblPerson 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
• 8.
  --Now Updating inTable tblPersone UPDATE tblPerson SET address='Bhopal' WHERE id=3 ----------------------------------------------------------------------------- --Now Updated Records in tblPerson and tblPersonUpdate SELECT * FROM tblPerson SELECT * FROM tblPersonUpdate