SQL Server Functions and Triggers PDF

Implementing Functions – Working with Triggers - Cursor Objectives In this chapter, you will learn how to: ❑ Implement Functions ❑ Implement Triggers ❑ Cursors Implementing Functions Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. Th...

Implementing Functions – Working with Triggers - Cursor Objectives In this chapter, you will learn how to: ❑ Implement Functions ❑ Implement Triggers ❑ Cursors Implementing Functions Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function. UDFs are of different types: scalar functions and table-valued functions. As a database developer, it is important for you to learn to create and manage different types of UDFs. Creating UDFs A UDF contains the following components: ❑ Function name with optional schema/owner name ❑Input parameter name and data type ❑Return parameter data type and optional name ❑One or more T-SQL statements To create a function, you can use the CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is: CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema__name. ] parameter_data_type [ = default ] } [ , --n ] ] ) RETURNS return__data_type [ WITH [ ,...n ] ] [ AS ] BEGIN function_body RETURN expression END [;] where, ❑ schema_name is the name of the schema to which the UDF belongs. ❑ function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema. ❑ @parameter_name is a parameter in the UDF. One or more parameters can be declared. ❑ [ type_schema_name. ] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs. ❑ [ = default ] is a default value for the parameter. ❑ return_data_type is the return value of a scalar user-defined function. ❑ function_body specifies a series of T-SQL statements. ❑ UDFs can be of two types, scalar and table-valued functions. The definition of each function is different. Therefore, it is important to learn how to create each type of function. Creating Scalar Functions Scalar functions accept a single parameter and return a single data value of the type specified in the RETURNS clause. A scalar function can return any data type except text, ntext, image, cursor, and timestamp. A function contains a series of T-SQL statements defined in a BEGIN...END block of the function body that returns a single value. Consider an example of a scalar function that calculates the monthly salary of the employees accepting the pay rate as an input and returning a single value after multiplying the value with the number of hours and number of days: CREATE FUNCTION HumanResources.MonthlySal ( @PayRate float) RETURNS float AS BEGIN RETURN (@PayRate * 8 * 30) You can execute this function by using the following statements: DECLARE @PayRate float SET @PayRate = HumanResources.MonthlySal(12.25) PRINT @PayRate END Creating Table -Valued Functions A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table- valued functions return the output as a table data type. The table data type is a special data type used to store a set of rows, which return the result set of a table-valued function. Table-valued functions are of two types: ❑ Inline table-valued function ❑Multi statement table-valued function Inline Table-Valued Function An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements. Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement: CREATE FUNCTION fx_Department_GName( @GrName nvarchar(20) ) RETURNS table AS RETURN (SELECT * FROM HumanResources.Department WHERE GroupName=@GrName ) GO You can use the following statement to execute the fx_Department_Gname function with a specified argument: SELECT * FROM fx__Department_Gname ('Manufacturing' ) The preceding statement will return a result set, as shown in the following figure. Consider another example of an inline function that accepts rate as a parameter and returns all the records that have a rate value greater than the parameter value: CREATE FUNCTION HumanResources.Emp_Pay(@Rate int) RETURNS table AS RETURN ( SELECT e.EmployeelD, e. Title. Rate FROM HumanResources. Employee AS e JOIN HumanResources.EmployeePayHistory AS er ON e.EmployeeID=er.EmployeelD WHERE er.Rate>@Rate ) GO Multistatement Table-Valued Function A multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN...END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. Consider an example where the multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements: CREATE FUNCTION PayRate (@rate money) RETURNS @table TABLE (EmployeelD int NOT NULL, RateChangeDate datetime NOT NULL, Rate money int NOT NULL, PayFrequency int NOT NULL, ModifiedDate datetime NOT NULL) AS BEGIN INSERT @table SELECT * FROM HumanResources.EmployeePayHistory WHERE Rate > @rate RETURN END The function returns a result set in form of a temporary table, @table, created within the function. You can execute the function by using the following statement: SELECT * FROM PayRate(45) Implementing Triggers At times, while performing data manipulation on a database object, you might also need to perform another manipulation on another object. For example, in an organization, the employees use the Online Leave Approval system to apply for leaves. When an employee applies for a leave, the leave details are stored in the LeaveDetails table. In addition, a new record is added to the LeavesForApproval table. When the supervisors log on to the system, all the leaves pending for their approval are retrieved from the LeavesForApproval table and displayed to them. To perform such operations, the SQL Server allows you to implement triggers. A trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain actions, such as insert or delete. Triggers are used to ensure data integrity before or after performing data manipulations. Before you implement a trigger, it is important to know the different types of triggers that can be created by using SQL Server. Identifying Types of Triggers In the SQL Server, various kinds of triggers can be used for different types of data manipulation operations. The SQL Server supports the following types of triggers: - Data Modification Language (DML) triggers - Data Definition Language (DDL) triggers DML Triggers A DML trigger is fired when data in the underlying table is affected by DML statements, such as INSERT, UPDATE, or DELETE. These triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex actions and cascade these actions to other dependent tables. Cascading is the process of reflecting the changes made in a table in the other related tables. The DML triggers have the following characteristics: Fired automatically by the SQL Server whenever any data modification statement is issued. Cannot be explicitly invoked or executed, as in the case of the stored procedures. Prevents incorrect, unauthorized, and inconsistent changes in data. Can be nested up to 32 levels. The nesting of triggers occurs when a trigger performs an action that initiates another trigger Whenever a trigger is fired in response to the INSERT, DELETE, or UPDATE statement, the SQL Server creates two temporary tables, called magic tables. The magic tables are called Inserted and Deleted. The magic tables are conceptual tables and are similar in structure to the table on which the trigger is defined. The Inserted table contains a copy of all records that are inserted in the trigger table. The Deleted table contains all records that have been deleted from the trigger table. Whenever you update data in a table, the trigger uses both the inserted and the deleted tables. Depending on the operation that is performed, the DML triggers can be further categorized as: Insert trigger: Is fired whenever an attempt is made to insert a row in the trigger table. When an INSERT statement is executed, a new row is added to both the trigger and the inserted tables. Delete trigger: Is fired whenever an attempt is made to delete a row from the trigger table. When a DELETE statement is executed, the specified rows from the trigger table are deleted and are added to the deleted table. The deleted and trigger tables do not have any rows in common, as in the case of the inserted and trigger tables. There are three ways of implementing referential integrity by using a DELETE trigger. These are: The cascade method: Deletes records from the dependent tables whenever a record is deleted from the master table. The restrict method: Restricts the deletion of records from the master table if the related records are present in the dependent tables. The nullify method: Nullifies the values in the specified columns of the dependent tables whenever a record is deleted from the master table. Update trigger: Is fired when an UPDATE statement is executed in the trigger table. It uses two logical tables for its operations, the deleted table that contains the original rows (the rows with the values before updating) and the inserted table that stores the new rows (the modified rows). After all the rows are updated, the deleted and inserted tables are populated and the trigger is fired. For example, you have a table with three columns. The table stores the details of hardware devices. You updated a value in column 2 from ‘Printer’ to ‘Lex New Printer’. During the update process, the deleted table holds the original row (the row with the values before updating), and the inserted table stores the new row (the modified row) with the value ‘Lex New Printer’ in Column2. DDL Triggers A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing. Database auditing helps in monitoring the DDL operations on a database. DDL operation can include operations such as creation of a table or view, or modification of a table or procedure. Consider an example, where you want the database administrator to be notified whenever a table is created in the Master Database. For this purpose, you can create a DDL trigger. Depending on the way in which triggers are fired, they are categorized as: After Triggers Instead of Triggers Nested Triggers Recursive Triggers After Triggers The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The main difference in the functionality of an after trigger is that it is fired after the execution of the DML operation for which it has been defined. The after trigger is executed when all the constraints and triggers defined on the table are successfully executed. By default, if more than one alter trigger is created on a table for a DML operation such as insert, update, or delete, then the sequence of execution is the order in which they were created. For example, the EmpSalary table stores the salary and tax details for all the employees in an organization. You need to ensure that after the salary details of an employee are updated in the EmpSalary table, the tax details are also recalculated and updated. In such a scenario, you can implement an after trigger to update the tax details when the salary details are updated. You can have multiple after triggers for any single DML operation. Instead of Triggers The instead of triggers can be primarily used to perform an action, such as a DML operation on another table or view. This type of trigger can be created on both a table as well as a view. For example, if a view is created with multiple columns from two or more tables, then an insert operation on the view is only possible if the primary key fields from all the base tables are used in the query. Alternatively, if you use an instead of trigger, you can insert data in the base tables individually. This makes the view logically updateable. You can even create an Instead of trigger to restrict deletion in a master table. For example, you can display a message “Master records cannot be deleted” if a delete statement is executed on the Employee table of the Adventure Works database. Unlike after triggers, you cannot create more than one Instead of trigger for a DML operation on the same table or view. Nested Triggers Nested triggers are fired due to actions of other triggers. For example, you delete a row from TableA. A trigger on TableA deletes rows from TableB. Because you are deleting rows from TableB, a trigger is executed on TableB to record the deleted rows. Recursive Triggers Recursive triggers are a special case of nested triggers. Unlike nested triggers, support for recursive triggers is at the database level. As the name implies, a recursive trigger eventually calls itself. There are two types of recursive triggers, Direct and Indirect. Direct Recursive Trigger: A direct trigger is a trigger that performs the same operation (insert, update, or delete) on the same table causing the trigger to fire itself again. Indirect Recursive Trigger: An indirect trigger is a trigger that fires a trigger on another table and eventually the nested trigger ends up firing the first trigger again. For instance, an UPDATE on TableA fires a trigger that in turn fires an update on TableB. The update on TableB fires another trigger that performs an update on TableC. TableC has a trigger that causes an update on TableA again. The update trigger of TableA is fired again Creating Triggers You can use the CREATE TRIGGER statement to create triggers. The syntax of the CREATE TRIGGER statement is: CREATE TRIGGER trigger_name ON { OBJECT NAME } { FOR | AFTER [ INSTEAD OF } { event__type [ ,...n ] | DDL___DATABASE_LEVEL_EVENTS } { AS { sql_statement [...n ] } } where, trigger_name specifies the name of the trigger to be created. table_name specifies the name of the table on which the trigger is to be created. for | after | instead of specifies the precedence and execution context of a trigger. as sql_statements specifies the trigger conditions and actions. A trigger can contain any number of T-SQL statements, provided these are enclosed within the BEGIN and END keywords. For example, the following statement create a trigger on the EmployeeDepartmentHistory table of the Adventure Works database: CREATE TRIGGER [HumanResources].[trgDepartment] ON [HumanResources].[Department] AFTER UPDATE AS BEGIN UPDATE [HumanResources].[Department] SET [HumanResources].[Department].[ModifiedDate] = GETDATE() FROM Inserted WHERE Inserted.[DepartmentID] = [HumanResources].[Department].[DepartmentID]; END; The preceding statements create a trigger named trgDepartment that is fired on every successfully executed update statement on the HumanResources.Department table. The trigger updates the ModifiedDate column of every updated value with the current date. The following statement displays the data that is inserted in the magic tables: CREATE TRIGGER [HumanResources].[trgMagic] ON [HumanResources].[EmployeeDepartmentHistory] AFTER UPDATE AS BEGIN SELECT * FROM Deleted SELECT * FROM Inserted END; The preceding statements create an update trigger on the HumanResources.EmployeeDepartmentHistory table. Whenever any update statement is fired on the [HumanResources],[EmployeeDepartmentHistory] table, the trgMagic trigger is executed and shows you the previous value in the table as well as the updated value. Suppose you fire the following update statement on the HumanResources.EmployeeDepartmentHistory table: UPDATE HumanResources.EmployeeDepartmentHistory SET DepartmentID = 16 WHERE EmployeelD = 4 When the update statement is executed on the table, the trgMagic trigger is fired displaying the following output In the preceeding figure, the result set on the top shows the values before the execution of the UPDATE statement. The result set at the bottom shows the updated values. Creating an insert Trigger Consider an example where the users at Adventure Works, Inc. want the modified date to be set to the current date whenever a new record is entered in the Shift table. To perform this task, you can use the following statement: CREATE TRIGGER trglSBertShifi ON HumanResources.Shift FOR INSERT AS DECLARE @ModifiedDate datetime SELECT @ModifiedDate = ModifiedDate FROM Inserted IF (@ModifiedDate != getdate()) BEGIN PRINT 'The modified date should be the current date. Hence, cannot insert.’ ROLLBACK TRANSACTION END RETURN Creating a Delete Trigger The following statement create a trigger to disable the deletion of rows from the Department table: CREATE TRIGGER trgDeleteDepartment ON HumanResources.Department FOR DELETE AS END RETURN PRINT 'Deletion of Department is not allowed’ ROLLBACK TRANSACTION RETURN Creating an Update Trigger Consider an example where you need to create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of Rate is increased. To perform this task, you can write the following statement: CREATE TRIGGER trgUpdateEmployeePayHistory ON HumanResources.EmployeePayHistory FOR UPDATE AS IF UPDATE (Rate) BEGIN DECLARE @AvgRate float SELECT @AvgRate = AVG(Rate) FROM HumanResources.EmployeePayHistory IF(@AvgRate > 20) BEGIN PRINT 'The average value of rate cannot be more than 20’ ROLLBACK TRANSACTION END END Creating an After Trigger Consider an example where you need to display a message after a record is deleted from the Employee table. To perform this task, you can write the following statement: CREATE TRIGGER trgDeleteShift ON HumanResources.Shift AFTER DELETE AS PRINT 'Deletion successful’ In case you have multiple after triggers for a single DML operation, you can change the sequence of execution of these triggers by using the sp__settriggerorder system stored procedure. The syntax of the spsettriggerorder stored procedure is: spsettriggerorder , , where, triggername specifies the name of the trigger whose order of execution is being changed. order-value specifies the order in which the trigger needs to be executed. The values that can be entered are FIRST, LAST, and NONE. If FIRST is mentioned, then the trigger. is the first trigger to be executed, if LAST is mentioned, then the trigger will be the last trigger to be executed. If NONE is specified, then the trigger is executed on a random basis. DML-operation specifies the DML operation for which the trigger was created. This should match the DML operation associated with the trigger. For example, if UPDATE is specified for a trigger that is created for the insert operation, the sp_settriggerorder stored procedure will generate an error. For example, you create another after trigger, trgDeleteShiftl on the Shift table. By default, the triggers are executed in the sequence of creation. However, if you need to execute the trigger named trgDeleteShiftl before executing the first trigger, trgDeleteShift, you can execute the following statement: sp_settriggerorder `HumanResources.trgDeleteShiftl', 'FIRST', 'DELETE' RETURN Managing Triggers While managing triggers, you can perform the following operations on a trigger: Alter a trigger Delete a trigger Altering a Trigger As a database developer, you might need to modify the logic or code behind a trigger. For example, a trigger is used to calculate a 10 percent discount on every item sold. With the new management policy, the discount rate has been increased to 15 percent. To reflect this change in the trigger, you need to change the code in the trigger. You can use the ALTER TRIGGER statement to modify a trigger. The syntax of the ALTER TRIGGER statement is: ALTER TRIGGER trigger_name { FOR | AFTER } { event__type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS } { AS { sql_statement [...n ] } } Consider an example. When an employee resigns or is transferred from one department to another, the end date is updated in the EmployeeDepartmentHistory table. After the end date is updated, the ModifiedDate attribute of the EmployeeDepartmentHistory table should be updated to the current date. You can modify the trglnsertShift trigger that was created earlier to check whether the ModifiedDate attribute is the current date or not. If the ModifiedDate attribute is not the current date, the trigger should display a message, “The modified date is not the current date. The transaction cannot be processed.” To modify the trglnsertShift trigger, you need to execute the following statement: ALTER TRIGGER HumanResources.trglnsertShift ON HumanResources.Shift FOR INSERT AS DECLARE @ModifiedDate datetime SELECT @ModifiedDate = ModifiedDate FROM Inserted IF (@ModifiedDate != getdate()) BEGIN RAISERROR ('The modified date is not the current date. The transaction cannot be processed 10, 1) ROLLBACK TRANSACTION END RETURN Deleting a Trigger To delete a trigger, you can use the DROP TRIGGER statement. The syntax of the DROP TRIGGER statement is: where, trigger is the name of the trigger you want to drop. The following statement drops the HumanResources.trgMagic trigger: DROP TRIGGER HumanResources.trgMagic Cursors The part of the memory where the process statements are stored is known as context memory. This memory contains all intermediate results, and this can be used for more optimized query. To access this memory location, a cursor is used, and the cursor can be resembled to pointers in some procedural language. The cursor contains the information on a select statement and the rows of data accessed by it. Further, cursor contains attributes that can be used in PL/SQL statements. The cursors are of two types 1. Implicit Cursors 2. Explicit Cursors Implicit Cursors As the name states, the implicit cursors are generated automatically when an SQL statement is executed. They are generated for all DML statements. Some of the attributes that are provided by DBMS are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. This example demonstrates the use of SQL%ROWCOUNT attribute. The table given below is used to understand the outcome. Create Employee table and have records: DECLARE rows number(2); BEGIN UPDATE Employee SET salary = salary+5000; IF sql%notfound THEN dbms_output.put_line(‘no Employee updated’ ELSIF sql%found THEN rows :=sql%rowcount; dbms_output.put_line(rows || ‘ Employees updated ’); END IF; END; Output: 5 Employees updated PL/SQL procedure successfully complete Explicit cursors The explicit cursors are defined in the PL/SQL block by the programmer to utilize the benefits of the context area. It is created on a SELECT statement which returns more than one row Following are the typical steps using the explicit cursor: 1. Declare the cursor to initialize in the memory. 2. Open the cursor to allocate memory. 3. Fetch the cursor to retrieve data. 4. Close the cursor to release allocated memory. This example demonstrates the use of declare open fetch and close an explicit cursor. The table given below is used to understand the outcome. Execute the following program to retrieve the employee name and address.