Basic SQL for Testers : UDF & Triggers
1. User Defined Functions
There are two types of User-Defined functions in SQL Server and they are Scalar, and Table-Valued.
1.1 Scalar Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
1.2 Table Value Function
An Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
A Trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action on the table that they are assigned to.
Basically, triggers are classified into two main types:-
2.1 After Triggers
These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:
- AFTER INSERT Trigger
- AFTER UPDATE Trigger
- AFTER DELETE Trigger
2.2 Instead of Trigger
These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted(unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:
- INSTEAD OF INSERT Trigger
- INSTEAD OF UPDATE Trigger
- INSTEAD OF DELETE Trigger
3. Difference between SP & UDF
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2. Function can be called from SQL statements where as procedure can not be called from the SQL statements.
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. Stored Procedure supports deferred name resolution. Example while writing a stored procedure that uses table named table1 and table2 etc..but actually not exists in database is allowed only in during creation but run time throws error Function wont support deferred name resolution.
5. Functions doesn’t support Transaction while Stored Procedure does.
6. Stored procedures can call/invoke a Function or another Stored proc but Function can only call another function(s).