First we look at TRIGGER objects, which in itself is a very large topic, particularly because there are three different types of triggers that you can work with, but equally because they are quite a bit more complex than STORED PROCEDURE objects. You use them to react to some event, either a DML operation such as an INSERT, UPDATE or DELETE statement execution; someone changing a setting or object on the server; or even someone logging into a server.

Then we cover the details of User-Defined Functions (or UDFs) that allow you to create code that is called in the same way that a system function might. UDFs are very powerful tools, but are also very dangerous for performance when used poorly.

In this section, we focus on DML triggers, because they are the ones that are generally used for business requirements. In the next section we review the other types of triggers, which are mostly focused on administration needs. DML TRIGGER objects are schema owned, database contained objects, like STORED PROCEDURE, VIEW, and CONSTRAINT objects, so their names must not collide with other objects in the database.

DML TRIGGER objects are typically used for a couple of purposes that are hard to do in the declarative configuration of tables. The logic often could be placed in a STORED PROCEDURE object, but by using a TRIGGER object, you can limit duplication of code:

Complex data integrity CHECK constraints can only see data in the same row. If you need to check data across multiple rows, only triggers can do this automatically.

Running code in response to some action For example, if an order comes in past a threshold (like a $3,000,000 order for lattes from Fourth Coffee), you could write a row to a table to have the row checked for validity.

Ensuring columnar data is modified If you want to make sure that data is modified, like a column that tells you when a row was last modified, triggers can ensure that the user does not put in invalid data.

Making a view editable If a VIEW references more than one table, it becomes complicated to modify it using simple DML operations.

There are two different types of DML Trigger objects that work for INSERT, UPDATE, and DELETE operations that you should familiarize yourself with:

AFTER These triggers perform after a DML operation. They are typically used for doing data validations, as you can see the data as it is after the operation has occurred.

INSTEAD OF These triggers perform instead of the DML operation, so if you want the operation to occur, you need repeat the DML in the code.

The following subsections contain an example of each of the scenarios that are listed, along with some commentary on the major pitfalls you encounter. This is not by any means an exhaustive list of ways that triggers can be used, but a simple overview of how they are be created to implement given needs.

While this covers a great percentage of needs, if you need to check data across a group of rows, triggers can do this (technically CHECK constraints can use a USER DEFINED FUNCTION, but TRIGGER objects are considered the best method). For this example, consider you have the following table (just including columns that we need for the example):

Click here to view code image

CREATE TABLE Examples.AccountContact
(
     AccountContactId int NOT NULL CONSTRAINT PKAccountContact PRIMARY KEY,
     AccountId        char(4) NOT NULL,
     PrimaryContactFlag bit NOT NULL
);

You are given the business requirement to ensure there is always one primary contact for an account, if a contact does exist. A first step is to identify the query that shows you rows that do not match this rule. In this case:

Click here to view code image

SELECT AccountId, SUM(CASE WHEN PrimaryContactFlag = 1 THEN 1 ELSE 0 END)
FROM   Examples.AccountContact
GROUP BY AccountId
HAVING SUM(CASE WHEN PrimaryContactFlag = 1 THEN 1 ELSE 0 END) <> 1;

If that query returns data, then you know something is wrong. This query is the basis of the data check. We can put this into a trigger as seen in Listing 2-9, which checks on any INSERT or UPDATE operation.

LISTING 2-9 Trigger to stop multiple PrimaryContacts for an Account during an UPDATE or INSERT operation

Click here to view code image

---

CREATE TRIGGER Examples.AccountContact_TriggerAfterInsertUpdate
ON Examples.AccountContact
AFTER INSERT, UPDATE AS
BEGIN
  SET NOCOUNT ON;
  SET ROWCOUNT 0; --in case the client has modified the rowcount
  BEGIN TRY
  --check to see if data is returned by the query from previously
  IF EXISTS ( SELECT AccountId
              FROM   Examples.AccountContact
                     --correlates the changed rows in inserted to the other rows
                     --for the account, so we can check if the rows have changed
              WHERE  EXISTS (SELECT *
                             FROM   inserted
                             WHERE  inserted.AccountId =
                                                 AccountContact.AccountId

                             UNION ALL
                             SELECT *
                             FROM   deleted
                             WHERE  deleted.AccountId =
                                                 AccountContact.AccountId)
              GROUP BY AccountId
              HAVING SUM(CASE WHEN PrimaryContactFlag = 1 then 1 ELSE 0 END) <> 1)

          THROW  50000, 'Account(s) do not have only one primary contact.', 1;
   END TRY
   BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        THROW;
     END CATCH
END;

---

For the most part, this is pretty straightforward. We give the TRIGGER a name, tell it the TABLE it is for, and then that this fires AFTER an INSERT or UPDATE operation. Then it is just like a stored procedure for the most part. There are two virtual tables inserted and deleted that instantiated when the trigger performs. Inserted shows you how the data looks after the operation, and deleted shows you how the data looks before the operation. Both tables only have data during an UPDATE operation, showing you the before and the after versions. In some cases, both tables are empty, such as an UPDATE statement where the WHERE clause matched no rows, or a MERGE statement where one of the operations had no matches.

The most important part of writing such a trigger is that you must be prepared for more than one row to be modified. In the EXISTS block, we have the query we started from, and added the WHERE EXISTS condition to limit the scope of the query to just rows that have been modified based on their AccountId. You have to use both inserted and deleted table rows because there is nothing stopping the AccountId from changing:

Click here to view code image

SELECT AccountId
FROM   Examples.AccountContact
--correlates the changed rows in inserted to the other rows
--for the account, so we can check if the rows have changed
-------------------------------------------------------------
WHERE  EXISTS (SELECT *
               FROM   inserted
               WHERE  inserted.AccountId = AccountContact.AccountId
               UNION ALL
               SELECT *
               FROM   deleted
               WHERE  deleted.AccountId = AccountContact.AccountId)
-------------------------------------------------------------
GROUP BY AccountId
HAVING SUM(CASE WHEN PrimaryContactFlag = 1 then 1 ELSE 0 END;

Many trigger writers make the mistake of writing variable declaration statements to grab values from the inserted/deleted virtual table like the following:

Click here to view code image

SELECT @AccountId = AccountId FROM inserted;

Using that AccountId to check for issues misses all but the one row. You must, though, test all of the cases with single and multiple rows. For this trigger, consider running at least the following simple tests:

Click here to view code image

--Success, 1 row
INSERT INTO Examples.AccountContact(AccountContactId, AccountId, PrimaryContactFlag)
VALUES (1,1,1);
--Success, two rows
INSERT INTO Examples.AccountContact(AccountContactId, AccountId, PrimaryContactFlag)
VALUES (2,2,1),(3,3,1);
--Two rows, same account
INSERT INTO Examples.AccountContact(AccountContactId, AccountId, PrimaryContactFlag)
VALUES (4,4,1),(5,4,0);
--Invalid, two accounts with primary
INSERT INTO Examples.AccountContact(AccountContactId, AccountId, PrimaryContactFlag)
VALUES (6,5,1),(7,5,1);

This returns:

Click here to view code image

Msg 50000, Level 16, State 1, Procedure AccountContact_TriggerAfterInsert, Line 29
One or more Accounts does not have one and only one primary contact.

Then, without showing messages:

Click here to view code image

--Invalid, no primary
INSERT INTO Examples.AccountContact(AccountContactId, AccountId, PrimaryContactFlag)
VALUES (8,6,0),(9,6,0);
--Won't work, because AccountId is new, and this row is not primary
UPDATE Examples.AccountContact
SET    AccountId = 6
WHERE  AccountContactId = 5;

Triggers can be tricky to get right, and logically tracing through the code and testing is important. On the exam, if presented with a trigger, it is important to be very careful to be able to trace through the single-row and multi-row operations that are needed.

The requirements for our problem stated that we are to make sure every account has a primary contact, if a contact exists. As it stands now, while the user can’t create or modify rows to violate the requirement, a user can delete the primary row. So, creating a DELETE TRIGGER works very similar to the INSERT/UPDATE one, except now you use the deleted virtual table, as you can see in Listing 2-10.

LISTING 2-10 Trigger to stop multiple PrimaryContacts for an Account during a DELETE operation

Click here to view code image

---

CREATE TRIGGER Examples.AccountContact_TriggerAfterDelete
ON Examples.AccountContact
AFTER DELETE AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
   IF EXISTS ( SELECT AccountId
                FROM   Examples.AccountContact
                WHERE  EXISTS (SELECT *
                               FROM   deleted
                               WHERE  deleted.AccountId =
                                          AccountContact.AccountId)
             GROUP BY AccountId
             HAVING SUM(CASE WHEN PrimaryContactFlag = 1 then 1 ELSE 0 END) > 1)
       THROW  50000, 'One or more Accounts did not have one primary contact.', 1;
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW;
    END CATCH;
END;

---

The basic structure of the TRIGGER for data integrity is to see if there is an issue, either by checking data in the inserted/deleted tables, the primary table, or any tables, and if there is an issue, run a THROW statement and let the ROLLBACK TRANSACTION undo the statement’s effect and any other activity done within the context of the transaction. If the caller has a TRY...CATCH block, they get the error captured. If not, the batch ends due to the THROW statement. If you use RAISERROR, things are trickier because the batch continues after the transaction ends.

Click here to view code image

CREATE TABLE Examples.Promise
(
    PromiseId int NOT NULL CONSTRAINT PKPromise PRIMARY KEY,
    PromiseAmount money NOT NULL
);

No matter the charity, there are a few levels of promises that can be received. For simplicity, let’s define two: Normal and Extranormal. A Normal promise is in a typical range that a person promises if they are normal and sincere. Extranormal promises are outside of the Normal and need verification. Extranormal promises for this scenario are those over $10,000.00. The requirements are to create a log of promises to verify when rows are created or updated.

So, you design a table that has the Promise rows to be verified, which looks like this (without the details of the row being verified):

Click here to view code image

CREATE TABLE Examples.VerifyPromise
(
    VerifyPromiseId int NOT NULL CONSTRAINT PKVerifyPromise PRIMARY KEY,
    PromiseId int NOT NULL CONSTRAINT AKVerifyPromise UNIQUE
                  --FK not included for simplicity
);

In Listing 2-11 the TRIGGER object fulfills this requirement.

LISTING 2-11 Trigger to create rows in another table

Click here to view code image

---

CREATE TRIGGER Examples.Promise_TriggerInsertUpdate
ON Examples.Promise
AFTER INSERT, UPDATE AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
       INSERT INTO Examples.VerifyPromise(PromiseId)
       SELECT PromiseId
       FROM   inserted
       WHERE  PromiseAmount > 10000.00
         AND  NOT EXISTS (SELECT * --keep from inserting duplicates
                          FROM   VerifyPromise
                          WHERE  VerifyPromise.PromiseId = inserted.PromiseId)
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW; --will halt the batch or be caught by the caller's catch block
   END CATCH
END;

---

The biggest thing to note in this TRIGGER is how you need to do very little error handling. Just use the TRY...CATCH block to see if there is an error with the statement, and if so, run the ROLLBACK TRANSACTION statement that rethrows the error message.

Consider the following TABLE:

Click here to view code image

CREATE TABLE Examples.Lamp
(
    LampId       int IDENTITY(1,1) CONSTRAINT PKLamp PRIMARY KEY,
    Value          varchar(10) NOT NULL,
    RowCreatedTime datetime2(0) NOT NULL
        CONSTRAINT DFLTLamp_RowCreatedTime DEFAULT(SYSDATETIME()),
    RowLastModifiedTime datetime2(0) NOT NULL
        CONSTRAINT DFLTLamp_RowLastModifiedTime DEFAULT(SYSDATETIME())
);

While we specified a DEFAULT constraint, the user can put anything at all in the table. Instead, let’s use two TRIGGER objects. The first is an INSTEAD OF INSERT TRIGGER object as seen in Listing 2-12.

LISTING 2-12 INSTEAD OF TRIGGER to automatically set RowCreated and RowLastModified time columns

Click here to view code image

---

CREATE TRIGGER Examples.Lamp_TriggerInsteadOfInsert
ON Examples.Lamp
INSTEAD OF INSERT AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
        --skip columns to automatically set
        INSERT INTO Examples.Lamp( Value)
        SELECT Value
        FROM   inserted
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW; --will halt the batch or be caught by the caller's catch block
   END CATCH
END;

---

This trigger is very similar to the one in previous sections. The biggest difference is that the INSERT statement is doing the operation that the user expected it was doing, but skipping the columns that use a defaulted value. You can use this step in the process to do any formatting that you don’t want the user to have control over. After inserting a row and viewing it, you see the following:

Click here to view code image

INSERT INTO Examples.Lamp(Value, RowCreatedTime, RowLastModifiedTime)
VALUES ('Original','1900-01-01','1900-01-01');

SELECT *
FROM   Examples.Lamp;

Here are the two columns:

Click here to view code image

LampId      Value      RowCreatedTime              RowLastModifiedTime
----------- ---------- --------------------------- ---------------------------
1           Original   2016-09-20 21:03:54         2016-09-20 21:03:54

Next, create the INSTEAD OF UPDATE TRIGGER that makes sure that the RowLastModifiedTime is modified, and the RowCreatedTime is never modified.

Click here to view code image

CREATE TRIGGER Examples.Lamp_TriggerInsteadOfUpdate
ON Examples.Lamp
INSTEAD OF UPDATE AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
        UPDATE Lamp
        SET    Value = inserted.Value,
               RowLastModifiedTime = DEFAULT --use default constraint
        FROM   Examples.Lamp
                 JOIN inserted
                    ON Lamp.LampId = inserted.LampId;
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW; --will halt the batch or be caught by the caller's catch block
   END CATCH;
END;

This is similar to the INSERT trigger, but we do the UPDATE instead of the INSERT. This time, skip the RowCreatedTime column because the time the row was created doesn’t change, but the modified time does. Update and view the row previously created:

Click here to view code image

UPDATE Examples.Lamp
SET    Value = 'Modified',
       RowCreatedTime = '1900-01-01',
       RowLastModifiedTime = '1900-01-01'
WHERE LampId = 1;

SELECT *
FROM   Examples.Lamp;

The RowLastModifiedTime is different than the first call, and different now from the RowCreatedTime:

Click here to view code image

LampId      Value      RowCreatedTime              RowLastModifiedTime
----------- ---------- --------------------------- ---------------------------
1           Modified   2016-09-20 21:07:07         2016-09-20 21:10:26

For example, create the following TABLE and VIEW objects as seen in Listing 2-13.

LISTING 2-13 Setting up a scenario for demonstrating using TRIGGER to make any view editable

Click here to view code image

---

CREATE TABLE Examples.KeyTable1
(
    KeyValue int NOT NULL CONSTRAINT PKKeyTable1 PRIMARY KEY,
    Value1   varchar(10) NULL
);
CREATE TABLE Examples.KeyTable2
(
    KeyValue int NOT NULL CONSTRAINT PKKeyTable2 PRIMARY KEY,
    Value2    varchar(10) NULL
);
GO
CREATE VIEW Examples.KeyTable
AS
    SELECT COALESCE(KeyTable1.KeyValue, KeyTable2.KeyValue) as KeyValue,
           KeyTable1.Value1, KeyTable2.Value2
    FROM   Examples.KeyTable1
             FULL OUTER JOIN Examples.KeyTable2
                ON KeyTable1.KeyValue = KeyTable2.KeyValue;

---

Note that in the view, there is no way that you can insert data using this view, because the actual KeyValue columns are not exposed in the view, so the following attempt is to insert into the table:

Click here to view code image

INSERT INTO Examples.KeyTable (KeyValue, Value1, Value2)
VALUES (1,'Value1','Value2');

This gives you the following error:

Click here to view code image

Msg 4406, Level 16, State 1, Line 21
Update or insert of view or function 'Examples.KeyTable' failed because it contains
a derived or constant field.

Next, add an INSTEAD OF INSERT TRIGGER to the table. In the TRIGGER, we get the inserted and deleted virtual tables that are the shape of the VIEW objects structure, which we will use to do the INSERT operations as seen in Listing 2-14.

LISTING 2-14 INSTEAD OF TRIGGER to make view editable

Click here to view code image

---

CREATE TRIGGER Examples.KeyTable_InsteadOfInsertTrigger
ON Examples.KeyTable
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET ROWCOUNT 0; --in case the client has modified the rowcount
    BEGIN TRY
          --Insert data into one of the tables
        INSERT INTO Examples.KeyTable1(KeyValue, Value1)
        SELECT KeyValue, Value1
        FROM   Inserted;
        --and then the other
        INSERT INTO Examples.KeyTable2(KeyValue, Value2)
        SELECT KeyValue, Value2
        FROM   Inserted;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        THROW; --will halt the batch or be caught by the caller's catch block
    END CATCH;
END;

---

Now, if you try to insert into the view, using the same statement as before:

Click here to view code image

INSERT INTO Examples.KeyTable (KeyValue, Value1, Value2)
VALUES (1,'Value1','Value2');

It will succeed. And to view the data:

SELECT *
FROM   Examples.KeyTable;

It looks just like any other table:

Click here to view code image

KeyValue    Value1     Value2
----------- ---------- ----------
1           Value1     Value2

This is a very simple version of what this TRIGGER may need to be for a production worthy version. When you have more than one table to be concerned with, there is a question of what happens if one row already exists, and another doesn’t. If you build the UPDATE trigger, an UPDATE can either be an UPDATE or an INSERT for one of the tables. But you should make sure that one of the table’s rows exists.

In this section, we review the other two types of TRIGGER objects:

DDL triggers Used to react to DDL operations at the server or database level. For example, you can capture the DDL of every CREATE TABLE and ALTER TABLE statement and log the results in a table, or even stop them from occurring.

Logon triggers Used to react to someone logging into the server. For example, you could state that login LOGIN1 (also referred to as a server principal) could not log in from 8PM – 7AM. These triggers just stop the login action, so if the user is already connected during this time period, it does not end their connection.

In the next two sections, we cover examples of DDL triggers at the server, and then the database level. There is not that much difference to the syntax, but there are a few differences to understand about how they work, and where they reside.

Create the following TABLE object that contains the time, statement, and the login name of the user that made the change to the database.

Click here to view code image

USE ExamBook762Ch2;
GO
CREATE TABLE Examples.DDLDatabaseChangeLog
(
   DDLDatabaseChangeLogId int NOT NULL IDENTITY
        CONSTRAINT PKDDLDatabaseChangeLog PRIMARY KEY,
    LogTime datetime2(0) NOT NULL,
    DDLStatement nvarchar(max) NOT NULL,
    LoginName sysname NOT NULL
);

Next, since the log table is in a different database, but the trigger is not scoped to that database, we need to create security principals. Security is not on the exam, but to make the example somewhat realistic, this is needed because security chaining does not pertain to this non-schema owned object. Instead use the EXECUTE AS clause on the CREATE TRIGGER statement to dictate security principals. We start by creating a server principal, a user in the ExamBook762Ch2 database.

Click here to view code image

--Names used to make it clear where you have used examples from this book outside
--of primary database
CREATE LOGIN Exam762Examples_DDLTriggerLogging WITH PASSWORD = 'PASSWORD$1';
CREATE USER Exam762Examples_DDLTriggerLogging
                                 FOR LOGIN Exam762Examples_DDLTriggerLogging;
GRANT INSERT ON  Examples.DDLDatabaseChangeLog TO
                            Exam762Examples_DDLTriggerLogging;

We use just three events to cover the database events listed in our requirements, but there are many more. For a full list, check this link to DDL Events on Technet: https://technet.microsoft.com/en-us/library/bb522542.aspx.

The trigger itself is fairly simple. The interesting part is the EVENTDATA() function. It returns an XML string value that contains information about the DDL operation that caused the trigger to fire. Unlike a DML TRIGGER object, a DDL TRIGGER fires once per statement. We use just a single value from the statement, the CommandText value. Note that the DDL TRIGGER object is not a schema-owned object.

Click here to view code image

CREATE TRIGGER DatabaseCreations_ServerDDLTrigger
ON ALL SERVER
WITH EXECUTE AS 'Exam762Examples_DDLTriggerLogging'
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
    SET NOCOUNT ON;
    --trigger is stored in master db, so must
    INSERT INTO ExamBook762Ch2.Examples.DDLDatabaseChangeLog(LogTime, DDLStatement,
                                                                          LoginName)
    SELECT SYSDATETIME(),EVENTDATA().value(
                     '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
           ORIGINAL_LOGIN(); --Original login gives you the user that is connected.
                             --Otherwise we would get the EXECUTE AS user.

To test this trigger, create a LOGIN and give it rights to create and alter a database, assuming your server allows SQL Standard logins. If not, the same information is captured if you use any login.

Click here to view code image

CREATE LOGIN Exam762Examples_DatabaseCreator WITH PASSWORD = 'PASSWORD$1';
GRANT CREATE ANY DATABASE TO Exam762Examples_DatabaseCreator;
GRANT ALTER ANY DATABASE TO Exam762Examples_DatabaseCreator;

Now, login as Exam762Examples_DatabaseCreator, and run the following set of batches:

Click here to view code image

CREATE DATABASE Example
GO
ALTER DATABASE Example SET RECOVERY SIMPLE;
GO
DROP DATABASE Example;

And the database owner that you have been using:

Click here to view code image

SELECT LogTime, DDLStatement, LoginName
FROM Examples.DDLDatabaseChangeLog;

You receive a log of changes:

Click here to view code image

LogTime                     DDLStatement                                 LoginName
--------------------------- -------------------------------------------- -------------
2016-09-21 16:55:09         CREATE DATABASE Example                      Exam762Examp...
2016-09-21 16:55:19         ALTER DATABASE Example SET RECOVERY SIMPLE   Exam762Examp...
2016-09-21 16:55:27         DROP DATABASE Example                        Exam762Examp...

While mostly an administration function, this provides functionality that can be very useful. Something you can do in a DDL TRIGGER is ROLLBACK to disallow an action (no need for special security here):

Click here to view code image

CREATE TRIGGER DatabaseCreations_StopThemAll
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
    SET NOCOUNT ON;
    ROLLBACK TRANSACTION;
    THROW 50000,'No more databases created please',1;

Now, everyone (even system administrators), is disallowed to change a database. Something is commonly done with DDL Triggers of this type is to disable them. You can disable a TRIGGER using the DISABLE TRIGGER statement:

Click here to view code image

DISABLE TRIGGER DatabaseCreations_StopThemAll ON ALL SERVER;

It is a very good idea to clean up your objects unless you want to keep them, as they span outside of the single database:

Click here to view code image

DROP TRIGGER DatabaseCreations_ServerDDLTrigger ON ALL SERVER;
DROP USER Exam762Examples_DDLTriggerLogging;
DROP LOGIN Exam762Examples_DDLTriggerLogging;
DROP LOGIN Exam762Examples_DatabaseCreator;

We start with a table that is very much the same as the one for database changes:

Click here to view code image

CREATE TABLE Examples.DDLChangeLog
(
   DDLChangeLogId int NOT NULL IDENTITY
        CONSTRAINT PKDDLChangeLog PRIMARY KEY,
    LogTime datetime2(0) NOT NULL,
    DDLStatement nvarchar(max) NOT NULL,
    LoginName sysname NOT NULL
);

We again need to configure some security so the user can perform an INSERT statement into the table, but this time strictly in the context of the database:

Click here to view code image

CREATE USER Exam762Examples_DDLTriggerLogging WITHOUT LOGIN;
GRANT INSERT ON Examples.DDLChangeLog TO Exam762Examples_DDLTriggerLogging;

Now we create the DDL TRIGGER on the database scope. Just like the server version, this is not a schema-scoped object. In this version of the trigger we are going to save off the DDL into a variable, do the ROLLBACK TRANSACTION, and then log the change (note that if the DDL statement is in an external transaction, the change is still logged because of the ROLLBACK TRANSACTION).

Click here to view code image

CREATE TRIGGER DatabaseChanges_DDLTrigger
ON DATABASE
WITH EXECUTE AS 'Exam762Examples_DDLTriggerLogging'
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
    SET NOCOUNT ON;
    DECLARE @eventdata XML = EVENTDATA();
    ROLLBACK; --Make sure the event doesn't occur
    INSERT INTO Examples.DDLChangeLog(LogTime, DDLStatement, LoginName)
    SELECT SYSDATETIME(),
           @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                                                      'nvarchar(max)'),
           ORIGINAL_LOGIN();
    THROW 50000,'Denied!',1;

Now, when any user in the database (even a system administrator) tries to CREATE, ALTER, or DROP a TABLE object:

Click here to view code image

CREATE TABLE Examples.Test
(
    TestId int NOT NULL
);
GO
DROP TABLE Examples.DDLChangeLog;

You receive the following error message (or in this case, you would get two of the same error message):

Click here to view code image

Msg 50000, Level 16, State 1, Procedure DatabaseChanges_DDLTrigger, Line 25
Denied!

Then, viewing the data in the log table:

Click here to view code image

SELECT LogTime, DDLStatement, LoginName
FROM   Examples.DDLChangeLog;

You see the following statements were attempted:

Click here to view code image

LogTime                     DDLStatement                         LoginName
--------------------------- ------------------------------------ -----------------------
2016-09-21 19:16:06         CREATE TABLE Examples.Test           DomainNamelouis
                            (
                                 TestId int NOT NULL
                            )
2016-09-21 19:16:51         DROP TABLE Examples.DDLChangeLog;    DomainNamelouis

Again, clean up your code or future examples do not work:

Click here to view code image

DROP TRIGGER DatabaseChanges_DDLTrigger ON DATABASE;
DROP USER Exam762Examples_DDLTriggerLogging;

For this example, create the following table, which can capture the name of the login, the time of login, and the application that the login comes from:

Click here to view code image

CREATE TABLE Examples.LoginLog
(
    LoginLogId  int NOT NULL IDENTITY(1,1)
          CONSTRAINT PKLoginLog PRIMARY KEY,
    LoginName   sysname NOT NULL,
    LoginTime   datetime2(0) NOT NULL ,
    ApplicationName sysname NOT NULL
);

Similar to the DDL trigger, there is very little to the LOGIN TRIGGER. The trigger fires once per logon operation, and there is no data it provides by a function or virtual table. All data you need comes from system functions. In order to log on to a table, just like the DDL triggers, we need to provide security information, as this is a non-schema bound object with no database context.

Click here to view code image

CREATE LOGIN Exam762Examples_LogonTriggerLogging WITH PASSWORD = 'PASSWORD$1';
CREATE USER Exam762Examples_LogonTriggerLogging
                                 FOR LOGIN Exam762Examples_LogonTriggerLogging;
GRANT INSERT ON Examples.LoginLog TO Exam762Examples_LogonTriggerLogging;

Now create the trigger. It uses the ORIGINAL_LOGIN() function to get the security context from the principal that connected, as the EXECUTE AS clause changes the context inside the trigger. Then, if the user is not Login_NotAllowed, it logs the data:

Click here to view code image

CREATE TRIGGER Exam762ExampleLogonTrigger
ON ALL SERVER
WITH EXECUTE AS 'Exam762Examples_LogonTriggerLogging'
FOR LOGON
AS
    IF ORIGINAL_LOGIN() = 'Login_NotAllowed'
        THROW 50000,'Unauthorized Access',1;
    ELSE
        INSERT INTO ExamBook762Ch2.Examples.LoginLog(LoginName, LoginTime,
                                                     ApplicationName)
        VALUES (ORIGINAL_LOGIN(),SYSDATETIME(),APP_NAME());

To test the LOGIN TRIGGER, create the LOGIN:

Click here to view code image

CREATE LOGIN Login_NotAllowed WITH PASS

WORD = 'PASSWORD$1';

Try to log in (see Figure 2-2), using any tool, such as Management Studio.

After clicking connect, you see the dialog in Figure 2-3.

To be sure that your LOGON TRIGGER actually works before disconnecting all of your connections, connect to a new window using your typical security rights. Don’t be terribly surprised when you find this log has more rows than you initially expect:

Click here to view code image

   LoginName                 LoginTime            ApplicationName
------------------------ -------------------- ---------------------------------------
WIN-8F59BO5AP7Dlouis    2016-09-21 21:26:50  Microsoft SQL Se..IntelliSense
WIN-8F59BO5AP7Dlouis    2016-09-21 21:26:50  Microsoft SQL Se..IntelliSense
WIN-8F59BO5AP7Dlouis    2016-09-21 21:27:06  Microsoft SQL Se..Query
WIN-8F59BO5AP7Dlouis    2016-09-21 21:27:06  Microsoft SQL Se..Query

As with the previous examples, be sure to clean this up if you are doing this on your server, because if the ExamBook762Ch2 database is later dropped with this trigger enabled, no one will be able to logon to the server:

Click here to view code image

DROP TRIGGER Exam762ExampleLogonTrigger ON ALL SERVER;
DROP USER Exam762Examples_LogonTriggerLogging;
DROP LOGIN Exam762Examples_LogonTriggerLogging;

The first example, uses an AFTER TRIGGER object. Start with the following table:

Click here to view code image

CREATE TABLE Examples.UpdateRows
(
    UpdateRowsId int NOT NULL IDENTITY(1,1)
        CONSTRAINT PKUpdateRows PRIMARY KEY,
    Value varchar(20) NOT NULL
);
INSERT INTO Examples.UpdateRows (Value)
VALUES ('Original'),('Original'),('Original');

This has the following data:

Click here to view code image

UpdateRowsId Value
------------ --------------------
1            Original
2            Original
3            Original

And the AFTER UPDATE TRIGGER object in Listing 2-15 is added to the table.

LISTING 2-15 Trigger for the AFTER TRIGGER example

Click here to view code image

---

CREATE TRIGGER Examples.UpdateRows_TriggerInsert
ON Examples.UpdateRows
AFTER UPDATE AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0;
   BEGIN TRY
        DECLARE @UpdateRowsId int
        SELECT @UpdateRowsId = UpdateRowsId
        FROM   inserted
        ORDER BY UpdateRowsId;

        UPDATE Examples.UpdateRows
        SET    Value = UPPER(Value)
        WHERE  UpdateRowsId = @UpdateRowsId;
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW; --will halt the batch or be caught by the caller's catch block
   END CATCH;

END;

---

Now, a user runs the following UPDATE statement:

Click here to view code image

UPDATE Examples.UpdateRows
SET    Value = 'Modified';

And receives the following output:

(3 row(s) affected)

What are the contents of the table? Either:

Click here to view code image

UpdateRowsId Value
------------ --------------------
1            Modified
2            Modified
3            MODIFIED

UpdateRowsId Value
------------ --------------------
1            Original
2            Original
3            Original
UpdateRowsId Value
------------ --------------------
1            MODIFIED
2            MODIFIED
3            MODIFIED

UpdateRowsId Value
------------ --------------------
1            MODIFIED
2            Modified
3            Modified

Or can you actually tell?

In this case, the fourth set of outputs match the table contents. Because of the way the TRIGGER is coded, only a single row is modified. So, it would be the first or the last set of results, or how you can’t tell. You are guaranteed to get the first row in the set because of the ORDER BY clause on this statement (and it is necessary for you to realize that ORDER BY would order in ascending order by default):

Click here to view code image

SELECT @UpdateRowsId = UpdateRowsId
FROM   inserted
ORDER BY UpdateRowsId;

Without the ORDER BY clause, the order is not guaranteed, so you might get a different result (even though the fourth result would still be extremely likely).

This second example uses an INSTEAD OF TRIGGER object. Previous examples of triggers have made sure that the primary key column value was not changeable by using a column with the IDENTITY property or that it did not matter. The table we use is very simple, and the PRIMARY KEY constraint is on a column that can be changed:

Click here to view code image

CREATE TABLE Examples.KeyModify
(
    KeyModifyId  int CONSTRAINT PKKeyModify PRIMARY KEY,
    Value       varchar(20)
);
INSERT INTO Examples.KeyModify(KeyModifyId, Value)
VALUES (1,'Original'), (2,'Original'),(3,'Original');

Now, the trigger in Listing 2-16 is added to the table.

LISTING 2-16 Trigger for the INSTEAD OF TRIGGER example

Click here to view code image

---

CREATE TRIGGER Examples.KeyModify_TriggerInsteadOfInsert
ON Examples.KeyModify
INSTEAD OF UPDATE AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0;
   BEGIN TRY
        UPDATE Examples.KeyModify
        SET    Value = UPPER(inserted.Value)
        FROM   Examples.KeyModify
                 JOIN inserted
                    ON KeyModify.KeyModifyId = inserted.KeyModifyId
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW;
   END CATCH
END;

---

Now, a user runs the following statement:

Click here to view code image

UPDATE Examples.KeyModify
SET     KeyModifyId = KeyModifyId + 10, --Change Primary Key Value
        Value = 'Modified';

After performing this statement, the user gets the following message:

(3 row(s) affected)

Did anything change in the table? Are the KeyModifyId column values 11, 12, and 13? Does the Value column say Original, ORIGINAL, Modified, or MODIFIED? The key to the answer is the JOIN on the inserted virtual table. The inserted virtual table looks like:

Click here to view code image

KeyModifyId Value
----------- --------------------
11          Modified
12          Modified
13          Modified

This is just what your statement told it to do. However, the TABLE still has the following row values, since this is an INSTEAD OF TRIGGER object:

Click here to view code image

KeyModifyId Value
----------- --------------------
1           Original
2           Original
3           Original

At the point of the UPDATE. When you try to join these two sets together, zero rows match, so the final table looks like:

Click here to view code image

KeyModifyId Value
----------- --------------------
1           Original
2           Original
3           Original

The message from the UPDATE statement execution: (3 row(s) affected) is actually returned even if you leave off the UPDATE altogether. So nothing is changed. If it seems tricky, perhaps it is. However, they are real tables, and the code does work without error. You just have to work through the solution, regardless of whether it is realistic or not.

Scalar Used to create code that returns a single value of a data type (more than just integers like STORED PROCEDURES could).

Table To the user, it appears to be essentially a view that allows you to predefine parameters that can be used to filter or alter the output.

Each function has different uses, so let’s review them independently.

Click here to view code image

CREATE FUNCTION Examples.ReturnIntValue
(
    @Value  int
)
RETURNS int
AS
  BEGIN
    RETURN @Value
  END;

This can be called in a very similar manner to any system function we have used previously, for example:

Click here to view code image

SELECT Functions.ReturnIntValue(1) as IntValue;

You can access data in the function code, and they are very similar in structure to STORED PROCEDURE objects. The code in the FUNCTION has two primary limitations that make them complex to use.

First, there is no error handling that you can provide. There are runtime errors, such as those that stop a query from returning rows to occur that can’t be caught during the compile process. One such example is the divide-by-zero errors. Perform the following query in the WideWorldImporters database:

Click here to view code image

SELECT OrderId, 1/ (4732-OrderId)
FROM   Sales.Orders;

You then see multiple rows returned and a divide-by-zero error. The same sort of issues occur with a scalar UDF, in that the errors come as data is being returned, not like you saw when creating STORED PROCEDURE objects. You cannot perform a THROW or RAISERROR statement to cause an error message to occur. Any error handling that you implement needs to be an understanding with the user of the function of the illegal value, like a negative, or NULL value.

Second, you may not make any side effects from the function. So you have no INSERT, UPDATE, or SELECT statements that modify tables other than a table variable (Of the form: DECLARE @table table(column datatype), which we use later in the table valued function code. They are available in scalar UDFs, but arern’t typical, nor do any use of system functions change data.

Note that we did not use SET NOCOUNT ON, because that is considered a side-effecting function, even if it is simply for the scope of the object. BEGIN and END are required around the body of the code, and you must have a RETURN statement that returns a value of the data type that matches the RETURNS clause.

It is allowable to access a table in your scalar functions. For example, still in the WideWorldImporters database, if you have a business requirement to implement a scalar UDF, the user can give them the number of orders for a Customer. Optionally, for a specific OrderDate, you can write the function shown in Listing 2-17.

LISTING 2-17 Scalar function that accesses a table

Click here to view code image

---

CREATE FUNCTION Sales.Customers_ReturnOrderCount
(
    @CustomerID int,
    @OrderDate date = NULL
)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT, --if all parameters NULL, return NULL immediately
     SCHEMABINDING --make certain that the tables/columns referenced cannot change
AS
  BEGIN
      DECLARE @OutputValue int

      SELECT  @OutputValue = COUNT(*)
      FROM    Sales.Orders
      WHERE   CustomerID = @CustomerID
        AND   (OrderDate = @OrderDate
               OR @OrderDate IS NULL);

      RETURN @OutputValue
   END;

---

Using parameters of a FUNCTION object differs from using a STORED PROCEDURE, in that you can’t use named parameters, and you can’t skip parameters that have defaults. For example, to use this function you might code the following:

Click here to view code image

SELECT Sales.Customers_ReturnOrderCount(905, '2013-01-01');

This tells you that this customer has two orders for that day. To use the default parameter, you need to use the DEFAULT keyword:

Click here to view code image

SELECT Sales.Customers_ReturnOrderCount(905, DEFAULT);

While this can be quite useful, using functions in a query tends to cost more to perform over more direct manners. Consider the following two queries:

Click here to view code image

SELECT CustomerID, Sales.Customers_ReturnOrderCount(905, DEFAULT)
FROM   Sales.Customers;

SELECT CustomerID, COUNT(*)
FROM   Sales.Orders
GROUP  BY CustomerID;

If you compare the plans, the first plan (which looks very complex graphically) is 3 percent of the cost of the two queries. The second query’s plan is very simple-looking, but at 97 percent of the cost, it seems the hands down winner is the FUNCTION. But using SET STATISTICS ON you discover why.

Query using the FUNCTION:

Click here to view code image

Table 'Worktable'. Scan count 0, logical reads 0, physical reads
Table 'Customers'. Scan count 1, logical reads 4, physical reads
 SQL Server Execution Times:
    CPU time = 375 ms,  elapsed time = 439 ms.

Query using the SELECT with the GROUP BY:

Click here to view code image

Table 'Orders'. Scan count 1, logical reads 191, physical reads 0
SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 34 ms.

The GROUP BY query looks far worse, but performs over 10 times faster. However, we know that the function is accessing the Orders table, and that information is missing. The same is true in the plan. The code in the scalar UDF is not represented in a straightforward manner in the plan either.

The most common use case for scalar UDFs is to format some data in a common manner. For example, say you have a business need to format a value, such as the CustomerPurchaseOrderNumber in the Sales.Orders table in WideWorldImporters in a given way, and in multiple locations. In this case we just right pad the data to eight characters, and prepend ‘CPO’ to the number. For this you can write an expression in the SELECT clause:

Click here to view code image

SELECT N'CPO' + RIGHT(N'00000000' + CustomerPurchaseOrderNumber,8)
FROM WideWorldImporters.Sales.Orders;

Now, if you need to use this in multiple places, you can fold that expression into a scalar USER DEFINED FUNCTION object, like so:

Click here to view code image

CREATE FUNCTION Sales.Orders_ReturnFormattedCPO
(
    @CustomerPurchaseOrderNumber nvarchar(20)
)
RETURNS nvarchar(20)
WITH RETURNS NULL ON NULL INPUT,
     SCHEMABINDING
AS
 BEGIN
    RETURN (N'CPO' + RIGHT(N'00000000' + @CustomerPurchaseOrderNumber,8));
 END;

Now you can write:

Click here to view code image

SELECT Sales.Orders_ReturnFormattedCPO('12345') as CustomerPurchaseOrderNumber;

This then returns:

CustomerPurchaseOrderNumber
---------------------------
CPO00012345

Note that this, too, has performance implications that are not quite as obvious as the function that accesses a table. First, you never want to use this to format a column in a WHERE clause:

Click here to view code image

SELECT OrderId
FROM   Sales.Orders
WHERE  Sales.Orders_ReturnFormattedCPO(CustomerPurchaseOrderNumber) = 'CPO00019998';

In the best case, this scans an index that contains CustomerPurchaseOrder, but in the worst case it scans the entire base table structure. Note that this is true of any system function as well, so it is not really just a general rule of thumb that any column values that are formatted in any clause other than the SELECT clause may be cause for concern with performance.

However, even in the SELECT clause, there is some overhead with using a scalar UDF:

Click here to view code image

SET STATISTICS TIME ON;
SELECT Sales.Orders_ReturnFormattedCPO(CustomerPurchaseOrderNumber)
FROM   Sales.Orders;

SELECT N'CPO' + RIGHT(N'00000000' + [CustomerPurchaseOrderNumber],8)
FROM WideWorldImporters.Sales.Orders;

In this test, the function version took 188 ms of CPU time, and the expression only 15ms. So whether or not it is worth it to use a scalar UDF is a personal preference. So, an exam question about scalar functions can be about what you include in the function, or it can ask you to predict the better-performing statement and/or why it might be the case.

Simple Consisting of a single Transact-SQL query, simple table-valued UDFs work very much like a VIEW.

Multi-Statement Consists of as many statements as you need, allowing you to build a set of data using the same logic as you had in scalar UDFs, but returning a table instead of a scalar variable.

For these examples, use the same requirements used in our scalar example, returning the number of sales for a given customer, and optionally on a given day. In addition, add a requirement to determine if they have any backorders on that day.

Starting with the simple table-valued UDF, the basics of the object is, just like a VIEW, a single SELECT query. As such, there is not a performance penalty in using a table-valued USER DEFINED FUNCTION versus a VIEW, depending on how you use it (which can also be said about how VIEW objects are used.):

Click here to view code image

CREATE FUNCTION Sales.Customers_ReturnOrderCountSetSimple
(
    @CustomerID int,
    @OrderDate date = NULL
)
RETURNS TABLE
AS
RETURN (SELECT COUNT(*) AS SalesCount,
               CASE WHEN MAX(BackorderOrderId) IS NOT NULL
                          THEN 1 ElSE 0 END AS HasBackorderFlag
        FROM   Sales.Orders
        WHERE  CustomerID = @CustomerID
        AND   (OrderDate = @OrderDate
               OR @OrderDate IS NULL));

The syntax is pretty self-explanatory, you just declare that you are returning a table, and in the RETURN clause (no BEGIN and END), you put the query with the parameters used as you desire. Usage is much like a view, only you have parameters you need to include:

Click here to view code image

SELECT *
FROM   Sales.Customers_ReturnOrderCountSetSimple(905,'2013-01-01');

This returns the following set:

Click here to view code image

SalesCount  HasBackorderFlag
----------- ----------------
2           1

And to default a parameter, you use the DEFAULT keyword as before:

Click here to view code image

SELECT *
FROM   Sales.Customers_ReturnOrderCountSetSimple(905,DEFAULT);

This returns:

Click here to view code image

SalesCount  HasBackorderFlag
----------- ----------------
125         1

Now you can use it in a query to get both calculated values by joining using the OUTER APPLY join operator, which applies column values from the left input as parameters into the right (you can also use literals):

Click here to view code image

SELECT CustomerId, FirstDaySales.SalesCount, FirstDaySales.HasBackorderFlag
FROM   Sales.Customers
        OUTER APPLY Sales.Customers_ReturnOrderCountSetSimple
                            (CustomerId, AcountOpenedDate) as FirstDaySales
WHERE  FirstDaySales.SalesCount > 0;

There are two APPLY operator versions. OUTER APPLY returns every row from the left input, while CROSS APPLY only returns rows where there is a match in the right input. Performing this query returns the following abridged output:

Click here to view code image

CustomerId  SalesCount  HasBackorderFlag
----------- ----------- ----------------
10          2           1
57          1           0
...         ...         ...
995         2           1
1000        2           1

For a multi-statement table-valued UDF, the syntax is quite different. You define the output specifically by declaring a table variable, and then by loading it. The following code in Listing 2-18 returns the exact same base query used in the simple version of the function.

LISTING 2-18 Multi-statement table-valued function that accesses a table

Click here to view code image

---

CREATE FUNCTION Sales.Customers_ReturnOrderCountSetMulti
(
    @CustomerID int,
    @OrderDate date = NULL
)
RETURNS  @OutputValue TABLE (SalesCount int NOT NULL,
                             HasBackorderFlag bit NOT NULL)
AS
 BEGIN
    INSERT INTO @OutputValue (SalesCount, HasBackorderFlag)
    SELECT COUNT(*) as SalesCount,
                   CASE WHEN MAX(BackorderOrderId) IS NOT NULL
                               THEN 1 ElSE 0 END AS HasBackorderFlag
    FROM   Sales.Orders
    WHERE  CustomerID = @CustomerID
    AND   (OrderDate = @OrderDate
            OR @OrderDate IS NULL)

    RETURN;
END;

---

Multi-statement table-valued UDFs are always slower than equivalent simple ones. If you compare the plan and STATISTICS TIME output of the two queries, you see very similar issues with multi-statement table-valued UDFs, as there was with scalar UDFs that accessed tables. Compare the following two calls

Click here to view code image

SET STATISTICS TIME ON;
SELECT CustomerId, FirstDaySales.SalesCount, FirstDaySales.HasBackorderFlag
FROM   Sales.Customers
        OUTER APPLY Sales.Customers_ReturnOrderCountSetSimple
                        (CustomerId, AccountOpenedDate) as FirstDaySales
WHERE  FirstDaySales.SalesCount > 0;

SELECT CustomerId, FirstDaySales.SalesCount, FirstDaySales.HasBackorderFlag
FROM   Sales.Customers
        OUTER APPLY Sales.Customers_ReturnOrderCountSetMulti
                        (CustomerId, AccountOpenedDate) as FirstDaySales
WHERE  FirstDaySales.SalesCount > 0;

Note that the first plan that uses the simple form, is considered 89 percent of the cost. Yet when you look at the execution time, it takes twice as long. This is because the simple form is optimized like a VIEW object, incorporating the DDL of the object into the query plan, but the multi-statement form hides the costs of the coded object.

In the system functions, some examples of deterministic functions are ABS, which returns the absolute value of a number, and YEAR, which returns the year from a date value. Functions that are not deterministic include SYSDATETIME(), which returns the current date and time, and NEWID(), which returns a new GUID value. For more information about deterministic and non-deterministic functions, the following MSDN article provides more details and functions https://msdn.microsoft.com/en-us/library/ms178091.aspx. The basic criteria is that the USER DEFINED FUNCTION is declared as WITH SCHEMABINDING, accesses no external data, and uses no non-deterministic system functions.

One place this is important is when you are using a value in an index, either in a VIEW object or computed column in a TABLE object. So, when building functions, it is generally important to make your function deterministic. For example, consider the requirement you might have to build a function that proper cases a value by making the first letter in every word uppercase. Listing 2-19 includes a version of a function that does this.

LISTING 2-19 Slightly complex scalar function to demonstrate determinism

Click here to view code image

---

CREATE FUNCTION Examples.UpperCaseFirstLetter
(
    @Value varchar(50)
)
RETURNS nvarchar(50)
WITH SCHEMABINDING
AS
BEGIN
   --start at position 2, as 1 will always be uppercase if it exists
   DECLARE @OutputValue nvarchar(50), @position int = 2, @previousPosition int
   IF LEN(@Value) = 0 RETURN @OutputValue;
                      --remove leading spaces, uppercase the first character
   SET @OutputValue = (LTRIM(CONCAT(UPPER(SUBSTRING(@Value,1,1)),
                                           LOWER(SUBSTRING(@Value,2,99)))));
   --if no space characters, exit
   IF CHARINDEX(' ',@OutputValue,1) = 0 RETURN @OutputValue;
   WHILE 1=1
    BEGIN
       SET @position = CHARINDEX(' ',@outputValue,@position) + 1
       IF @position < @previousPosition or @position = 0
           BREAK;
       SELECT @OutputValue = CONCAT(SUBSTRING(@OutputValue,1,@position - 1),
                                   UPPER(SUBSTRING(@OutputValue,@position,1)),
                                   SUBSTRING(@OutputValue,@position + 1,50)),
             @PreviousPosition = @Position
    END
    RETURN @OutputValue
END;

---

You can run it as:

Click here to view code image

SELECT Examples.UpperCaseOnlyFirstLetter(N'NO MORE YELLING') as Name;

This returns:

Click here to view code image

Name
--------------------------------------------------
No More Yelling

To determine if the FUNCTION is deterministic, use the OBJECTPROPERTY() function:

Click here to view code image

SELECT OBJECTPROPERTY(OBJECT_ID('Examples.UpperCaseFirstLetter'), 'IsDeterministic')
                                                                      AS IsDeterministic

No matter how complex it appears, since we did not use external data or non-deterministic system functions, and used WITH SCHEMABINDING, we discover it is deterministic:

IsDeterministic
----------------
1

For a non-deterministic example, consider the following function that gives you the start of the current month. It does this by using the SYSDATETIME() system function, which is non-deterministic:

Click here to view code image

CREATE FUNCTION Examples.StartOfCurrentMonth
()
RETURNS date
WITH SCHEMABINDING
AS
 BEGIN
    RETURN (DATEADD(day, 0, DATEDIFF(day, 0, SYSDATETIME() ) -
                                        DATEPART(DAY,SYSDATETIME()) + 1));
 END;

And, we test if it is deterministic:

Click here to view code image

SELECT OBJECTPROPERTY(OBJECT_ID('Examples.StartOfCurrentMonth'), 'IsDeterministic')
                                                                  AS IsDeterministic

As expected, this function is not deterministic. While it might seem the case, determinism is not limited to scalar UDFs. Table-valued functions can be deterministic as well. Consider the following multi-statement table-valued UDF. It declares a table, loads it from a simple row constructor of 10 values, and returns the following:

Click here to view code image

CREATE FUNCTION Examples.ReturnOneToTenSet
()
RETURNS @OutputTable TABLE (I int)
WITH SCHEMABINDING
AS
  BEGIN
    INSERT INTO @OutputTable(I)
    VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

    RETURN;
  END;

Checking this function, you see that it is deterministic :

Click here to view code image

SELECT OBJECTPROPERTY(OBJECT_ID('Examples.ReturnOneToTenSet'), 'IsDeterministic')
                                                                   AS IsDeterministic;

PRIMARY KEY Used to specify the primary uniqueness criteria for a table.

UNIQUE Used to enforce any additional uniqueness criteria other than the PRIMARY KEY constraint

FOREIGN KEY Enforces relationships between tables, making sure references exist. Usually references the PRIMARY KEY constraint, but can reference a UNIQUE constraint as well.

CHECK Allows you to declaratively specify Boolean predicates that must not be FALSE.

DEFAULT Guides the user’s input when there isn’t necessary a simple choice for a value.

NULL values are complicated with constraints. In UNIQUE constraints, they are treated as unique values. In CHECK constraints, they always pass the test unless explicitly tested for. For FOREIGN KEY constraints, they are always allowed, even if it is only one NULL column value in a composite key.

There are two main ways to pick which columns to place a PRIMARY KEY constraint. Using a natural key, or a value from the logical group of attributes is one way. A very typical implementation is to use a surrogate key, usually some artificial value like an auto-generated value.

STORED PROCEDURE objects are modules that allow you to create custom code that is performed together. A query plan is saved off with the stored procedure that is parameterized much easier than an ad-hoc batch of Transact-SQL.

Using STORED PROCEDURE objects for building a coded interface to Transact-SQL objects allows programmers to do simple tasks in a manner similar to procedural programming languages.

Use table-valued parameters to send a STORED PROCEDURE object many rows at a time, allowing you to create complex objects in single STORED PROCEDURE calls.

For error handling, using the TRY...CATCH construct allows you to capture errors thrown by Transact-SQL statements. You can use THROW and RAISERROR to throw your own error messages. Unhandled THROW statements stop the batch from running, RAISERROR does not.

TRIGGER objects can be used to react to different actions on the server. There are three kinds of triggers:

DML Used to enhance data integrity with access to more data than CHECK constraints, cascade modifications with more control than FOREIGN KEY constraints, and manipulate the data that is being inserted and updated into a table. There are INSTEAD OF triggers where you have to redo the action, and AFTER triggers that fire after the operation.

DDL Used to capture and react to server or database level DDL statements.

Logon Used to take some action when a server principal accesses the server.

USER DEFINED FUNCTION objects allow you to build modules that are used in other Transact-SQL statement in the same way a table or a system function is. There are two kinds: table-valued and scalar.

Users provide an email address when they sign up. However, sometimes the same person creates multiple accounts with the same email address, causing issues with the validity of a research results set.

You have a stored procedure that needs to run three INSERT statements, the first two of which should all complete or all not complete. The fifth should run no matter what, succeeding/failing independently. How would you code this?

In the RecipientType column in the Recipient table, there have been values entered like “Dunno” and “Whatever,” which are not valid types of recipients. How can you make sure that the column does not include values that it should not?

You need to make sure that a column that contains an offer code is always five characters long and uppercase. What tool (or tools) will you use to make sure that the string value is all uppercase letters, and how?

You have given users rights to add indexes to a certain database, but you want to make sure that no indexes are added from 8:00AM to 10:00AM.

You are building a complex stored procedure that can take 10-20 seconds for each execution, and much longer if the @checkAll parameter has a NULL value, a value it should never have.

For this need, you want to use a UNIQUE constraint on an EmailAddress column of the table where you define a participant for the survey questions. For example, the partial table was originally created:

Click here to view code image

CREATE TABLE Examples.Respondent
(

     RespondentId int NOT NULL CONSTRAINT PKRespondent PRIMARY KEY,
     EmailAddress  nvarchar(500) NOT NULL
);

Adding the following constraint prevents the issue with duplicated data:

Click here to view code image

ALTER TABLE Examples.Respondent
     ADD CONSTRAINT AKRespondent UNIQUE (EmailAddress);

You have a stored procedure that needs to run three INSERT statements, the first two of which should all complete or all not complete. The third INSERT should run no matter what, succeeding/failing independently. How do you code this?

Say you have the following simple table:

Click here to view code image

CREATE TABLE Examples.ThreeInsert
(
       ThreeInsertId int CONSTRAINT PKThreeInsert PRIMARY KEY
);

You can code something like the following. In code destined for production work, you likely want to code nested TRY...CATCH blocks, and save the error messages from the first two INSERT statements in variables to throw at the end.

Click here to view code image

CREATE PROCEDURE Examples.ThreeInsert_Create
            @SecondValue int = 2 --Pass in 1 to and no data is inserted

AS
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT INTO Examples.ThreeInsert (ThreeInsertId)
        VALUES (1);
       INSERT INTO Examples.ThreeInsert (ThreeInsertId)
       VALUES (@SecondValue);
       COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        --No THROW will mean no reporting of message
    END CATCH;

    INSERT INTO Examples.ThreeInsert (ThreeInsertId)
    VALUES (3);

In the RecipientType column in the Recipient table, there have been values entered like “Dunno” and “Whatever,” which are not valid types of recipients. How can you make sure that the column does not include values that it should not?

This was a bit of a tricky question, but the true answer to the question as written is that you can’t defend against an unknown entry, without a specific domain of legal values. It would be possible to create a CHECK constraint that has a predicate of (RecipientType NOT IN (‘Dunno’,’Whatever’)) and you have stopped two values, but not all of them.

After defining the legal values, say: ‘Regular,’ ‘Special Handling;’ you can then handle this in one of two ways. Consider the following partial table:

Click here to view code image

CREATE TABLE Examples.Recipient
(
    RecipientType varchar(30) NOT NULL
);

You can add a CHECK constraint such as:

Click here to view code image

ALTER TABLE Examples.Recipient
   ADD CONSTRAINT CHKRecipient_RecipientType
         CHECK (RecipientType IN ('Regular','Special Handling'));

An alternate solution is to use a domain TABLE object with a FOREIGN KEY constraint, such as:

Click here to view code image

CREATE TABLE Examples.RecipientType
(
       RecipientType varchar(30) NOT NULL CONSTRAINT PKRecipientType PRIMARY KEY
);

INSERT INTO Examples.RecipientType(RecipientType)
VALUES ('Regular'),('Special Handling');

ALTER TABLE Examples.Recipient
      ADD CONSTRAINT FKRecipient_Ref_ExamplesRecipientType
      FOREIGN KEY (RecipientType) REFERENCES Examples.
RecipientType(RecipientType);

You need to make sure that the offer code column is always uppercase, what tool (or tools) would you use to make sure that the string value is all uppercase letters, and how?

For this question, there are two answers that would equally achieve the goal of making sure the string value is all uppercase letters. For example, consider the following table:

Click here to view code image

CREATE TABLE Examples.Offer
(
    OfferCode char(5) NOT NULL
);

Consider using an INSTEAD OF TRIGGER object. When you are doing the INSERT and UPDATE operations, you force the value to be uppercase; after making sure all of the characters are letters. The following is the INSERT trigger.

Click here to view code image

CREATE TRIGGER Examples.Offer_TriggerInsteadOfInsert
ON Examples.Offer
INSTEAD OF INSERT AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0; --in case the client has modified the rowcount
   BEGIN TRY
        IF EXISTS (SELECT *
                   FROM   inserted
                   WHERE  OfferCode NOT LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]')
            THROW 50000,'An OfferCode is not all alpha characters',1;

        --skip columns to automatically set
        INSERT INTO Examples.Offer (OfferCode)
        SELECT UPPER(OfferCode)
        FROM   inserted
   END TRY
   BEGIN CATCH
       IF XACT_STATE() <> 0
           ROLLBACK TRANSACTION;
       THROW; --will halt the batch or be caught by the caller's catch block
   END CATCH
END;

Another method is actually to use a CHECK constraint. The requirement is to make sure the string is all uppercase coming from the user. You can do this by using an expression with a case sensitive or binary collation. To determine the collation of your database, you can check sys.databases:

Click here to view code image

SELECT collation_name
FROM sys.databases
WHERE  database_id = DB_ID();

This returns:

collation_name
----------------------------
Latin1_General_100_CI_AS

Change it to CS for the case sensitive version of Latin1_General_100 and use this in the CHECK constraint:

Click here to view code image

ALTER TABLE Examples.Offer
    ADD CONSTRAINT CHKOffer_OfferCode
       CHECK (OfferCode LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]'
                             COLLATE Latin1_General_100_CS_AS);

You have given user rights to add indexes to a certain database, but you want to make sure that no indexes are added from 8:00AM to 10:00AM.

Use a DDL Trigger, such as the following. Casting SYSDATETIME() as time gives us the time of day:

Click here to view code image

CREATE TRIGGER DatabaseChanges_DDLTrigger
ON DATABASE
WITH EXECUTE AS 'Exam762Examples_DDLTriggerLogging'
FOR CREATE_INDEX
AS
    SET NOCOUNT ON;
    IF CAST(SYSDATETIME() AS time) >= '08:00:00'
        AND CAST(SYSDATETIME() AS time) < '10:00:00'

    THROW 50000,'No indexes may be added between 8 and 10 AM',1;

You are building a complex stored procedure that can take 10-20 seconds for each execution, and much longer if the @checkAll parameter has a NULL value, a value it should never have.

For this, in the body of your STORED PROCEDURE, you would include a check for the parameter:

Click here to view code image

IF @checkAll IS NULL
      THROW 50000,'The value of @checkAll may not be NULL',1;