C H A P T E R  15

Triggers

Now that you have become quite proficient in using SQL Server 2012, you ought to know about one last aspect of it. Triggers are that one last step, and this chapter is the missing link in the foundation of your knowledge and skill set.

At times a modification to data somewhere within your database will require an automatic action on data elsewhere, either in your database, another database, or elsewhere within SQL Server; a trigger is the object that will do this for you. When a modification to your data occurs, SQL Server will fire a trigger, which is a specialized stored procedure that will run, performing the actions that you desire. Triggers are similar to constraints but more powerful, and they require more system overhead, which can lead to a reduction in performance. Triggers are most commonly used to perform business rules validation, carry out cascading data modifications (changes on one table causing changes to be made on other tables), and keep track of changes for each record (audit trail). You can also do any other processing that you require when data on a specific table are modified, such as auditing. You actually have come across triggers when looking at Declarative Management Framework earlier in the book in Chapter 3. These specialized system triggers are built to ensure the system’s integrity. You will see how these work by building your own DDL trigger later in this chapter.

The aim of this chapter is as follows:

  • Describe what a trigger is
  • Detail potential problems surrounding triggers
  • Show the CREATE TRIGGER T-SQL syntax
  • Discuss when to use a constraint and when to use a trigger
  • Show the system tables and functions specific to triggers
  • Demonstrate the creation of a trigger through a template and straight T-SQL statements
  • Discuss image data types and the problems that surround updating these columns and firing a trigger

First of all, let’s see just what constitutes a trigger.

What Is a Trigger?

A trigger is a specialized stored procedure that will be executed either on a data modification, known as a Data Manipulation Language (DML) trigger, or on a data model action, such as CREATE TABLE, known as a Data Definition Language (DDL) trigger. DML triggers are pieces of code attached to a specific table that are set to automatically run in response to an INSERT, DELETE, or UPDATE command. However, a DDL trigger is attached to an action that occurs either within a database or within a server. The first part of the chapter will look at DML triggers, followed by an investigation of DDL triggers. As a developer, you will use DML triggers mainly, and you will find DDL triggers used mainly by database administrators to ensure that certain actions cannot happen or are reported.

image Note Unlike stored procedures, you cannot manually make a trigger run, you cannot use parameters with triggers, and you cannot use return values with triggers.

The DML Trigger

Triggers have many uses. Perhaps the most common for a DML trigger is to enforce a business rule. For example, when a customer places an order, a trigger would check that they have sufficient funds or that you have enough stock; if any of these checks fail, you can complete further actions or return error messages and roll back the update.

DML triggers can be used as a form of extra validation—for example, to perform complex checks on data that a constraint could not achieve. Keep in mind that using constraints instead of triggers gives you better performance, but triggers are the better choice when dealing with complex data validation. Another use for a DML trigger is to make changes in another table based on what is about to happen within the original triggered table. For example, when you add an order, you would create a DML trigger that would reduce the quantity of items in stock. Finally, DML triggers can be used to create an automated audit trail that generates a change history for each record. Since the Sarbanes-Oxley Act (SOX) of 2002, DML triggers for the purpose of logging changes over time are created more and more often.

You can create separate triggers for any table modification action, or triggers that will fire on any combination of table actions. This means that you cannot create a trigger for SELECT. Obviously, as no table modifications occur on a SELECT statement, it is impossible to create such a trigger. There are three main types of triggers:

  • INSERT trigger
  • DELETE trigger
  • UPDATE trigger

You can also have a combination of the three types of triggers, and you will find one trigger for each action and a trigger for multiple actions within database developments.

Triggers can update tables within other databases if desired, and it is also possible for triggers to span servers as well, so don’t think the scope of triggers is limited to the current database or server.

It is possible for a trigger to fire a data modification, which in turn will execute another trigger, which is known as a nested trigger. For example, imagine you have Table A, which has a trigger on it to fire a modification within Table B, which in turn has a trigger on it that fires a modification within Table C. If a modification is made to Table A, then Table A’s trigger will fire, modifying the data in Table B, which will fire the trigger in Table B, thus modifying data in Table C. This nesting of triggers can go up to 32 triggers deep before you reach the limit set within SQL Server; however, if you start getting close to that sort of level, either you have a very complex system, or perhaps you have been overly zealous with your creation of triggers!

It is possible to switch off any nesting of triggers so that when one trigger fires, no other trigger can fire; however, this is not usually the norm. Be aware that your performance will suffer greatly when you start using nested triggers; use them only when necessary.

image Note There is one statement that will stop a DELETE trigger from firing. If you issue a TRUNCATE TABLE T-SQL command, it is as if the table had been wiped without any data logging. A truncate logs only the pages of data and not each row. This also means that a DELETE trigger will not fire, as it is not a deletion per se that is happening.

As with stored procedures, do take care when building triggers: you don’t want to create a potentially endless loop in which a trigger causes an update, which fires a trigger already fired earlier within the loop, thereby repeating the process.

CREATE TRIGGER Syntax for DML Triggers

The creation of a trigger through T-SQL code can be quite complex if you use the full trigger syntax. However, the reduced version that I cover here is much more manageable and easier to demonstrate. When building a trigger, you can create it for a single action or for multiple actions. To expand on this, a trigger can be for the insertion of a record only, or it can cover inserting and updating the record. Any combination is possible.

image Note Although this chapter will demonstrate DML triggers on tables, a trigger can also be placed on a view as well, so that when data is modified through a view, it too can fire a trigger if required. Any underlying triggers will also fire, of course.

Here is the syntax for creating a basic trigger:

CREATE TRIGGER [schema_name.]trigger_name
ON {table|view}
[WITH ENCRYPTION]
{
{{FOR {AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
[{IF [UPDATE (column)
[{AND|OR} UPDATE (column)]] ]
COLUMNS_UPDATE()]
sql_statements}}

Let’s explore the options in this syntax more closely:

  • CREATE TRIGGER schema_name.trigger_name: As ever, you need to inform SQL Server what you are attempting to do, and in this instance, you want to create a trigger. The name for the trigger must also follow the SQL Server standards for naming objects within a database, and a trigger should also belong to a schema just like other objects.
  • ON {table|view}: It is then necessary to give the name of the single table or view that the trigger relates to, which is named after the ON keyword. Each trigger is attached to one table only.
  • [WITH ENCRYPTION]: As with views and stored procedures, you can encrypt the trigger using the WITH ENCRYPTION options so that the code cannot be viewed by prying eyes.
  • {FOR|AFTER|INSTEAD OF}:
    • The FOR|AFTER trigger will run the code within the trigger after the underlying data is modified. Therefore, if you have any constraints on the table for cascading changes, then the table changes and these cascade changes will complete successfully before the trigger fires. You specify either FOR or AFTER.
    • INSTEAD OF: This is the most complex of the three options to understand as a trigger defined with this option will run the T-SQL within the trigger rather than allow the data modification to run. This includes any cascading. To clarify, if you have an INSTEAD OF trigger that will execute on a data INSERT, then the insertion will not take place.
  • {[INSERT] [,] [UPDATE] [,] [DELETE]}: This section of the syntax determines on what action(s) the trigger will execute. This can be an INSERT, an UPDATE, or a DELETE T-SQL command. As mentioned earlier, the trigger can fire on one, two, or three of these commands, depending on what you want the trigger to do. Therefore, at this point, you need to mention which combination of commands, separated by a comma, you want to work with.
  • AS: The keyword AS defines that the trigger code has commenced, just as the AS keyword defines the start of a stored procedure. After all, a trigger is just a specialized stored procedure.
  • [{IF UPDATE (column) [{AND|OR} UPDATE (column)]]: This option is a test to check whether a specific column has been modified. This happens through the use of the UPDATE() keyword. By placing the name of the column to test between the parentheses, a logical TRUE or FALSE will be returned depending on whether the column has been updated. The deletion of a record will not set the UPDATE test to TRUE or FALSE, as you are removing an item and not updating it. An INSERT or an UPDATE will set the UPDATE test to the necessary value. This function is demonstrated later in the chapter.
  • COLUMNS_UPDATE(): This has functionality similar to UPDATE(), but instead of testing a specific named column, it tests multiple columns in one test.
  • sql_statements: At this point, you code the trigger just like any other stored procedure.

The main thought you must keep in mind when building a trigger is that a trigger fires after each statement that modifies data is executed but before the modification is made to rows in the table. Therefore, if you have a statement that updates many rows, the trigger will fire after all the requested updates are done, not when each of the rows has been dealt with.

image Note Keep in mind that the FOR trigger executes before the underlying data is modified; therefore, a trigger can issue a ROLLBACK for that particular action if so desired.

Now that you know how to create a trigger, you will learn which situations they best apply to, as opposed to constraints.

Why Not Use a Constraint?

There is nothing stopping you from using a constraint to enforce a business rule, and in fact, constraints should be used to enforce data integrity. Constraints also give you better performance than triggers. However, they are limited in what they can achieve and what information is available to them to complete their job.

Triggers are more commonly used for the validation of business rules, or for more complex data validation, which may or may not then go on to complete further updates of data elsewhere within SQL Server.

A constraint is able to validate only data that are within the table the constraint is being built for or a specified value entered at design time. This is in contrast to a trigger, which can span databases, or even servers, and check against any data set at design time or a data set built from data collected from other actions against any table. This can happen if the necessary access rights are given to all objects involved.

However, constraints are the objects to use when you want to ensure that data forming a key are correct, or when you need to enforce referential integrity through a foreign key constraint.

At times a fine line will exist between building a constraint and a trigger, when the trigger is meant to perform a very simple validation task. In this case, if the decision deals with any form of data integrity, then use a constraint, which will give you better performance than using a trigger. If the object to be built is for business rules and requires complex validation, needs to handle multiple databases or servers, or requires advanced error handling, then build a trigger. For example, a trigger must be used if you need a change on one table to result in an action (update, delete, etc.) on a table that is located in another database. You might have this situation if you keep an audit trail (change history) database separate from your production database. It is doubtful that you would want to use a trigger if you are doing something simple, such as verifying that a date field contains only values within a certain range.

Deleted and Inserted Logical Tables

When a table is modified, whether this is by an insertion, an update, or deletion, any associated trigger to that action will run. The trigger will take an exact copy of the row of data that is held in two system logical tables called DELETED and INSERTED. When a record is inserted into a table within a database, a full copy of the insertion of the record is placed into the INSERTED table. Every item of information placed into each column for the insertion is then available for checking. If a deletion is performed, a full copy of the row of data is placed in the DELETED table. Finally, when an update occurs on a row of data, a full copy of the row before the modification is placed in the DELETED table, and then a copy of the row of data after the modification is placed in the INSERTED table.

The INSERTED and DELETED tables will hold one record from each table for each modification. Therefore, if you perform an UPDATE that updates 100 rows, the DELETED logical table is populated with the 100 rows prior to the UPDATE. The modification then takes place, and then the INSERTED table is populated with 100 rows. Finally, the trigger will fire. Once the trigger has completed, the data for that table are removed from the relevant logical tables.

These tables are held within the tempdb temporary database, and therefore triggers will affect the performance of the tempdb and will be affected by any other process utilizing tempdb. However, it is not possible to complete any further processing on these tables, such as creating an index, as they are held in a version store, and the data can be interrogated only via a SELECT statement and cannot be modified. You can access these tables only within a trigger to find out which records have been inserted, updated, or deleted.

image Note There are two version stores within each instance of SQL Server. This is an advanced topic, but in essence, one version store holds versions of each row of data where you have online index build operations on them, and the other index store is for rows of data where you don’t have online index build operations. These exist to reduce the amount of I/O on the transaction log.

To check what columns have been modified, it would be possible to compare each and every column value between the two tables to see what information had been altered. Luckily, as was discussed when you examined the syntax, there is a function, UPDATE(), that can test whether a column has been modified.

Now that you are fully up to date as to what a DML trigger is and how it works, it is time to create and test the first trigger within the database.

Creating a DML FOR/AFTER Trigger

The first trigger you will be looking at is a DML trigger to work with a customer record when a transaction occurs. The following example will demonstrate how to create a trigger on a data insertion, but also what happens to that INSERT when there is a problem in the trigger itself. As you are near the end of the book, the T-SQL within the trigger will be more advanced than some of the code so far.

TRY IT OUT: CREATING A TRIGGER IN QUERY EDITOR

The purpose of this example is to change a customer’s account balance when a financial transaction occurs as defined by an INSERT in the TransactionDetails.Transactions table. You want to change the balance AFTER the row has been inserted into the TransactionDetails.Transactions table. This is so you do not change the customer’s account balance if later in the INSERT of the row a problem occurs and the INSERT does not complete.

  1. First of all, it is necessary to give the trigger a meaningful name. Then you define the table that the trigger will be attached to, which in this case is the TransactionDetails.Transactions table. The final part of the start of the trigger will then define the type of trigger and on what actions the trigger will execute. This will be a FOR AFTER trigger on an INSERT on the TransactionDetails.Transactions table. The first part of the code looks as follows:
    USE ApressFinancial
    GO
    CREATE TRIGGER TransactionDetails.trgInsTransactions
    ON TransactionDetails.Transactions
    AFTER INSERT
    AS
    BEGIN
  2. It is now time to enter the remainder of the code for the trigger. You need to retrieve the Amount and TransactionType from the INSERTED table to be able to use these in the update of the CustomerDetails.Customers table. You can JOIN from the INSERTED table to the TransactionDetails.TransactionTypes table to find out whether you are dealing with a credit or a debit transaction. If it is a debit, then through the use of a subquery and a CASE statement you can alter the Amount by multiplying it by -1 so that you are reducing a customer’s balance. Notice the subquery includes a WHERE statement so that if you are entering a transaction type that does not affect the cash balance, such as recording a share movement, then the ClearedBalance will not be altered. The final action is to update the customer’s balance, which you will do via an UPDATE statement. There is a great deal to take in, so take time over the code. Also, the two examples of running this trigger should clear up any queries you might have.
    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance +
          (SELECT CASE WHEN CreditType = 0
                       THEN i.Amount * -1
                       ELSE i.Amount
                       END
             FROM INSERTED i
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = i.TransactionType
            WHERE AffectCashBalance = 1)
       FROM CustomerDetails.Customers c
       JOIN INSERTED i ON i.CustomerId = c.CustomerId
    END
  3. Execute the code to create the trigger in the database. You can test the trigger now by inserting a cash withdrawal or deposit relating to the two transaction types you currently have. You will list the customer balance before executing the INSERT into the TransactionDetails.Transactions table, and then you will add the row and look at the balance again to show that it has changed. Enter the following code, which inserts a withdrawal of $200 from CustomerId 1’s account:
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE customerId=2

    INSERT INTO TransactionDetails.Transactions (CustomerId,TransactionType,
    Amount,RelatedProductId, DateEntered)
    VALUES (2,4,200,1,GETDATE())

    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE customerId=2
  4. Execute the code. As you see in Figure 15-1, the results should show that the balance has dropped by $200 as expected. You could also double-check that the transaction exists in the TransactionDetails.Transactions table.
    images

    Figure 15-1. Balance reduction after trigger action

  5. The next test simulates a noncash transaction that has been recorded. For example, if you bought some shares, there would be the cash transaction removing the funds from your bank account, TransactionType=4, which was executed in the previous step, and then a second row entered on TransactionType=1, which is the equities product showing the addition of shares and is shown in the code next. This is a simple accounting procedure of one debit of cash and one credit of shares. I want to demonstrate, though, what happens when a trigger fails, and to achieve this, the CustomerDetails.Customers table needs to have its structure altered.
  6. Enter and execute the following code:
    UPDATE CustomerDetails.Customers
    SET ClearedBalance = 0
    WHERE ClearedBalance IS NULL
    GO
    ALTER TABLE CustomerDetails.Customers
    ALTER COLUMN ClearedBalance money NOT NULL
    GO
  7. Enter the following code, which will simulate a noncash transaction:
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE customerId=2

    INSERT INTO TransactionDetails.Transactions (CustomerId,TransactionType,
    Amount,RelatedProductId, DateEntered)
    VALUES (2,1,200,1,GETDATE())

    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE customerId=2
  8. Now execute the code. You will be presented with an error indicating that you cannot enter a NULL value in the ClearedBalance column. This has occurred because the CASE statement in the trigger will generate a NULL. Move to the Results tab, where only the opening balance is displayed, as shown in Figure 15-2. The trigger has a bug in that the subquery will attempt to update a balance even when there is a NULL cash value, and the trigger has not accounted for that scenario.
    (1 row(s) affected)
    Msg 515, Level 16, State 2, Procedure trgInsTransactions, Line 6
    Cannot insert the value NULL into column 'ClearedBalance',
    table 'ApressFinancial.CustomerDetails.Customers'; column does not
    allow nulls. UPDATE fails.
    The statement has been terminated.
    images

    Figure 15-2. Balance not updated

  9. To reiterate, the INSERT statement into the TransactionDetails.Transactions table is correct and would normally work. However, the trigger has a bug, so the transaction did not insert the data and was rolled back. You can see this by inspecting the TransactionDetails.Transactions table with the following code and the results shown in Figure 15-3.
    SELECT *
        FROM TransactionDetails.Transactions
      WHERE CustomerId=2
    ORDER BY DateEntered
    images

    Figure 15-3. Transaction table listing

  10. You can change a trigger using the ALTER TRIGGER command. The changes to the code occur in the subquery: you surround the single column you will have returned with an ISNULL() test. If the result is NULL, then you transpose this with the value of 0 as the cash balance is not to alter. The code you need to change is in bold.
    ALTER TRIGGER TransactionDetails.trgInsTransactions
    ON TransactionDetails.Transactions
    AFTER INSERT
    AS
    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance +
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN i.Amount * -1
                       ELSE i.Amount
                       END
             FROM INSERTED i
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = i.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN INSERTED i ON i.CustomerId = c.CustomerId
  11. Once the changes have been completed, you can then execute the code to alter the trigger. You can now rerun the test, which will add a row to the TransactionDetails.Transactions table without altering the balance. If you want, you could also list the TransactionDetails.Transactions table to prove that the INSERT succeeded this time, as Figure 15-4 demonstrates.
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE customerId=2

    INSERT INTO TransactionDetails.Transactions (CustomerId,TransactionType,
    Amount,RelatedProductId, DateEntered)
    VALUES (2,1,200,1,GETDATE())

    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE customerId=2
images

Figure 15-4. Transactions table with no balance change

Checking Specific Columns

It is possible to check whether a specific column or set of columns has been updated via the UPDATE() or COLUMNS_UPDATED() functions available within a trigger. This can reduce the amount of processing within the trigger and therefore speed up your batch and transactions. Keep in mind that a trigger should be as quick and as short as possible. Checking columns and performing specific T-SQL code only if a column is altered will reduce trigger overheads. As you will see, only when an amount or type of transaction has altered do you really need to perform an UPDATE on the CustomerDetails.Customers table.

The first statement you will look at is UPDATE().

Using UPDATE()

The UPDATE() function is a very simple yet powerful tool to a developer who is building a trigger. It is possible to check against a specific column, or a list of columns, to see whether a value has been inserted or updated within that column. It is not possible to check whether a value has been deleted from a column, because, quite simply, you cannot delete columns; you can delete only whole rows of data. If you want to check more than one column at the same time, place the columns one after another with either an AND or an OR depending on what you want to happen. Each individual UPDATE() will return TRUE if a value has been updated. If there are a number of columns, each column will have to be defined separately—for example:

IF UPDATE(column1) [AND|OR UPDATE(column2)]

You can use this function to deal with updates to the TransactionDetails.Transactions table. For example, there will be times that a transaction record has been incorrectly inserted. The trigger you created previously would have to be modified to deal with an UPDATE to alter the CustomerDetails.Customers ClearedBalance. The UPDATE would remove the value within the DELETED table and then apply the value within the INSERTED table. However, what if the alteration has nothing to do with any transaction that would alter the cash balance? For example, you were changing the date entered. By simply checking each column as necessary, it is possible to see whether an update is required to the CustomerDetails.Customers table. The two columns that are of interest within the trigger are Amount and TransactionType.

TRY IT OUT: UPDATE() FUNCTION

  1. Within Query Editor, let’s alter the trigger to deal with an UPDATE first of all before moving to the UPDATE() function. The first part of the alteration is to tag an UPDATE to the AFTER statement.
    ALTER TRIGGER TransactionDetails.trgInsTransactions
    ON TransactionDetails.Transactions
    AFTER INSERT,UPDATE
    AS
  2. Then you need to deal with the undoing of the amount in the DELETED table row from the CustomerDetails.Customers table. The actions on the ClearedBalance need to be the opposite of the addition.
    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance -
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN d.Amount * -1
                       ELSE d.Amount
                       END
             FROM DELETED d
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = d.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN DELETED d ON d.CustomerId = c.CustomerId
  3. The remainder of the trigger is the same. Once you have added in the following code, execute it so that the trigger is altered:
    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance +
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN i.Amount * -1
                       ELSE i.Amount
                       END
             FROM INSERTED i
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = i.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN INSERTED i ON i.CustomerId = c.CustomerId
  4. You can test that the trigger works by reducing the amount of a withdrawal—for me the TransactionId number is 1004—which currently sits at a value of $200. The following code will list the transactions for CustomerId=2 and the current cleared balance. You then alter the amount of withdrawal from $200 down to $100. The final actions will list the tables to prove the update to the TransactionDetails.Transactions and CustomerDetails.Customers tables has succeeded.
    SELECT *   FROM TransactionDetails.Transactions
     WHERE CustomerId = 2
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = 2
    UPDATE TransactionDetails.Transactions
       SET Amount = 100
     WHERE TransactionId = 1004
    SELECT *
      FROM TransactionDetails.Transactions
     WHERE CustomerId = 2
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = 2
  5. Once you execute the code, the transactions amount and cleared balances are altered, as shown in Figure 15-5. So now you know the trigger has worked and will do these actions no matter what happens to the TransactionDetails.Transactions table.
    images

    Figure 15-5. Transactions and balances

  6. You are now going to alter the trigger to test the Amount and TransactionType columns. If there is an update, you will complete the actions described previously; if not, then you will skip this processing. You will prove which path the trigger takes by using the system function RAISERROR, which you saw in the discussion of error handling in Chapter 13. Each section of the IF statement will have an appropriate RAISERROR.
  7. To alter the trigger to update only the CustomerDetails.Customers table if Amount or TransactionType is altered, you will need to add or alter the lines of code in bold here. If you execute this code, you will have a RAISERROR saying this is what you have done. Similarly, if you don’t update the table, you will have an appropriate but different RAISERROR. The trigger is defined in the following code with the alterations shown in bold. Once you have made the same changes, execute the code to alter the trigger.
    ALTER TRIGGER TransactionDetails.trgInsTransactions
    ON TransactionDetails.Transactions
    AFTER INSERT,UPDATE
    AS
    IF UPDATE(Amount) OR Update(TransactionType)
    BEGIN
    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance -
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN d.Amount * -1
                       ELSE d.Amount
                       END
             FROM DELETED d
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = d.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN DELETED d ON d.CustomerId = c.CustomerId

    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance +
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN i.Amount * -1
                       ELSE i.Amount
                       END
             FROM INSERTED i
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = i.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN INSERTED i ON i.CustomerId = c.CustomerId
    RAISERROR ('You have completed an update',10,1)
    END
    ELSE
    RAISERROR ('Updates have been skipped',10,1)
  8. You can now test out the example, which will not update the Amount or TransactionType but will alter the DateEntered.
    SELECT *
      FROM TransactionDetails.Transactions
     WHERE TransactionId=1004
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = 2
    UPDATE TransactionDetails.Transactions
       SET DateEntered = DATEADD(dd,-1,DateEntered)
     WHERE TransactionId = 1004
    SELECT *
      FROM TransactionDetails.Transactions
     WHERE TransactionId=1004
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = 2
  9. Once you have run this code, you will see the Results tab showing the DateEntered being altered but the ClearedBalance not, as Figure 15-6 illustrates. However, at this point, you don’t know if this is because you have removed and then readded the amount, giving a null effect.
    images

    Figure 15-6. Details where updates have been skipped

  10. Moving to the Messages tab, you can see the RAISERROR that occurred when you skipped updating the CustomerDetails.Customers table. There are also fewer “row(s) affected” messages.
    (1 row(s) affected)
    (1 row(s) affected)
    Updates have been skipped
    (1 row(s) affected)
    (1 row(s) affected)
    (1 row(s) affected)

This brings us to the end of looking at the UPDATE() function. Let’s move on to COLUMNS_UPDATED().

Using COLUMNS_UPDATED()

Instead of working with a named single column, the COLUMNS_UPDATED() function can work with multiple columns. It does this through the use of bit flags rather than naming columns, and therefore if you change the order of columns, you can get into trouble. There are eight bits in a byte, and a bit can be either off (a value of 0) or on (a value of 1).

COLUMNS_UPDATED() checks the bits of a single byte, which is provided by SQL Server, to see whether a column has been updated. It can do this by correlating a bit with a column in the underlying table. So to clarify, the TransactionDetails.Transactions table has nine columns. The first column, TransactionId, would relate to the first bit within the byte. The Amount column is the fifth column and therefore would relate to the fifth bit within the byte. If the first bit is on (a value of 1), the TransactionId column has been updated. Similarly, if the fourth bit is on, the Amount column has been updated.

image Note Confusingly, when talking about bits, the first bit is known as bit 0, the second bit is known as bit 1, and the byte is made up of bits 0 through 7. Therefore, the TransactionId column is bit 0, and the Amount column is bit 4. You will use this convention from this point onward.

The bit flag settings are based on the column order of the table definition. To test for a bit value, you use the ampersand (&) operator to test a specific bit or multiple set of bits. Before you discuss how this works, inspect Table 15-1. A bit value increases by the power of 2 as you progress down the bit settings, as you can see.

images images

image Note Another point about bits is that they work from right to left. For example, 00000010 shows bit 1 is set and therefore a value of 2.

Now if bits 2 and 4 are switched on within a byte—in other words, if they have a setting of true (00010100)—then the value is 4 + 16, which equates to 20. Therefore, to test whether the third and fifth columns of the table have both been altered, you would use the following syntax:

IF COLUMNS_UPDATE() & 20 = 20

This is a great deal to take in and understand, so I have included the following code to help you to understand this further. Here you have a byte data type variable. You then set the variable to a value; in this case, you believe that bits 0 and 1 will be set. By using the & operator, you can check this. To reiterate, slightly confusingly, it’s not the bit position you have to test, but the corresponding bit value, so bit 0 has a value of 1.

DECLARE @BitTest varbinary
SET @BitTest = 3
SELECT @BitTest & 1,@BitTest & 2,@BitTest & 4,@BitTest & 8,@BitTest & 16

As a byte contains eight bits, COLUMNS_UPDATED() can test only the first eight columns on this basis. Obviously, tables will contain more than eight columns, as you have seen with the TransactionDetails.Transaction table you have just been using.

Once a table has more than eight columns, things change. Instead of being able to test COLUMNS_UPDATED() & 20 > 0 to check whether columns 3 or 5 have updated, it is necessary to SUBSTRING() the value first. Therefore, to test columns 3 or 5, the code needs to read as follows:

IF (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20) > 0

However, even this is not the correct solution, although you are almost there. It is necessary to substring the COLUMNS_UPDATED() into eight-bit chunks for each set of eight columns. However, you need to involve the power() function to get the correct value to test for. The syntax for the power() section of the test is as follows:

power(2,(column_to_test – 1))

Therefore, if you want to test whether column 9 has been updated, the statement would be as follows, where you take the second set of eight columns using the SUBSTRING character 2, and then test the first column of the second set of eight—in other words, column 8 + 1 = 9.

IF (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))

The following tests columns 1, 4, and 10 to see whether any of them has changed:

IF (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(1-1))
OR SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(4-1))
OR SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(2-1)))

You can use this function to deal with updates to the TransactionDetails.Transactions table. For example, there will be times that a transaction record has been incorrectly inserted. The trigger you created previously would have to be modified to deal with an UPDATE that alters the customer’s ClearedBalance. The UPDATE would remove the value within the DELETED table and then apply the value within the INSERTED table. However, what if the alteration has nothing to do with any transaction that would alter the cash balance? For example, say you were changing the date entered. By simply checking each column as necessary, it is possible to see whether an update is required to the CustomerDetails.Customers table. The two columns that would interest the trigger are Amount and TransactionType.

TRY IT OUT: COLUMNS_UPDATED()

The code here takes the same example as UPDATE() and converts it to use COLUMNS_UPDATED(). It is a two-line change. The following test will see whether either the TransactionType or the Amount has altered by checking the two column settings using the power() function.

  1. Alter the trigger, and follow the previous example to ensure you get the same results. The changes are in bold. Execute the code when you have made the changes.
    ALTER TRIGGER TransactionDetails.trgInsTransactions
    ON TransactionDetails.Transactions
    AFTER UPDATE,INSERT
    AS
        IF (SUBSTRING(COLUMNS_UPDATED(),1,1) = power(2,(3-1))
        OR SUBSTRING(COLUMNS_UPDATED(),1,1) = power(2,(5-1)))
    BEGIN
    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance -
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN d.Amount * -1
                       ELSE d.Amount
                       END
             FROM DELETED d
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = d.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN DELETED d ON d.CustomerId = c.CustomerId

    UPDATE CustomerDetails.Customers
       SET ClearedBalance = ClearedBalance +
          ISNULL((SELECT CASE WHEN CreditType = 0
                       THEN i.Amount * -1
                        ELSE i.Amount
                       END
             FROM INSERTED i
             JOIN TransactionDetails.TransactionTypes tt
               ON tt.TransactionTypeId = i.TransactionType
            WHERE AffectCashBalance = 1),0)
       FROM CustomerDetails.Customers c
       JOIN INSERTED i ON i.CustomerId = c.CustomerId
    RAISERROR ('You have completed an update ',10,1)
    END
    ELSE
    RAISERROR ('Updates have been skipped',10,1)
  2. Now test the change by entering the following to test the trigger.
    SELECT *
      FROM TransactionDetails.Transactions
     WHERE TransactionId=1004
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = 2
    UPDATE TransactionDetails.Transactions
       SET DateEntered = DATEADD(dd,-1,DateEntered)
     WHERE TransactionId = 1004
    SELECT *
      FROM TransactionDetails.Transactions
     WHERE TransactionId=1004
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = 2
  3. When the foregoing code is executed, you should have results similar to Figure 15-7, where the balance of the customer remains constant but the DateEntered is reduced by a day.
    images

    Figure 15-7. Using COLUMNS_UPDATED() within a trigger test results

Now that you have covered DML triggers, you can take a look at DDL triggers.

DDL Triggers

Checking whether an action has happened on an object within SQL Server either on a database or within the server is not code that you will write every day. As more and more audit requirements are enforced on companies to ensure that their data are safe and have not been amended, auditors are now also turning their attention to areas that may cause that data to be altered. A DDL trigger is like a data trigger, as it can execute on the creation, deletion, or modification of rows within system tables rather than on user tables. So how does this help you?

I am sure you can recall specific stories involving major institutions having a program running that removed funds or stock. My favorite is one in which a developer wrote a program that calculated interest on clients’ accounts. Obviously, there needed to be roundings, so the bank always rounded down to the nearest cent. However, all the “down roundings” added up each month to a fairly substantial amount of money. Of course, auditors saw that the data updates were correct, as the amount on the transaction table matched the amount in the client’s account. The interest calculation stored procedure also passed QA at the time. However, once it was live, the developer altered the stored procedure so that all the down roundings were added up in a local variable, and at the end of the process, the amount was added to a “hidden” account. It was a simple stored procedure that never went wrong, and of course it was obfuscated, so nobody by chance could see what the developer had done. They could, of course, see the code as it is only obfuscated, but as it never went wrong, they had no need to. If the stored procedure needed an update, it was the “old” correct code that went live, and the developer simply waited until the time was right and reapplied his code. Auditors could not figure out why at a global level thousands of dollars could not be accounted for over time. Of course, eventually they did, but if they had a DDL trigger so that they received an e-mail or some other notification whenever a stored procedure was released, they could have immediately seen two releases of the stored procedure and asked “Why?” within minutes. Our example will demonstrate this in action.

You have also seen some system DDL triggers as I mentioned at the start of the chapter with the Declarative Management Framework. They are a great deal more complex than the trigger that will be demonstrated and also interact with Management Studio to show what is happening, but in essence they are the same.

First of all, let’s look at database-scoped events, and then toward the end of the section, you will pull the information together in a working example.

DDL Database-Level Events

This section covers events that can force a DDL trigger to execute. Similar to DML triggers that can execute on one or more actions, a DDL trigger can also be linked to one or more actions. However, a DDL trigger is not linked to a specific user table and type of action. Therefore, one trigger could execute on any number of unrelated actions. For example, the same trigger could fire on a stored procedure being created, a user login being dropped, and a table being altered. I doubt if you will create many, if any, triggers like this, but it is possible. A DDL trigger that accepts multiple events that occur can decide what to do with each event, from ignoring upward. However, catching every event results in an overhead on every action.

There are two ways that you can create a trap for events that fire. It is possible to either trap these events individually (or as a comma-separated list) or as a catchall. You will see how to do this once you have looked at what events are available.

Database-Scoped Events

Table 15-2 lists some of the DDL database actions that can be trapped. The full list is comprehensive and covers every database event there is. Many of the actions you will recognize from previous chapters, although the commands have spaces between words rather than underscores.

images images
DDL Statements with Server Scope

Database-level events are not the only events that can be trapped within a trigger; server events can also be caught.

Table 15-3 shows some of the DDL statements that have the scope of the whole server. Many of these you may not come across for a while, if at all, so you will concentrate on database-scoped events.

images

image Note It is not possible to have a trigger that fires on both server and database events; it’s one or the other.

The syntax for a DDL trigger is very similar to that for a DML trigger:

CREATE TRIGGER trigger_name
ON {ALL SERVER|DATABASE}
[WITH ENCRYPTION]
{
{{FOR |AFTER } {event_type,...}
AS
sql_statements}}

The main options that are different are as follows:

  • ALL SERVER|DATABASE: The trigger fires either for the server or the database you are attached to when creating the trigger.
  • Event_type: This is a comma-separated list from either the database or server list of DDL actions that can be trapped.

image Note You can also catch events that can be grouped together. For example, all table and view events can be defined with a group, or this group can be refined down to just table events or view events. The only grouping you will look at is how to catch every database-level event.

Dropping a DDL Trigger

Removing a DDL trigger from the system is not like removing other objects where you simply say DROP object_type object_name. With a DDL trigger, you have to suffix this with the scope of the trigger.

DROP TRIGGER trigger_name ON {DATABASE|ALL SERVER}

EVENTDATA()

As an event fires, although there are no INSERTED and DELETED tables to inspect what has changed, you can use a function called EVENTDATA(). This function returns an XML data type containing information about the event that fired the trigger. The basic syntax of the XML data is as follows, although the contents of the function will be altered depending on what event fired:

<SQLInstance>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ComputerName>name</ComputerName>
</SQLInstance>

I won’t detail what each event will return in XML format; otherwise, you will be here for many pages. However, in one of the examples that follow, you will create a trigger that will fire on every database event, trap the event data, and display the details.

Database-level events have the following base syntax, different from the previously shown base syntax:

<SQLInstance>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ComputerName>name</ComputerName>
    <DatabaseName>name</DatabaseName>
    <UserName>name</UserName>
    <LoginName>name</LoginName>
</SQLInstance>

The XML elements can be described as follows:

  • PostTime: The date and time of the event firing
  • SPID: The SQL Server process ID that was assigned to the code that caused the trigger to fire
  • ComputerName: The name of the computer that caused the event to fire
  • DatabaseName: The name of the database that caused the event to fire
  • UserName: The name of the user who caused the event to fire
  • LoginName: The login name of the user who caused the event to fire

It’s time to see a DDL trigger in action.

TRY IT OUT: DDL TRIGGER

This first example will create a trigger that will execute when a stored procedure is created, altered, or dropped. When it finds this action, it will check the time of day, and if the time is during the working day, then the action will be disallowed and be rolled back. On top of this, you will raise an error listing the stored procedure. This will allow you to see how to retrieve information from the EVENTDATA() function. The final action is to roll back the changes if an action is happening during the working day and send an e-mail.

image Note If you are using SQL Server Express, then it is not possible to send e-mails as this is not available in this edition.

  1. Enter and execute the following trigger, which will fire when a stored procedure is created, modified, or dropped. Of course, enter your own e-mail address for the @recipients. Also alter the @profile_name if you used a different profile name in Chapter 8. Finally, the @subject is split here for reading and should be either two strings or on one line.
    CREATE TRIGGER trgSprocs
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
    AS
    IF DATEPART(hh,GETDATE()) > 8 AND DATEPART(hh,GETDATE()) < 18
    BEGIN
        DECLARE @Message nvarchar(max)
        SELECT @Message =
          'Completing work during core hours. Trying to release - '
          + EVENTDATA().value
            ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
       RAISERROR (@Message, 16, 1)
       ROLLBACK
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'ApressFinancialDBMA',
        @recipients = '[email protected]',
        @body = 'A stored procedure change',
        @subject = 'A stored procedure change has been initiated and rolled back
    during core hours'

    END
  2. You can now test the trigger. Depending on what time of day you run the code, the following will either succeed or fail:
    CREATE PROCEDURE Test1
    AS
    SELECT 'Hello all'
  3. Try running the preceding code between 9 a.m. and 5 p.m. so that it is possible to see the creation fail. Running the code in the afternoon provided me with the following error:
    Msg 50000, Level 16, State 1, Procedure trgSprocs, Line 11
    Completing work during core hours.
    Trying to release - CREATE PROCEDURE Test1
    AS
    SELECT 'Hello all'
    Mail queued.
    Msg 3609, Level 16, State 2, Procedure Test1, Line 3
    The transaction ended in the trigger. The batch has been aborted.
  4. It is necessary to drop the preceding trigger so you can move on, unless of course you are now outside of the prohibited hours and you want the trigger to remain.
    DROP TRIGGER trgSprocs ON DATABASE
  5. This next DDL trigger will not look for any specific event but instead this trigger will execute on any action that occurs at the database. This will allow you to see the XML data generated on any event you want to.
    CREATE TRIGGER trgDBDump
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
        SELECT EVENTDATA()
  6. This trigger can be tested by successfully creating the stored procedure you couldn’t in the first example.
    CREATE PROCEDURE Test1
    AS
    SELECT 'Hello all'
  7. Check the results window. You should see results that you have not seen before. What is returned is XML data, and the results window displays the data as partly shown in Figure 15-8.
    images

    Figure 15-8. Event data XML

  8. If you click the row, a new Query Editor pane opens after a few moments, and the XML data are transposed into an XML document layout. Each of the nodes can be inspected just like the CommandText node was earlier.
    <EVENT_INSTANCE>
      <EventType>CREATE_PROCEDURE</EventType>
      <PostTime>2011-11-14T09:56:26.990</PostTime>
      <SPID>52</SPID>
      <ServerName>FAT-BELLY-SONYAPRESS_DEV1</ServerName>
      <LoginName>FAT-BELLY-SONYRobinDewson</LoginName>
      <UserName>dbo</UserName>
      <DatabaseName>ApressFinancial</DatabaseName>
      <SchemaName>dbo</SchemaName>
      <ObjectName>Test1</ObjectName>
      <ObjectType>PROCEDURE</ObjectType>
      <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
    ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
    ENCRYPTED="FALSE" />
        <CommandText>CREATE PROCEDURE Test1
    AS
    SELECT 'Hello all'
    </CommandText>
      </TSQLCommand>
    </EVENT_INSTANCE>

Summary

DML triggers should be seen as specialized and specific stored procedures set up to help your system with maintaining data integrity, cascading updates throughout a system, or enforcing business rules. If you take out the fact that there are two system tables, INSERTED and DELETED, and that you can check what columns have been modified, then the whole essence of a trigger is that it is a stored procedure that runs automatically when a set data-modification condition arises on a specific table.

DDL triggers will be built mainly for security or reporting of system changes to compliance departments and the like. With the EventData() XML information available to a trigger, a great deal of useful information can be inspected and used further.

Coding a trigger is just like coding a stored procedure with the full control of flow, error handling, and processing that is available to you within a stored procedure object.

The aim of this chapter was to demonstrate how a trigger is fired and how to use the information that is available to you within the system to update subsequent tables or to stop processing and roll back the changes.

The DML triggers built within this chapter have demonstrated how to use the logical tables, as well as how to determine whether a column has been modified. The DDL triggers built have demonstrated how you can trap events and determine what has been changed within either a database or a server.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.226.239