PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 73

The code looks similar, but the function is called more like a function call
in other programming languages. You are probably still asking yourself,
“What's the difference?” Well, in addition to calling a function and putting
its return into a variable, you can call UDFs inline with other code.
Consider the following example of a UDF that returns a new employee ID.
This function is being called inline with the insert statement for the em-
ployee table. Calling UDFs in this way prevents you from writing extra
code to store a return variable for later use.
INSERT INTO employee (employeeid, firstname, lastname)
VALUES ( dbo.GetNewEmployeeID() , 'Eric', 'Johnson')
The next big difference in UDFs is the type of data they return. UDFs
that can return single values are known as scalar functions. The data the
function returns can be defined as any data type except for text, ntext,
image, and timestamp. To this point, all the examples we have looked at
have been scalar values.
UDFs can also be defined as table-valued functions: functions that
return a table data type. Again, table-valued functions can be called inline
with other T-SQL code and can be treated just like tables. Using the fol-
lowing code, we can pass the employee ID into the function and treat the
return as a table.
SELECT * FROM dbo.EmployeeData(8765448)
You can also use table-valued functions in joins with other functions or
with base tables. UDFs are used primarily by developers who write T-SQL
code against your database, but you can use UDFs to implement business
rules in your model. UDFs also can be used in check constraints or trig-
gers to help you maintain data integrity.
Triggers
Triggers and constraints are the two most common ways to enforce data in-
tegrity and business rules in your physical database. Triggers are stored
T-SQL scripts, similar to stored procedures, that run when a DML state-
ment (other than SELECT) is issued against a table or view. There are two
types of DML triggers available in SQL Server.
With an AFTER trigger, which can exist only on tables, the DML
statement is processed, and after that operation completes, the trigger