C H A P T E R 1 3
Triggers
Although you have become quite proficient in using SQL Server 2008, you really 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.
There will be times when 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), keep track of changes for each record (audit trail),
or do any other processing that you require when data on a specific table is modified. 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 commands.
• Talk about 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 can execute either on a data modification, known as
a Data Modification 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.
418 CHAPTER 13 ■ T R IGGERS
■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, check that he has 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 number of that item 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.
We can create separate triggers for any table action except SELECT, or triggers that will fire on any
combination of table actions. 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.
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.
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, you either 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.
■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 has been wiped without any logging. This also means that a DELETE trigger will not
fire, as it is not a deletion per se that is happening.
CHAPTER 13 ■ TRIGGERS 419
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, it can be created for a single action or for multiple actions. To expand on
this, a trigger can be for insertion of a record only, or it can cover inserting and updating the record.
■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.
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: First of all, as ever, you need to inform SQL Server
what you are attempting to do, and in this instance, you wish 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. In this chapter, you name
the triggers starting with tg to indicate the object is a trigger, followed by the type of trigger
(ins for insert, del for delete, and upd for update), and then the name of the root table the trigger
will be associated with.
• 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 and these cascades will complete before the trigger fires. You either specify FOR or AFTER.
• INSTEAD OF: 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.
420 CHAPTER 13 ■ T R IGGERS
• {[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 wish the trigger to do. Therefore, at this point, you need to mention which combination
of commands, separated by a comma, you wish to work with.
• AS: The keyword AS defines that the trigger code has commenced, just as the AS keyword defined
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 can be used within a trigger
that is not available within a stored procedure, and that is the test to check whether a specific
column has been modified or not. This happens through the use of the UPDATE() keyword. By
placing the name of the column to test in between the parentheses, a logical TRUE or FALSE will
be returned depending on whether the column has been updated or not. 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 record manipulation will set the UPDATE test to the necessary value.
• 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 that you must keep in mind when building a trigger is that a trigger fires after
each record is flagged to be modified, but before the modification is actually placed into the table.
Therefore, if you have a statement that updates many rows, the trigger will fire after each record is
flagged, not when all the records have been dealt with.
■Note Keep in mind, 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, we’ll look at 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 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 only able to validate data that is 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 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 to ensure that data forming a key is correct, or when
referential integrity needs to be enforced 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.
CHAPTER 13 ■ TRIGGERS 421
If the object to be built is for business rules and may require 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 like verifying that a date field only contains
values within a certain range.
Deleted and Inserted Logical Tables
When a table is modified, whether this is by an insertion, modification, or removal, an exact record of the
row of data 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 record of the row of data is placed in the DELETED table. Finally, when an update
occurs on a row of data, a record 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 the INSERTED table is populated
with 100 rows. Finally, the trigger will fire. Once the trigger has completed, the data for that
table is 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 only be interrogated via a SELECT statement and cannot
be modified. You can only access these tables within a trigger to find out which records have been
inserted, updated, or deleted.
■Note There are two version stores within each instance of SQL Server. This is an advanced topic, and we don’t
cover it, 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 we 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 Trigger
The first trigger we 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 we are near the
end of the book, our T-SQL within the trigger will be more advanced than some of the code so far.
422 CHAPTER 13 ■ T R IGGERS
Try It Out: Creating a Trigger in Query Editor
The purpose of our example is to change a customer’s account balance when a financial transaction occurs as defined by
an INSERT in the TransactionDetails.Transactions table. We want to change the balance AFTER the row has
been inserted into the TransactionDetails.Transactions table. This is so we 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. Ensure that Query Editor is running and that you are logged in with an ID that can insert objects into the database.
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
2. It is now time to enter the remainder of the code for the trigger. We 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. We can JOIN from the INSERTED table to the TransactionDetails.TransactionTypes
table to find out whether we 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 we can alter the Amount by multiplying it by -1 so that we are reducing a
customer’s balance. Notice the subquery includes a WHERE statement so that if we 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 we 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 will have.
■Note This trigger does have a deliberate bug, which is included so that you can see a little later in this section
what happens when a trigger has a bug.
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
CHAPTER 13 ■ TRIGGERS 423
3. Execute the code to create the trigger in the database. We can test the trigger now by inserting a cash withdrawal
or deposit relating to the two transaction types we currently have. We will list the customer balance before executing
the INSERT into the TransactionDetails.Transactions table, and then we 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=1
INSERT INTO TransactionDetails.Transactions (CustomerId,TransactionType,
Amount,RelatedProductId, DateEntered)
VALUES (1,2,200,1,GETDATE())
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE customerId=1
4. Execute the code. As you see in Figure 13-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.
Figure 13-1. Balance reduction after trigger action
5. Our 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=2, and
then a second row entered on TransactionType=3, which is the equities product showing the addition of
shares. This is a simple accounting procedure of one debit and one credit. Enter the following code:
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE customerId=1
INSERT INTO TransactionDetails.Transactions (CustomerId,TransactionType,
Amount,RelatedProductId, DateEntered)
VALUES (1,3,200,1,GETDATE())
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE customerId=1
6. Now execute the code. Instead of seeing two rows where the balance hasn’t altered, we see the following error
message and on the Results tab only one set of output, as shown in Figure 13-2. The trigger has a bug in that the
subquery will return a NULL value where the transaction type does not affect a cash balance, and it has not
accounted for that scenario. There are two reasons for showing you an error: the first is to demonstrate how to
alter a trigger; the second, more importantly, is to determine whether the INSERT statement succeeded or failed.
424 CHAPTER 13 ■ T R IGGERS
(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.
Figure 13-2. Balance not updated
7. To reiterate, the INSERT statement is correct and would normally work. However, as the trigger has a bug, 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 13-3:
SELECT *
FROM TransactionDetails.Transactions
WHERE CustomerId=1
Figure 13-3. Transaction table listing
8. We can change a trigger using the ALTER TRIGGER command. The changes to the code occur in the subquery:
we surround the single column we will have returned with an ISNULL() test. If the result is NULL, then we
transpose this with the value of 0 as the cash balance is not to alter. The code we 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
CHAPTER 13 ■ TRIGGERS 425
9. Once the changes have been completed, you can then execute the code to alter the trigger. You can now rerun
our test, which will add a row to the TransactionDetails.Transactions table without altering the balance.
If you like, you can also list the TransactionDetails.Transactions table to prove that the INSERT
succeeded this time, as Figure 13-4 demonstrates.
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE customerId=1
INSERT INTO TransactionDetails.Transactions (CustomerId,TransactionType,
Amount,RelatedProductId, DateEntered)
VALUES (1,3,200,1,GETDATE())
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE customerId=1
Figure 13-4. Transactions table with no balance change
Checking Specific Columns
It is possible to check whether a specific column or set of columns have 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. Checking columns and only
performing specific T-SQL code 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 we 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
for a column, because, quite simply, you cannot delete columns; you can only delete whole rows of
data. If you wish 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 wish 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)]
426 CHAPTER 13 ■ T R IGGERS
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
we 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, we 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 us are Amount and
TransactionType.
Try It Out: UPDATE() Function
1. Within Query Editor, let’s alter our 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 we 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
CHAPTER 13 ■ TRIGGERS 427
4. We can test that the trigger works by reducing the amount of a withdrawal—in this case, TransactionId
number 5—which currently sits at a value of $200. The following code will list the transactions for CustomerId=1
and the current cleared balance. We 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 = 1
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE CustomerId = 1
UPDATE TransactionDetails.Transactions
SET Amount = 100
WHERE TransactionId = 5
SELECT *
FROM TransactionDetails.Transactions
WHERE CustomerId = 1
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE CustomerId = 1
5. Once you execute the code, the transactions amount and cleared balances are altered, as shown in Figure 13-5.
So now we know the trigger has worked and will do these actions no matter what happens to the transaction table.
Figure 13-5. Transactions and balances
6. We are now going to alter the trigger to test the Amount and TransactionType columns. If there is an update,
we will complete the actions described previously; if not, then we will skip this processing. We will prove which
path the trigger takes by using the system function RAISERROR, which you saw in the discussion of error handling
in Chapter 11. Each section of the IF statement will have an appropriate RAISERROR.
428 CHAPTER 13 ■ T R IGGERS
7. We will now alter the trigger to only update the CustomerDetails.Customers table if Amount or
TransactionType is altered. If we execute this code, we will have a RAISERROR saying this is what we have
done. Similarly, if we don’t update the table, we 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 ('We have completed an update',10,1)
END
ELSE
RAISERROR ('Updates have been skipped',10,1)
8. We 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=5
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE CustomerId = 1
UPDATE TransactionDetails.Transactions
SET DateEntered = DATEADD(dd,-1,DateEntered)
WHERE TransactionId = 5
CHAPTER 13 ■ TRIGGERS 429
SELECT *
FROM TransactionDetails.Transactions
WHERE TransactionId=5
SELECT ClearedBalance
FROM CustomerDetails.Customers
WHERE CustomerId = 1
9. Once you have run this code, you will see the Results tab showing the DateEntered being altered but the
ClearedBalance not, as Figure 13-6 illustrates. However, at this point, we don’t know if this is because we
have removed and then readded the amount, giving a null effect.
Figure 13-6. Details where updates have been skipped
10. Moving to the Messages tab, we can see the RAISERROR that occurred when we 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. 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.
430 CHAPTER 13 ■ T R IGGERS
■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. We 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 we discuss how
this works, inspect Table 13-1. A bit value increases by the power of 2 as you progress down the bit
settings, as you can see.
■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 our table have both been altered, we 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 we have a byte data type variable. We then set the variable to a value;
in this case, we believe that bits 0 and 1 will be set. By using the & operator, we can check this. To reiterate,
slightly confusingly, it’s not the bit position we 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 only test the first eight columns on
this basis. Obviously, tables will contain more than eight columns, as you have seen with the
TransactionDetails.Transaction table we have just been using.
Table 13-1. Bit Settings and the Equivalent Decimal Value
Bit Value
0 1
1 2
2 4
3 8
4 16
5 32
6 64
7 128
CHAPTER 13 ■ TRIGGERS 431
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 we are almost there. It is necessary to
substring the COLUMNS_UPDATED() into eight-bit chunks for each set of eight columns. However, we
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 wish to test whether column 9 has been updated, the statement would be as
follows, where we 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)))
We 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
we 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 we 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 us are Amount and
TransactionType.
Try It Out: COLUMNS_UPDATED()
The example in this section will take the same example as UPDATE() and convert 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. Alter the trigger and follow the previous example to ensure you get
the same results.
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
432 CHAPTER 13 ■ T R IGGERS
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 ('We have completed an update ',10,1)
END
ELSE
RAISERROR ('Updates have been skipped',10,1)
Now that we have covered DML triggers, we 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 is safe and has 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 we can all 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
CHAPTER 13 ■ TRIGGERS 433
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, then toward the end of the section, you will pull
the information together in a working example.
DDL_DATABASE_LEVEL_EVENTS
This section presents a listing of all the 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 table or type of action. Therefore, one
trigger could execute on any number of unrelated transactions. 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.
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
we have looked at what events are available.
Database-Scoped Events
Table 13-2 lists all the DDL database actions that can be trapped. This is quite a comprehensive list
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.
Table 13-2. Possible Database Scoped Events to Listen For
CREATE_TABLE ALTER_TABLE DROP_TABLE
CREATE_VIEW ALTER_VIEW DROP_VIEW
CREATE_SYNONYM DROP_SYNONYM CREATE_FUNCTION
ALTER_FUNCTION DROP_FUNCTION CREATE_PROCEDURE
ALTER_PROCEDURE DROP_PROCEDURE CREATE_TRIGGER
ALTER_TRIGGER DROP_TRIGGER CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION CREATE_INDEX ALTER_INDEX
DROP_INDEX CREATE_STATISTICS UPDATE_STATISTICS
DROP STATISTICS CREATE_ASSEMBLY ALTER_ASSEMBLY
DROP_ASSEMBLY CREATE_TYPE DROP_TYPE
CREATE_USER ALTER_USER DROP_USER
CREATE_ROLE ALTER_ROLE DROP_ROLE
CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE
CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
434 CHAPTER 13 ■ T R IGGERS
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 13-3 shows 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 we will concentrate on database-scoped events.
A DDL trigger can also accept every event that occurs within the database and, within the T-SQL
code, decide what to do with each event, from ignoring upward. However, catching every event
results in an overhead on every action.
■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_CONTRACT ALTER_CONTRACT DROP_CONTRACT
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_REMOTE_
SERVICE_BINDING
ALTER_REMOTE_
SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_SECEXPR DROP_SECEXPR CREATE_XML_SCHEMA
ALTER_XML_SCHEMA DROP_XML_SCHEMA CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
Table 13-3. DDL Server Scoped Statements You Can Listen For
CREATE_LOGIN ALTER_LOGIN DROP_LOGIN
CREATE_HTTP_ENDPOINT DROP_HTTP_ENDPOINT GRANT_SERVER_ACCESS
DENY_SERVER_ACCESS REVOKE_SERVER_ACCESS CREATE_CERT
ALTER_CERT DROP_CERT
Table 13-2. Possible Database Scoped Events to Listen For (Continued)
CHAPTER 13 ■ TRIGGERS 435
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.
■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 we
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:
I won’t detail what each event will return in XML format; otherwise, we will be here for many
pages. However, in one of the examples that follow, we 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:
436 CHAPTER 13 ■ T R IGGERS
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
1. 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, we 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.
CREATE TRIGGER trgSprocs
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
IF DATEPART(hh,GETDATE()) > 9 AND DATEPART(hh,GETDATE()) <>
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 = 'SQL Server Database Mail Profile',
@recipients = 'robin@fat-belly.com',
@body = 'A stored procedure change',
@subject = 'A stored procedure change has been initiated and rolled back
during core hours'
END
2. We 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:
CHAPTER 13 ■ TRIGGERS 437
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 we can move on, unless of course you are now outside of the prohibited
hours and you wish the trigger to remain.
DROP TRIGGER trgSprocs ON DATABASE
5. We can create our second DDL trigger. This time we will not look for any specific event but wish this trigger to
execute on any action that occurs at the database. This will allow us to see the XML data generated on any event
we 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 we couldn’t create in our 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 shown in Figure 13-7.
Figure 13-7. Event data XML
8. If you click the row, a new Query Editor pane opens after a few moments, and the XML data is transposed into
an XML document layout. Each of the nodes can be inspected just like the CommandText node was earlier.
438 CHAPTER 13 ■ T R IGGERS
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE" />
CREATE PROCEDURE Test1
AS
SELECT 'Hello all'
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 virtual 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.
439
■ ■ ■
C H A P T E R 1 4
SQL Server 2008 Reporting Services
As a beginner learning how to use SQL Server 2008, the final piece of the jigsaw puzzle is discovering
how to retrieve data from a database and place it on a report. Various reporting tools inhabit the
marketplace. Some, such as the Business Objects stable of products, are very powerful, but they’re
generic tools that cater to many different data sources. Microsoft has produced its own reporting
tool, Reporting Services, which is becoming more and more powerful with each version. The aim of
this chapter is to introduce you to Reporting Services so you can see some of what is achievable.
Building a report is a straightforward process of creating a connection, defining the data to
return, and then placing the data along with either other in-built functions or functions that you
build yourself. Once you build the report, you can deploy it to a reporting web server where users
throughout your organization can access it. It is possible for your user base to then export reports in
formats such as PDF or Excel, e-mail reports, and embed reports in applications.
Reporting Services is not just for SQL Server–based data. It can also retrieve data from a myriad
of data repositories to enhance and expand your SQL Server reports. Don’t get confused and believe
that Reporting Services is like Business Objects Crystal Reports in the sense that it has the same
amount of functionality. Reporting Services is improving and expanding its functionality, but Crystal
Reports allows connection to a greater set of data sources and can also produce more powerful graphs
in its reports. SQL Server Reporting Services is designed and streamlined for SQL Server.
This chapter will show you how to use Reporting Services to build a simple report, deploy it, and
then view the results in a web browser. If, at the end of the exercises, you think this is something that
you will use, then I recommend you read Pro SQL Server 2008 Reporting Services by Rodney Landrum,
Shawn McGehee, and Walter J. Voytek II (Apress, 2008), as it does require a whole book to learn how
to use this tool well.
In this chapter, I will cover the following:
• Understanding the Reporting Services architecture
• Configuring Reporting Services
• Building a report to return data
Let’s dive straight in and look at the architecture.
Reporting Services Architecture
When you installed SQL Server 2008 way back in Chapter 1, one of the options was to include
Reporting Services. At the time, you completed a default Native installation, which built two databases
and created the necessary setup required to be able to build reports on your desktop. I also
mentioned that if you were in a larger organization, it was possible to set up using SharePoint as the
report repository.
440 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
If you had chosen the SharePoint Integrated mode, then your Reporting Services would have
been deployed onto a SharePoint web server farm. The deployment would have placed components
on the farm to allow interaction with SharePoint. SharePoint is a good technology to have within a
large organization for many reasons, not the least of which is that it’s a managed central resource
point with features for archiving data, tracking changes, and showing the history of any changes
made. You can protect data so that no changes can be made. This allows auditors to know that no
information within a report has been altered either accidentally or maliciously.
In comparison to Integrated mode, Native mode is deployed as a standalone set of components
on your machine. Interaction with SQL Server, the designer, and the report viewer all occurs locally,
except when you’re connecting to data remotely.
If you take a look at Figure 14-1, which is taken from Books Online, you can see how Reporting
Services is built. The architecture will still work if you take the Report Server components and place
them on a SharePoint web server.
Figure 14-1. Reporting Services architecture
I won’t delve any more into the specific architecture, but it’s useful to know a little about the
data layer level, which contains two SQL Server databases and a lot of options for data sources.
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 441
SQL Server 2008 Reporting Services uses two SQL Server databases (ReportServer and
ReportServerTempDB) to store the information used by Reporting Services. The ReportServer database
stores static metadata such as report definitions, data sources, users, roles, subscriptions, and
schedule definitions. The ReportServerTempDB database stores temporary objects such as work tables
or session data.
The report data sources can come from SQL Server, Analysis Services, Excel, Access, Oracle, flat
files, or any OLE DB or Open Database Connectivity (ODBC) data sources. Using data-processing
extensions, you can add new sources of data.
Now that you have some understanding of Reporting Services, let’s start configuring this service
using the configuration tool.
Configuring Reporting Services
It is necessary to use the Reporting Services Configuration tool to set up SQL Server Reporting
Services for the server. You may find that this has been completed already on server instances that
you’re connecting to. However, for new installations, the service needs to have certain properties
defined before you can build and display reports.
Try It Out: Configuring Reporting Services
1. Start up the Configuration tool by navigating to Start ➤ All Programs ➤ Microsoft SQL Server 2008 ➤ Configuration
Tools ➤ Reporting Services Configuration Tools ➤ Reporting Services Configuration Manager. This displays a
connection dialog, as shown in Figure 14-2. The server is the computer that you’re currently running Reporting
Services on, but you can find any other SQL Server instance using the Server Name and Report Server Instance
properties. Once you’re happy, select Connect.
Figure 14-2. Connecting to a Report Server instance
2. You should now be in the Reporting Services Configuration Manager screen where you can define many
attributes of the Report Server setup. Figure 14-3 shows where you set up the server and start and stop the
service. Ensure that the service is started.
442 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-3. Report Server running status and details
3. The first option in the service definition is the service account that Reporting Services will use within Windows to
execute. Figure 14-4 shows that in this setup, Reporting Services is using a network account. You would use this
account when you have SQL Server on one server but your IIS installation defined on another server within the
network. You would also use this setup when the service needs to access resources over the network. However,
to make the configuration secure, you should either use an existing Windows account with the least amount of
authority to do what is required or create a Windows account specifically for Reporting Services.
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 443
Figure 14-4. Defining the service account
4. Figure 14-5 shows the configuration details used to access the Report Server. The virtual directory that Reporting
Services will use to connect to and then display the content is defined in the virtual directory option. This can be
an existing directory if you wish to keep the reports for all your SQL Server instances in one place. However, it’s
more likely that it will be a different virtual directory for each instance. You can administer each virtual directory
so that just like SQL Server, if one instance crashes, it won’t affect any other. If one virtual directory has problems, it
shouldn’t affect any other reporting virtual directory. If you see the warning triangle, as shown in Figure 14-5,
then click Apply to create your virtual directory. If there is no warning triangle, then the virtual directory should
already exist. Once the virtual directory has been created, you can access it via the Web service URL toward the
bottom of the figure. However, let’s complete the installation first.
444 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-5. Creating the IIS folder and the URL for the Reporting Services pages
5. After clicking Apply, you should see the directory built as shown in Figure 14-6.
Figure 14-6. Folder created and URL defined
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 445
6. Let’s move on to the next option, Database. If you click Change Database, you should see the screen shown in
Figure 14-7. Here you can create a new database for this Report Server instance or select an existing Report
Server database to use. If you have several SQL Server instances on one server and it is appropriate to have just
one Report Server instance (for example, in small organizational servers), then using an existing Report Server
database is probably the correct option. You would also use an existing Report Server if you did not have many
reports to produce or if the reports weren’t intensive. There is not much point in having a Report Server for each
instance if each Report Server only has a handful of reports. This example creates a new Report Server database.
Figure 14-7. Creating the Report Server databases for your server
7. Most of the options and settings for creating a database should be self-explanatory, as most were covered in
Chapter 1 when you built your SQL Server installation. After clicking Next in the screen shown in Figure 14-7, you
should come across the details for the server and the connection, as shown in Figure 14-8. The server is the SQL
Server installation you wish to use, and the authentication type is either Windows or SQL Server, as seen in
Chapter 1. Once you’re satisfied with the details and have tested the connection, click Next.
8. If this database is linked directly and solely to a SQL Server database, that means you’re working with an installation
of one SQL Server to one Report Server. Therefore, a naming standard reflecting this might be useful.
Figure 14-9 shows the Report Server database with a meaningful name, ApressFinancialReports.
446 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-8. Building the connection with relevant credentials
Figure 14-9. Naming the Reporting Services database
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 447
9. Figure 14-10 shows the final screen for the setup. This is where you define the login credentials between the
Report Server service and the Report Server database. Although Reporting Services is web-based in its deployment, it
doesn’t use an ASP.NET authentication. This means that you have to create a separate connection for the Report
Server to use. In Figure 14-10, the connection to the database uses the same credentials as Windows uses when
starting the Report Server service. Once you’re done, click Next. This brings up a summary; click Finish to build
the database.
Figure 14-10. Defining the credentials for Reporting Services to connect to the database
10. Once you build the database, you will be brought back to the Reporting Services Configuration Manager, as shown in
Figure 14-11. Here you define the configuration details that a report builder will use to access the reports. This
should not be confused with the URL shown at the bottom of Figure 14-5, which is the URL users will access to
see the reports. Click Apply to build the virtual directory and define the URL that you will use to design the reports.
I won’t cover the remaining settings, because they’re not required to build your first reports. Now that you have Reporting
Services set up, it’s time to move on to that first report.
448 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-11. The Report Manager URL
Building Your First Report Using Report Designer
In this section, you will call the Report Designer within Business Intelligence Development Studio to
produce a simple report containing the list of transactions from the TransactionDetails.Transactions
table. It will join the CustomerDetails.Customers table and also use some of the built-in reporting
functions to produce the date, time, page numbers, and so on.
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 449
Try It Out: Using the Report Wizard
1. From the Start menu, select Programs ➤ Microsoft SQL Server 2008 ➤ SQL Server Business Intelligence
Development Studio. Then select File➤ New ➤ Project.
2. You’re presented with a Visual Studio–based dialog that displays a list of different projects that Business Intelligence
Development Studio supports, as shown in Figure 14-12. There are three different report-based projects;
you’ll be working with the Report Server Project Wizard, which takes you through building a simple report step
by step. It works in a similar fashion to wizards for other competitive reporting products. Give your project a
meaningful name, and place the project in a logical location. Once you’re happy, click OK.
Figure 14-12. Selecting the relevant Business Intelligence project
450 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
3. After the welcome screen from the wizard, you need to define the connection details. Give the new data source
a name, ensure that Microsoft SQL Server is selected, and click Edit to build the connection details. You’re presented
with a connection dialog, as shown in Figure 14-13. You need to populate the server name with the server
you want your report to connect to and retrieve the data from. Next, define the authentication to use, then set the
database that this connection will use. If you have data coming from multiple servers or if you require multiple
connections to the same server because of user credential restrictions on databases, then you can set up multiple
data connections within the designer. You will see where this is possible in a few moments. Once you’re
happy, click OK.
Figure 14-13. Report connection details
4. This now brings you back to your data source dialog, as shown in Figure 14-14. Click Next.
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 451
Figure 14-14. A complete data source connection
5. You’re now presented with the Query Designer, where you can enter T-SQL to build your set of data with, as
shown in Figure 14-15. This designer works in a similar fashion to the View Designer you have seen already. The
following T-SQL, which forms the query, can be seen in the figure as well.
SELECT c.CustomerFirstName + ' ' + c.CustomerLastName as 'Name',
t.DateEntered, tt.TransactionDescription,t.Amount
FROM CustomerDetails.Customers c
JOIN TransactionDetails.Transactions t ON
t.CustomerId = c.CustomerId
JOIN TransactionDetails.TransactionTypes tt ON
tt.TransactionTypeId = t.TransactionType
452 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-15. The Query Builder ready for the query definition
6. Once you click OK, the next dialog asks you to define whether you want to build a tabular or matrix report. Select
Tabular and click OK.
7. The final thing you need to define within this wizard is how to lay out the report. This is a simplistic approach for
designing a report, but it is effective. As shown in Figure 14-16, work from the top down. Define which columns
you want at the top of each page, then create a page break. The second area allows you to define how data is
grouped on the page; this is where you can create a page break. The bottom option allows you to define the fields
that are displayed for each row of data returned. You can either click Next to see a summary screen, or click
Finish to define the report.
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 453
Figure 14-16. Detailing the fields and how they are used in the report
8. You’re now able to see the report solution in the Report Designer. On the left-hand side of the screen shown in
Figure 14-17, you can see the database connection you made a few steps earlier at the bottom of the list. On the
right are details about the Report Definition Language (.rdl) file and a properties dialog similar to the properties
dialog you saw in Chapter 5 when you were building tables. In the middle is the result of the details placed in the
table layout designer shown in Figure 14-16. You can move these fields around, and you can even place aggregations
or special fields on the report, such as a page number or the date the report was produced. You can find
these options under the Built-in Fields option on the left.
9. Let’s go back to the DataSet definition, because from this part of the designer it is possible to access a different
set of dialogs for defining the data set. Highlight DataSet1 or the name you called it, right-click, and select
Dataset Properties. The first option, Query, is just the same query you had earlier. However, if the query has a
parameter that is passed to it and defined in using an @ prefixed variable, as demonstrated in the following code
snippet, then you can define the parameter and a value in the second option, as shown in Figure 14-18. A similar
screen is also displayed if you’re running the report interactively.
WHERE c.CustomerId = @CustId
454 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-17. The Query Builder ready for the query definition
Figure 14-18. A defined parameter with a default value
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 455
10. The third option in the properties dialog is the Fields option, as seen in Figure 14-19. Any parameter returned
from a stored procedure or any column defined in a query can be given a more user-friendly name by altering the
details in the left-hand column. The left-hand column shows the value displayed in the report, and the right-hand
column shows the name of the field that sources the data.
Figure 14-19. Demonstrating the ability to alter the field names in the report
11. The penultimate option deals with how to define the returned data. For example, you can define the collation and
determine whether columns should be a fixed width or stretch to the largest size for the data. Figure 14-20
shows the six possible options.
12. The last part of defining the data set concerns filtering the data. This is useful if you have a stored procedure that
returns data that requires further filtering. As you can see in Figure 14-21, we have nothing to define. Once
you’re done, click OK.
13. Now click OK or Cancel.
There is much more to learn concerning reports, including deploying them to a Report Server. However, because of the
many things you need to consider, I fully recommend you read Pro SQL Server 2008 Reporting Services by Rodney Landrum,
Shawn McGehee, and Walter J. Voytek II (Apress, 2008).
456 CHAPTER 14 ■ SQL SERVER 2008 REPORTING SE RVICES
Figure 14-20. The ability to change some reporting options
Figure 14-21. The ability to define a filter for the data
CHAPTER 14 ■ SQL SERVER 2008 REPORTING SERVICES 457
Summary
This brings us to the end of Beginning SQL Server 2008 for Developers. Throughout the book, you’ve
followed an example that has demonstrated how to report data. You have seen an overview of how
to build a simple report, preview the results, and then deploy a report that’s ready for users.
At this point, you should be relatively proficient in SQL Server 2008, although there is a great
deal still to learn. However, the aim of this book was to take you right from the beginning and make
you proficient enough to be able to complete development tasks within SQL Server. The next move
for you would be to read Accelerated SQL Server 2008 by Rob Walters, Michael Coles, Robin Dewson,
Donald Farmer, Fabio Claudio Ferracchiati, and Robert Rae (Apress, 2008) and Pro SQL Server 2005
Assemblies by Robin Dewson and Julian Skinner (Apress, 2005).
Good luck!
Nenhum comentário:
Postar um comentário