BA 372 - Triggers and Stored Procedure Lab

BA 372 - Triggers and Stored Procdure Lab

The purpose of this lab is to expose you to the use of database stored procedures and database triggers.

Imagine that a company tracks its clients and stores a credit limit for each client as a business control. Credit limit changes can be made by only a few individuals and any changes to the credit limit should be tracked. The company implements integrated authentication where the end user’s Windows login credentials are used within the application program for authorization.

We will explore three deployment scenarios:

VB code containing SQL commands

VB code which calls a stored procedure

VB code calling a more robust stored procedure with enhanced logging, a database trigger, and improved messages

Each of these scenarios updates the credit limit after verifying the user’s right to do so and logs the changes.

To prepare for this exercise, we created a database for you on server Cobmisclass01. Your database name is StDB_ABC123 but replace ABC123 with your COB account name.

Open up SQL Server Management Studio, which can be found under the Start => Programs => Microsoft SQL Server 2005 => SQL Server Management Studio. For the server name type in cobmisclass01 and select Connect. Now open a new query window by selecting the New Query button below the File menu. Make sure that the database selected is your student database. Under the New Query button is a drop down menu with databases.

We start by setting up the database. It contains only three tables:

Table ActionLog is used to log any activities that must be logged: its fields are:

Username (holds the name of the user conducting the action).

ActionDate: the date and time the action takes place.

Comment: optional field to store a comment.

Table Clients stores credit limit information for customers:

ClientId: needs no explanation.

CreditLimit: needs no explanation.

CreditLimitChanged: the date and time the credit limit changed.

CreditLimit_ChangedBy: the Username of the person that changed it.

Table RoleAuthorization holds information on which user can perform which business process actions:

AuthUserName: the user authorized to perform an action.

AuthRole: the role assigned to AuthUserName.

Please note some appropriate primary and foreign keys have been omitted to simplify the code.

To set up the tables and data needed for this exercise copy the following SQL code into a new query window and execute it.

-- Drop the tables if they exist
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActionLog]') AND type in (N'U'))
DROP TABLE [dbo].[ActionLog]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Clients]') AND type in (N'U'))
DROP TABLE [dbo].[Clients]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoleAuthorization]') AND type in (N'U'))
DROP TABLE [dbo].[RoleAuthorization]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthCheck]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthCheck]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChgClientCreditLimit]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ChgClientCreditLimit]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetLastActionlogEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetLastActionlogEntry]

CREATE TABLE [dbo].[ActionLog](
     [UserName] [varchar](50),
     [ActionDate] [datetime],
     [Comment] [varchar](1024)
)

CREATE TABLE [dbo].[Clients](
     [ClientId] [int] not null,
     [ClientName] [nvarchar](256),
     [CreditLimit] [int],
     [CreditLimit_Changed] [datetime],
     [CreditLimit_ChangedBy] [varchar](50)
)
Alter TABLE[dbo].[Clients]
     Add Primary Key([ClientId])

CREATE TABLE [dbo].[RoleAuthorization](
     [AuthUserName] [varchar](50),
     [AuthRole] [varchar](32)
)

-- Let's insert a couple of clients to use in our tests
insert into clients(Clientid,ClientName,CreditLimit,creditlimit_changedby)
     values(1,'Bobs Pizza',5001,SYSTEM_USER)

insert into clients(Clientid,ClientName,CreditLimit,creditlimit_changedby)
     values(12,'Freds Footwear',5012,SYSTEM_USER)

Notice how in the above inserts, SYSTEM_USER is a built-in variable that evaluates to your login; hence, it is not contained in quotes, even though creditlimit_changedby contains a string.

You can re-run this script at any time to reset the database if you want to try again or when you get stuck.

Scenario 1 VB code containing SQL commands

Open up Microsoft Visual Studio 2005 and create a new console application (File => New Project => Visual Basic => Console Application).

The comments in the VB code below describe what is being done at each step.

A couple of the SQL commands that we use in this VB.Net program may be new for you.

The INSERT command adds rows to a database. In this lab we will use the following syntax:

INSERT INTO TableName (ColumnNames) SELECT ColumnValues The list of ColumnValues (separated by commas) corresponds in order to the list of ColumnNames.

The UPDATE command changes the contents of rows in a database. The syntax we will use is:

WHERE SomeColumnName = ExistingValue

This will update ColumnName to NewValue in all rows matching the WHERE clause.

SELECT TOP 1 returns only the first row from a query

Paste in the following code:

Module Module1

     Sub HandleError(ByRef e As Exception, ByVal Connection As System.Data.SqlClient.SqlConnection)
          Console.WriteLine("Error..." + e.Message)
          If Connection.State = ConnectionState.Open Then
               Connection.Close()
          End If
          System.Environment.Exit(1)
     End Sub

     Sub Main()

          Dim MyCOBAccountName As String
          ' Enter your COB account name (not password!)
          MyCOBAccountName = "byr1001"

          ' This sets up a connection string to talk to SQL server
          ' using your Windows Authentication Credentials
          Dim ConnectString As String = _
               "Server=cobmisclass01.bus.oregonstate.edu;" + _
               "Initial Catalog=stdb_" + _
               MyCOBAccountName + ";Integrated Security=SSPI"

          ' These are different objects than you used for DB access before
          ' But they act about the same
          Dim Connection As System.Data.SqlClient.SqlConnection
          Dim SQLCommand As System.Data.SqlClient.SqlCommand
          Dim SQLDataReader As System.Data.SqlClient.SqlDataReader
          Dim result_string As String
          Dim result_Integer As Integer

          'open the connection
          Connection = New System.Data.SqlClient.SqlConnection(ConnectString)
          Try
               Console.WriteLine(ConnectString)
               Connection.Open()
               Console.WriteLine("Connection open...")
          Catch e As Exception
               HandleError(e, Connection)
          End Try

          ' Now lets begin our application code
          ' define the variables: A client# (1 and 12 are there)
          Dim ClientId As Int32 = 12
          Dim NewCreditLimit As Int32 = 12001

          ' *******************
          ' Check for User Authorization
          ' *******************
          ' To authorize, you would have a row with this user (we use system_user again)
          ' and this Role in the RoleAuthorization Table
          SQLCommand = New System.Data.SqlClient.SqlCommand( _
          "select count(*) from RoleAuthorization " _
          + "where(AuthUsername = system_user)and " _
          + "AuthRole = 'ChgClientCreditLimit'", Connection)

          Try
               SQLDataReader = SQLCommand.ExecuteReader()
               SQLDataReader.Read()
               ' Notice... I have to deal with SQL data types somehow
               ' GetInt32? GetString?
               'We’re only requesting one column (count)
               ' so we specify the (0) field from the returned record)
               result_Integer = SQLDataReader.GetInt32(0)
               SQLDataReader.Close()

               'in case the execution fails, close the connection and exit
          Catch e As Exception
               HandleError(e, Connection)
          End Try

          ' User is Denied, log the Denial
          If result_Integer > 0 Then
               Console.WriteLine(" User Authorized")
          Else
               Console.WriteLine("!!! Denied Access !!!")
               Try
                    SQLCommand = New System.Data.SqlClient.SqlCommand( _
                    "insert into ActionLog(UserName,ActionDate,comment) " _
                    + "select system_user,getdate(),'<ActionType>DeniedAuth</ActionType>" _
                    + "<msg>DENIED ChgClientCreditLimit Client#" _
                    + CStr(ClientId) + "</msg>'", Connection)
                    ' Note the alternate call to execute a command not a query
                    SQLCommand.ExecuteNonQuery()
               Catch e As Exception
                    HandleError(e, Connection)
               End Try

               Console.WriteLine()
               Console.WriteLine("Press Enter to Exit.")
               System.Console.ReadLine()
               Exit Sub

          End If

          ' If we reach this code, we made it through the authorization check.
          ' *******************
          ' Make the change by running the SQL update command
          ' *******************
          Try
               SQLCommand = New System.Data.SqlClient.SqlCommand( _
               "update Clients set CreditLimit = " + CStr(NewCreditLimit) _
               + ",CreditLimit_ChangedBy = system_user,CreditLimit_Changed = getdate()" _
               + "where ClientId = " _
               + CStr(ClientId), Connection)
               ' Note the alternate call to execute a command not a query
               SQLCommand.ExecuteNonQuery()
          Catch e As Exception
               HandleError(e, Connection)
          End Try

          ' *******************
          ' Log the change
          ' *******************
          Try
               SQLCommand = New System.Data.SqlClient.SqlCommand( _
               "insert into ActionLog(UserName,ActionDate,comment) " _
               + "select system_user,getdate(),'<ActionType>UpdateClient</ActionType>" _
               + "<ClientId>" + CStr(ClientId) + "</ClientId>" _
               + "<NewCreditLimit>" + CStr(NewCreditLimit) + "</NewCreditLimit>'" _
               , Connection)
               SQLCommand.ExecuteNonQuery()
          Catch e As Exception
               HandleError(e, Connection)
          End Try

          ' *******************
          ' Display some result. How about the last log record?
          ' *******************

          Try
               SQLCommand = New System.Data.SqlClient.SqlCommand( _
               "select top 1 comment from ActionLog " _
               + "order by ActionDate desc", Connection)
               SQLDataReader = SQLCommand.ExecuteReader()
               SQLDataReader.Read()
               ' Notice... I have to deal with SQL data types somehow
               ' GetInt32? GetString?
               result_string = SQLDataReader.GetString(0)
               SQLDataReader.Close()

               'in case the execution fails, close the connection and exit
          Catch e As Exception
               HandleError(e, Connection)
          End Try

          Console.WriteLine(result_string)

          'we’re done so we can close the connection
          Try
               Connection.Close()
               Console.WriteLine("Connection closed...")
          Catch e As Exception
               HandleError(e, Connection)
          End Try

          Console.WriteLine()
          Console.WriteLine("Press Enter to Exit.")
          System.Console.ReadLine()
     End Sub

End Module

Notice that in the above code we have abstracted the various catch blocks used to handle database interaction errors in a sub called HandleError; saves a lot of lines of code.

Also notice that the action log record we create and return is an XML snippet. We do this because XML is easily parsed and so we make it easy to exchange these comments later.

Compile and Run. The output should look something like this:

Server=cobmisclass01.bus.oregonstate.edu;Initial Catalog=stdb_byr1001;Integrate
d Security=SSPI
Connection open...
!!! Denied Access !!!
<ActionType>DeniedAuth</ActionType><msg>DENIED ChgClientCreditLimit Client#12</m
sg>
Connection closed...

Press Enter to Exit.

Notice that you were denied the update of the database record because your code recognized that you were not authorized to make this change.

Now authorize yourself by excuting the following command in a query window:

Insert into RoleAuthorization (AuthUserName,AuthRole)
Select SYSTEM_USER,'ChgClientCreditLimit'

Try your program again. This time your results ought to be similar to the following:

Server=cobmisclass01.bus.oregonstate.edu;Initial Catalog=stdb_byr1001;Integrate
d Security=SSPI
Connection open...
User Authorized
<ActionType>UpdateClient</ActionType><ClientId>12001</ClientId><NewCreditLimit>
Connection closed...

Press Enter to Exit.

Scenario 2 VB code which calls stored procedures

Now we will move some of the functionality of this program from the VB program into a stored procedure.

First, a few words about stored procedures:
The procedures you will use in the lab are written in Transact-SQL. Microsoft added extensions to standard SQL to support a variety of functions in SQL Server. A similar language PL/SQL was developed by and is used in Oracle. Try not to get hung up on the details of stored procedure SQL syntax, but here are a few notes to guide you as you read the code:

Take a look at the following example of creating a stored procedure:

CREATE PROCEDURE [dbo].[ChgClientCreditLimit]
     @ClientId int
     @NewLimit int
     @SomeVariable int OUTPUT
AS
BEGIN
     code goes here
END

The Create Procedure syntax begins with the procedure name ([dbo].[ChgClientCreditLimit]), followed by a list of parameters (@ClientId int, @NewLimit int). Parameter names that begin with an @ are input parameters (passed to the procedure from the calling program). Parameters coded as OUTPUT can be passed back to the calling program. Parameters – input or output – can be given default values (e.g. = “Bob”). Parameters with default values do not have to be supplied by the calling program. Of course, you do not have to have parameters at all.

AS signals the beginning of the procedure code. The code itself sits between the BEGIN and END delimiters.

Variables can be declared in the body of the procedure using the declare @VariableName syntax with a data type (Note that unlike in VB.Net, you cannot initialize variables in a declare statement.)

Some more explanation of the procedure code below:

IF BEGIN/END

CAST

SET NOCOUNT ON

-- comment

@@ROWCOUNT

@@ERROR

System_name

OBJECT_NAME(@@PROCID)

Getdate()

So, now let’s define a couple of procedures:

First, a procedure to update a client’s credit limit:

CREATE PROCEDURE [dbo].[ChgClientCreditLimit]
-- Accept a client id, username, and credit limit
-- 1) verify the user is authorized to perform this function
-- 2) make the change
-- 3) log the change
-- 4) Send back a response
     @ClientId int,
     @NewLimit int
AS
BEGIN
     SET NOCOUNT ON;       -- Prevents return of an additional, unwanted result set

          -- Check authorization
          -- Count rows for this user, this role - should be max one logically
          declare @AuthCnt tinyint
          select @AuthCnt = count(*) from RoleAuthorization
               where AuthUsername = system_user and AuthRole = 'ChgClientCreditLimit'

          -- Test the result: No matching record? Return 1 (Denied)
          if @AuthCnt < 1
               BEGIN
                    -- record the denial in the log
                    insert into ActionLog(UserName,ActionDate,comment)
                    select system_user,getdate(), -- system user date and time
                         '<ActionType>DeniedAuth</ActionType>'
                         + '<msg>DENIED ChgClientCreditLimit'
                         + ' Client#'+ cast(@ClientId as varchar (8)) + '</msg>'
                    select 'Denied Access'
                    RETURN
               END

     -- change the record, trap errors
          update Clients set
               CreditLimit = @NewLimit,
               CreditLimit_ChangedBy = system_user,
               CreditLimit_Changed = getdate()
          where ClientId = @ClientId

          IF @@error <> 0      -- @@error <> 0 on an sql execution error
               or @@RowCount = 0      -- @@RowCount says how many rows were changed
               BEGIN
                    Select 'The Update Failed: System Error('
                    +cast(@@error as varchar(5))+') or Client Record Not Found'
                    RETURN
               END

     -- Create a log record
          insert into ActionLog(UserName,ActionDate,comment)
          select system_user,      -- name given in update
               getdate(),      -- system date and time
               '<ActionType>UpdateClient</ActionType>'
               + '<ClientId>'+cast(@ClientId as varchar(20))+ '</ClientId>'
               + '<NewCreditLimit>'+cast(@NewLimit as varchar(20))+ '</NewCreditLimit>'

     Select 'Credit Limit Changed'
     RETURN

END

Copy and paste the above procedure code into your query window and execute the query.

The next procedure will run a query against the security log and return its results. (In practice, we would not usually allow users to see the log, nor would we use the log as part of our regular processing.)

CREATE PROCEDURE [dbo].[GetLastActionlogEntry] AS
-- This a a simplistic way to grab the last message in XML format
BEGIN
     SET NOCOUNT ON;      -- avoids unwanted extra result sets

     select top 1      -- top 1 means the first record
     '<ActionLogEntry>'
     + '<UserName>' + UserName + '</UserName>'
     + '<ActionDateTime>' + cast(ActionDate as nvarchar(20)) + '</ActionDateTime>'
     + comment
     + '</ActionLogEntry>'
     from actionlog
     order by actiondate desc

END

Run the above create procedure code in the query window as well.

Now replace the VB.Net code in your console app with the following:

Notice how in comparison with the first program, this program contains much less code because all of the SQL work is now done by the procedure stored in the database. Of course, the stored procedure itself must still be called.

Compile and run. The results should be something like this:

Server=cobmisclass01.bus.oregonstate.edu;Initial Catalog=stdb_byr1001;Integrate
d Security=SSPI
Connection open...
Credit Limit Changed
<ActionLogEntry><UserName>BUS\byr1001</UserName><ActionDateTime>Apr 16 2007 1:
10PM</ActionDateTime><ActionType>UpdateClient</ActionType><ClientId>12</ClientId
><NewCreditLimit>13001</NewCreditLimit></ActionLogEntry>
Connection closed...

Press Enter to Exit.

Remove your authorization as folows:

In the query Window:

Delete RoleAuthorization where AuthUserName=SYSTEM_USER

Now run the program again and notice the effect of the authorization removal.

Reauthorize yourself running:

Insert into RoleAuthorization (AuthUserName,AuthRole)
Select SYSTEM_USER,'ChgClientCreditLimit'

Run your program again.

Notice that the VB code now only needs to know the connection string and the name of the procedure. The SQL code that does the credit limit change is now separated out and lives inside the database. Hence, if other programs would need to change credit limit, they can all use the same stored procedure. This encapsulation could be accomplished by placing the code to a separate code module or subroutine. But, organizations today often interact with their database from a variety of interfaces, e.g., web apps, remote procedure calls, and windows apps for example. By storing this code in the database instead, the functionality applies across multiple platforms.

Scenario 3: Enhanced stored procedure techniques

Now that the logic for changing and logging is all stored on the database side rather than on the application program side, we can further improve its functionality.

First, lets consider authorization.

Many programs will likely perform the same type of check. If each of them has the code to access the security tables, then a change in the authorization scheme would affect a lot of applications. If the authorization logic is encapsulated in a separate stored procedure it becomes somewhat equivalent to a subroutine stored in a separate code library which is linked to many individual application programs. When we modularize by creating a separate stored procedure the same code can be executed either by programs directly or by other stored procedures. However, please note that because the code lives in the database, changes go into effect without recompiling, relinking, or redistributing copies of the executable. This can save time and reduce the complexity of deployment. It also means that an errant procedure could immediately and negatively impact a whole bunch of applications. Thus, the use of a separate authorization procedure has significant architectural implications.

Create this procedure:

CREATE PROCEDURE [dbo].[AuthCheck]
     -- Check Authorization for the system_user in the supplied role
     -- Record the time you checked this user/action (role) combination
     -- If denied add it to the log, with a message if provided
     -- Data is returned two ways:
          -- in the return code, (See the Return commands)
          -- and in an output parameter (Putting a value in @ReturnMsg)
     @Role varchar(32),
     @DenialComments varchar(128) = '',
     @ReturnMsg varchar(128) ='Default Value' OUTPUT
     -- Adding a default to @ReturnMsg makes it optional although
     -- the default value not available when proc is executed

AS
BEGIN

     SET NOCOUNT ON;      -- avoids unwanted extra result sets

     select @ReturnMsg = 'DENIED'      -- Default for the Output Parameter

     -- count the rows for this user/role
     Declare @AuthCnt int
     select @AuthCnt = count(*) from RoleAuthorization
          where AuthUsername = system_user and AuthRole = @Role

     -- System error? Jump out, Don't even try to log
     IF @@ERROR <> 0      BEGIN
               Select @ReturnMsg = 'SQLSysError while authorizing (Proc=AuthCheck)'
               return @@ERROR      -- return the error code in the return code
     END

     IF @AuthCnt > 0      BEGIN
               Select @ReturnMsg = 'OK'      -- Populates the Output Parameter
               return 0      -- does a returncode style response
     END

     -- Log Denials: Patterns of denials are interesting!
     insert into ActionLog(UserName,ActionDate,comment)
          select system_user,getdate(),      -- system user date and time
          '<ActionType>DeniedAuth</ActionType>'
          + '<msg>DENIED ' + @Role + ' ' + @DenialComments + '</msg>'

     return 1      -- does a returncode style response

/* usage

declare @Answer nvarchar(10)
declare @RC int

--- Output Parameter
exec authcheck
     @Role='ChgClientCreditLimitx'
     ,@ReturnMsg = @Answer output
select 'read the output parameter' + @Answer

--- ReturnCode
exec @rc = authcheck
     @Role='ChgClientCreditLimitx'

select 'The Return Code is stored in @rc ' + cast(@rc as varchar(10))

*/

END

Note that AuthCheck records denied attempts. The code to decide which denied attempts must be logged is thereby captured in a single location. Keeping it consistent and verifying its implementation does not require going to multiple programs or multiple procedures. This illustrates how good modular design can be implemented in a stored procedure environment. Please also note that tried and true return code processing and error handling are supported by the stored procedure environment.

Next, lets think about comprehensive logging.
Although our ChgClientCreditLimit allows logging to be implemented a the database level, it does not guarantee that all credit limit changes will be recorded. If a program implements code to do the update directly, or if utility programs are used to update the records, changes won’t be recorded in the log. We can address this concern by creating a trigger.

Recall from class that a database trigger is a stored procedure that can be set to execute whenever specific database changes occur. Hence, we can define a trigger which executes and enters information into the log when credit limits are changed.

CREATE TRIGGER [dbo].[Tr_LogClientChanges]
     ON [dbo].[Clients]
     FOR INSERT,DELETE,UPDATE
AS
BEGIN
     SET NOCOUNT ON;
     -- this trigger logs credit limit changes in ActionLog
     -- Recording adds, deletes, and updates

     --- Adds put no rows in the deleted table
     insert into ActionLog(UserName,ActionDate,comment)
     select system_user,      -- name per system
          getdate(),      -- system date and time
          '<ClientChg> type="Insert">'
          + '<ClientId>'+cast(ClientId as varchar(20))+ '</ClientId>'
          + '<NewCreditLimit>'+cast(CreditLimit as varchar(20))+ '</NewCreditLimit>'
          + '<Comment>New Client ' + ClientName + '</Comment>'
          + '</ClientChg>'
     from inserted
     where clientid not in (select clientid from deleted)

     --- Deleted put no rows in the inserted table
     insert into ActionLog(UserName,ActionDate,comment)
     select system_user,getdate(),
     '<ClientChg> type="Delete">'
          + '<ClientId>'+cast(ClientId as varchar(20))+ '</ClientId>'
          + '<OldCreditLimit>'+cast(CreditLimit as varchar(20))+ '</OldCreditLimit>'
          + '<Comment>Deleted Client ' + ClientName + '</Comment>'
          + '</ClientChg>'
     from deleted
     where clientid not in (select clientid from inserted)

     --- Updates put rows in both tables
     insert into ActionLog(UserName,ActionDate,comment)
     select inserted.CreditLimit_ChangedBy,getdate(),
     '<ClientChg> type="ChgClient">'
          + '<ClientId>'+cast(inserted.ClientId as varchar(20))+ '</ClientId>'
          + case when deleted.Creditlimit<>inserted.Creditlimit then
          + '<OldCreditLimit>'+cast(deleted.CreditLimit as varchar(20))+ '</OldCreditLimit>'
          + '<NewCreditLimit>'+cast(inserted.CreditLimit as varchar(20))+ '</NewCreditLimit>'
          else '' end
          + case when deleted.Creditlimit<>inserted.Creditlimit then
          + '<OldClientName>'+cast(deleted.ClientName as varchar(20))+ '</OldClientName>'
          + '<NewClientName>'+cast(inserted.ClientName as varchar(20))+ '</NewClientName>'
          else '' end
          + '<Comment>Updated Client:' + inserted.ClientName + '</Comment>'
          + '</ClientChg>'
     from deleted, inserted
     where deleted.clientid = inserted.clientid
END

The above trigger fires whenever an Update, Insert, or Delete is performed on the Clients table.

CREATE TRIGGER

FOR INSERT

DELETE

UPDATE

This is a so-called AFTER trigger meaning that it fires after the INSERT, UPDATE or DELETE has taken place but before control is passed back to the calling program. If the trigger fails, all changes roll back. Thus, if the logging fails the update, insert or delete does not occur.

Triggers can ‘see’ what happened during a table change as these changes are stored in temporary tables that are created on-the-fly immediately before the trigger fires.
Inserted records appear in the temporary table called INSERTED.
Deleted records appear in the temporary table called DELETED.
On an UPDATE, the old values of affected records are reported in the DELETED table and the new values appear in the INSERTED table. You could think of it this way: on an update, the DBMS completely deleted the old record and inserted a new one in order to change a field.

To explore the functionality of the above trigger, go to a query window and execute the following:

     insert into clients(Clientid,ClientName,CreditLimit,creditlimit_changedby)
          values(99,'Some New Client',9999,system_user)
     Exec GetLastActionLogEntry

Do you see that it logs the change even though the change was made through the SQL query window rather than by a business process-specific application? These sorts of utilities are widely available and can be used to alter sensitive data while avoiding controls that might be built into a business application.

Now try:

delete clients where clientid=99
exec GetLastActionLogEntry

As you can see in this trigger’s code, a trigger can access other tables and respond to the details of any changes. For example, if we wanted we could adjust the trigger so that it only logs changes to existing records where the credit limit is changed rather than logging all changes.

How to do that? Well, you could make the trigger FOR UPDATE instead of FOR INSERT, DELETE, UPDATE and then you could take out the first two insert commands and add WHERE inserted.creditlimit <> deleted.creditlimit.

Using a trigger to do logging has several potential advantages:

The logging code for any update to the table is stored in a single place simplifying testing and maintenance.

The log entry will be created even when a change is made using a utility program.

The details of the logging can be changed without looking at application code. This allows a database manager to respond to new security parameters without touching business logic. A seperation of duties between the programmers and the database group is common in large organizations and is considered to be a way of reducing the risk of programmers inserting nefarious or erroneous code into an application.

Logging implementation details are not mingled with the other business logic found in programs or stored procedures. This makes the application logic simpler reducing dvelopment and testing complexity.

Access rights to the trigger can be separated from the access rights to the program or stored procedures to reduce the risk of a programmer intentionally or unintentionally circumventing the security rules.

Updates don’t occur if the insert into the log fails. While similar functionality could be built by instituing transactions with rollback at the program or procedure level, these approaches would involve additional code (increasing the cost of development and the risk of programmer error or fraud) and can increase the likelihood that a transaction will cause a deadlock if an error occurs. This is because the trigger is executed by the DBMS and does not rely on a successful “commit” command. Applications and even stored procedures can fail for a variety of reasons between begin transaction and commit or rollback leaving partially-processed data and un-released database locks.

Triggers run even when many udpates are done in one command. Imagine a command like update clients set Creditlimit = Creditlimit + 1000. All the records in the table would be changed, and all the changes would appear in the log. Try it if you like.

Of course there are also potential downsides:

Triggers use resources and slow down database changes.

An error in a trigger can prevent updates and tracking down the problem could become difficult.

Increased dependencies between tables can make maintenance activities difficult. For example, to work with an old copy of the client table you would have to have the actionlog table available as well.

Changing a field name in the action log could cause updates to the Clients table to fail.

Here is a new version of the ChgCLientCreditLimit procedure which uses the AuthCheck procedure instead of looking directly at the authorization tables and relies on the trigger for logging. It is much shorter than the previous version and, while the code has simply been moved to other modules, those other modules are part of an improved architecture which supports code sharing to reduced risk and reduce development costs.

ALTER PROCEDURE [dbo].[ChgCLientCreditLimit]
-- Accept a client id and a new credit limit
-- 1) verify the user is authorized to perform this function
-- 2) make the change
-- 3) Send back a response
-- This procedure demonstrates how modularity applies
-- 1) authorization is handled in a separate procedure
-- 2) Logging functionality relies on the trigger
-- Also, more expressive messages are returned to the client
     @ClientId int,
     @NewLimit int
AS
BEGIN
     SET NOCOUNT ON;      -- avoids creating an unwanted result set

     declare @rc int      -- make a variable to store a return code

     -- Use the procedure (AuthCheck) to authorize the action
     exec @rc = AuthCheck @Role='ChgClientCreditLimit'
     if @rc <> 0      BEGIN
          select 'Denied'
          RETURN
     End

     -- Do the update and check for errors
     update Clients set
          CreditLimit = @NewLimit,
          CreditLimit_ChangedBy = System_User,
          CreditLimit_Changed = getdate()
     where ClientId = @ClientId

     -- Check for system errors
     -- @@error <> 0 on an sql execution error
     -- @@RowCount says how many rows were changed
     -- No rows updated? Then we didn't really do anything!
     IF @@ERROR <> 0 or @@RowCount <> 1      BEGIN
          Select 'The Update Failed: System Error('
          +cast(@@error as varchar(5))+') or Client Record Not Found'
          RETURN
     END

     -- Note that the logging is done in a trigger

     SELECT 'Credit Limit Changed'
     RETURN

END

At this point you should have a good idea on how, when writing code that interacts with a database, we have a choice as to where to write and store certain sections of functionality. In the app? In the database? or use a mix? With any of these choices come advantages and disadvantages. Although it’ll take some experience to really appreciate these differences, you should be aware of their existence and have at least some ideas on how these different methods work.

For those of you interested in just a bit more depth, you should realize that all of the above codes can be refined quite a bit using much more sophisticated methods and procedures. For instance, we can much improve our error handling, debug messages and logging messages. For example, for denials let’s log who tried to change which client to what amount. This might be important if the updates should have been allowed or if we are looking for fraudulent activity.

ALTER PROCEDURE [dbo].[ChgClientCreditLimit]
-- Accept a client id and a new credit limit
-- 1) verify the user is authorized to perform this function
-- 2) make the change
-- 3) send back a response
-- Modularity is demonstrated:
-- 1) authorization is handled in a separate procedure
-- 2) logging functionality relies on the trigger
-- Error handling has been improved in this version
-- 1) a more expressive message is passed to AuthCheck to reord more details in case of a denial
-- 2) system error is differentiated from logic error when no records are updated
-- 3) the message returned to the client provides more information about the change
     @ClientId int,
     @NewLimit int
AS
BEGIN
     SET NOCOUNT ON;      -- avoids creating an unwanted result set

     declare @rc int      -- make a variable to store a return code
     declare @MyError int      -- make a variable to store the @@Error value
     declare @MyRowCount int      -- make a variable to store the @@RowCount value

     -- Using a called procedure (AuthCheck) could be done simply
     --     exec @rc = AuthCheck @Role='ChgClientCreditLimit'
     --     if @rc <> 0 return 1

     -- But we can do much more when good monitoring is desired:
     -- Make a place to catch messages from AuthCheck
     Declare @ReturnMsg varchar(128)      --- See how this is used as an OUTPUT parameter
     --- Prepare a log message in case Authorization is Denied
     -- This optional parameter allows for more detailed logging in AuthCheck
     Declare @Msg varchar(128)
     Select @Msg = 'Change credit limit for client '
          + cast(@ClientId as varchar(15))
          + ' to ' + cast(@NewLimit as varchar(15))

     -- Call the sub-procedure
     exec @rc = AuthCheck @Role = 'ChgClientCreditLimit '
          , @DenialComments = @Msg
          , @ReturnMsg = @ReturnMsg OUTPUT

     -- Now we can do extensive error reporting and give debugging feedback
     if @rc = 1      -- Denied
          BEGIN
               -- Send a result set back to the calling program
               Select 'User denied client credit limit privileges'
               RETURN
          END
     if @rc <> 0      -- System Error in Auth
          BEGIN
               -- Return extensive error and debugging messages
               Select 'System Error checking client credit limit privileges:'
               + @ReturnMsg + ': In Database Procedure:' +OBJECT_NAME(@@PROCID);
               RETURN
          END

     -- change the record
     update Clients set
          CreditLimit = @NewLimit,
          CreditLimit_ChangedBy = System_User,
          CreditLimit_Changed = getdate()
     where ClientId = @ClientId

     -- Save @@Error and @@RowCount because any statement resets them
      Select @MyError = @@ERROR, @MyRowCount = @@RowCount

     -- Check for system errors
     IF @MyError <> 0 -- @@error <> 0 on an sql execution error
          BEGIN
               Select 'System Error checking client credit limit privileges:'
               + @ReturnMsg + ': In Database Procedure:' +OBJECT_NAME(@@PROCID);
               RETURN
          END

     -- @@RowCount says how many rows were changed
     -- No rows updated? Then we didn't really do anything!
     IF @MyRowCount = 0
          BEGIN
               Select 'Client #:'
               + cast(@ClientId as varchar(15))
               + ' not found'
               RETURN
          END

     -- Where did the logging go?
     -- that is now done in a trigger, using a trigger to log
     -- means that any change is automatically logged not
     -- just changes from well behaved procedures

     -- Tell them it worked looking in the file for the current value
     Select 'Credit Limit For Client'
          + cast(@ClientId as varchar(15))
          + ' is now ' + cast(CreditLimit as varchar(15))
     from Clients
     where ClientId = @ClientId
     RETURN

END

Note how in the above code, system errors, denials, and attempts to change a nonexistent client are tracked separately and reported to the calling program.

Also note that a detailed message is prepared and passed to AuthCheck to produce a more detailed log of security-related events. AuthCheck was not changed, we just used more of the features. But again, we did this without altering the VB code. Thus, without changing any of the application programs which use the stored procedure, we can adjust the functionality associated with this action in the database.