Triggers

A trigger is a special kind of stored procedure attached to a table. Triggers work for tables much like event procedures work for forms. You can place T-SQL code in a trigger that fires when a user attempts to modify a table.

Access 2003 offers support for SQL Server triggers through its UI as well as in VBA procedures. With SQL Server 2000, Microsoft introduced a new kind of trigger called an INSTEAD OF trigger and renamed traditional triggers AFTER triggers. When present, an INSTEAD OF trigger operates instead of any AFTER triggers attached to a table. The Access UI has a special interface for working with AFTER triggers. Due to this special interface and the fact that you need to understand AFTER triggers before you can appreciate INSTEAD OF triggers, this discussion focuses exclusively on AFTER triggers (which the UI calls triggers). See Books Online for more in-depth coverage of INSTEAD OF triggers.

When you create a trigger for a table, all database maintenance operations for the table occur within a transaction. You can specify triggers for insert, update, and delete actions. You can even specify multiple triggers for the same kind of action. For example, you can have two or more delete triggers for the same table. SQL Server 2000 introduces the capability to specify which trigger fires first and which fires last. However, any triggers that fire between the first and last ones will occur in an indeterminate order.

Within the transaction for a trigger, revisions to the table are available from two special temporary tables named deleted and inserted . All deleted records are in the deleted table, and all inserted records are in the inserted table. Updated records move the old version of the record to the deleted table and add the new version to the inserted table. Changes are not committed to a table with a trigger until the trigger transaction concludes. Within the trigger transaction, your T-SQL code can change the operation so that it performs none, some, or more of the action that initiated the trigger. For example, you can roll back a delete, copy a deleted record to another table in the same database, or maintain referential integrity with a table in another database. SQL Server declarative referential integrity does not support referential integrity across two different databases.

Creating and Using Triggers from the Access UI

The next set of instructions will guide you through creating and testing the operation of a trigger for the Shippers table. Start by creating a fresh version of the table. The easiest and most reliable way to do this is to run the CreateAndPopulateShippersTable VBA procedure. This procedure is available from Module1 of the Chapter11SQL.adp file in this book's companion content.

After creating a new version of the Shippers table, select Tables in the Database window and right-click Shippers . Choose Triggers from the menu, and then click New on the Triggers For Table: Shippers dialog box. This creates a template like the one that follows this paragraph. You can use this template as a guide for creating your own custom trigger. For example, suppose you wanted to roll back all attempts to modify the Shippers table. To do so, you could invoke a T-SQL statement and print a message to inform the user of what was happening.

 CREATETRIGGERShippers_Trigger1  ONdbo.Shippers FOR/*INSERT,UPDATE,DELETE*/ AS /*IFUPDATE(column_name)...*/ 

The next T-SQL script shows the text of a trigger for the Shippers table that blocks all changes to the table. Users can still open the table and examine its contents. However, if they try to change the table by inserting a new record, updating an existing record, or deleting a record, the trigger rolls back the action and prints a message that says, "I am untouchable!" The ROLLBACK TRAN statement rolls back any attempt to modify the table, and the RAISERROR statement presents a message to Access from SQL Server.

As with a stored procedure, Access opens the template with a CREATE statement for a trigger. However, any attempt to view the trigger after saving it changes the CREATE keyword to ALTER . The default name for the trigger is Shippers_Trigger1 . You can override this default and use a name that's more meaningful for your application.

 CREATETRIGGERShippers_Trigger1 ONShippers FORINSERT,UPDATE,DELETE AS ROLLBACKTRAN RAISERROR50000'Iamuntouchable!' 

Figure 11-23 shows the error message generated by the Shippers_Trigger1 trigger. Notice that the Shippers table shows an attempt to update the CompanyName field from Speedy Express to Speedy Expressx . This attempt generates the error message box. This rollback action is a cool, powerful feature: With just a couple of lines of T-SQL code, you can protect a table from being changed. The protection applies even if users try to make a change directly in the table without using one of your Access forms.

Figure 11.23: An error message generated by the Shippers_Trigger1 trigger.

You can clear the attempted change to the table by pressing Escape twice. Then close the table.

Programmatically Changing a Table with a Trigger

Recall that we have three VBA procedures for making changes to the Shippers table. These are the procedures that use parameters for inserting, updating, and deleting records in a table. Run the InsertANewShipper procedure from Module1 of the Chapter11SQL.adp file. Notice that it fails with a run-time error. The message that accompanies the error message reads, "I am untouchable!" This message confirms that the Shippers_Trigger1 trigger is preventing the InsertANewShipper procedure from adding a new record to the Shippers table. As you'll recall, this procedure ran successfully earlier in the chapter.

Note  

A sample in the "User-Defined Functions" section at the end of this chapter shows how to trap an error and retrieve its message without relinquishing control to the error.

You might need to temporarily disable a trigger to make some changes to a table that it's blocking. One way to do this is with an ALTER TABLE statement that includes a DISABLE TRIGGER statement. Recall that a trigger applies to a table. Therefore, you need to modify a table when you want to disable a trigger that applies to it. Follow the DISABLE TRIGGER keyword phrase with the name of the trigger that you want to disable. After making your changes to the table, you should reenable the trigger. To do so, you can embed an ENABLE TRIGGER statement inside another ALTER TABLE statement.

The following VBA procedure shows an updated version of the VBA procedure for inserting a record into the Shippers table. This procedure uses the DISABLE TRIGGER and ENABLE TRIGGER statements to determine when the trigger operates and to make a change to the table when the trigger is disabled. The sample runs the ALTER TABLE statements with a Command object because it's already available, thanks to the approach that inserts a new record by using the parameters.

 SubInsertANewShipper2() Dimcmd1AsADODB.Command Dimprm1AsADODB.Parameter Dimprm2AsADODB.Parameter     'DisableShippers_Trigger1 Setcmd1=NewADODB.Command cmd1.ActiveConnection=CurrentProject.Connection cmd1.CommandText=_  "ALTERTABLEShippersDISABLETRIGGERShippers_Trigger1" cmd1.CommandType=adCmdText cmd1.Execute     'PointaConnectionobjectatthestoredprocedure cmd1.CommandType=adCmdStoredProc cmd1.CommandText= "Insert_a_new_shipper"     'Createandappendparameters Setprm1=cmd1.CreateParameter("@CompanyName",adVarChar,_ adParamInput,40) prm1.Value= "CABDelivers" cmd1.Parameters.Appendprm1     Setprm2=cmd1.CreateParameter("@Phone",adVarChar,_ adParamInput,24) prm2.Value= "[phone]" cmd1.Parameters.Appendprm2     'Invokeastoredprocedurebyexecutingacommand cmd1.Execute     'ReenableShippers_Trigger1 cmd1.CommandText=_ "ALTERTABLEShippersENABLETRIGGERShippers_Trigger1" cmd1.CommandType=adCmdText cmd1.Execute     EndSub 

Problem and Fix for Manual Trigger Creation

If you create even a moderately sized collection of triggers manually, your code will be in jeopardy. There are a couple of reasons for this. First, users can readily update the triggers through the Access UI if they have permission. In cases where you have no backup for the triggers, diagnosing the problem and developing a solution can be time consuming. Second, if you need to drop a table, your application loses all triggers associated with that table. When you manually enter triggers using the Access UI, you make your code vulnerable to both problems. In addition, you have no way to automate a recovery from the problem after it occurs.

An alternative is to create your triggers in VBA programmatically. This still does not stop either problem from occurring. However, recovering a lost or corrupted trigger is as simple as dropping an existing version of the trigger and then running the code to create a new version of it. The next pair of VBA procedures modifies the CreateAndPopulateShippersTable procedure discussed earlier in this chapter so that it automatically creates the Shippers_Trigger1 sample just presented. Before actually creating the new trigger, the procedure deletes a prior version if one is available. A call to the Drop_a_trigger procedure performs this. The Drop_a_trigger procedure demonstrates how to use the Exists keyword to test for such a condition ”for example, the existence of a trigger with the target name in a database's sysobjects table. This system-defined table maintains one row for each object within a database. The Type column of this table denotes the type of object for each row, and the Name column represents an object's name.

 SubCreateAndPopulateShippersTable2()  Dimstr1AsString Dimcnn1AsADODB.Connection DimTableNameAsString DimTriggerNameAsString     'PointaConnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'Deletethetableifitexistsalready TableName= "Shippers" Drop_a_tablecnn1,TableName     'Createthetable str1= "CREATETABLE " &TableName& " " &_  "(" &_  "ShipperIDintIDENTITY(1,1)NOTNULLPRIMARYKEYCLUSTERED, " &_  "CompanyNamevarchar(40)NOTNULL, " &_  "Phonevarchar(24)NULL " &_  ")" cnn1.Executestr1     'Runcustomstoredproceduretopopulatetablebased 'onNorthwindCSdatabase str1= "EXECCopy_from_NorthwindCS_Shippers" cnn1.Executestr1     'Deletethetriggerifitexistsalready TriggerName= "Shippers_Trigger1" Drop_a_triggercnn1,TriggerName     'Addthetriggerafterpopulatingthetable str1= "CREATETRIGGER " &TriggerName& " " &vbLf&_  "ONShippers " &vbLf&_  "FORINSERT,UPDATE,DELETE " &vbLf&_  "AS " &vbLf&_  "ROLLBACKTRAN " &vbLf&_  "RAISERROR50000'Iamuntouchable!'" cnn1.Executestr1     'RefreshDatabasewindowtoshownewtable RefreshDatabaseWindow     EndSub     SubDrop_a_trigger(cnn1AsADODB.Connection, TriggerNameAsString)  Dimstr1AsString     'PointaConnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'SearchsysobjectsforTriggerName,anddrop 'thetriggerifitexists str1= "IFEXISTS(SELECTnameFROMsysobjects " &_  "WHEREname='" &TriggerName& "'ANDtype='TR') " &_  "DROPTRIGGER " &TriggerName cnn1.Executestr1     EndSub 

Archiving Updated and Deleted Records

Instead of blocking changes, we can use triggers to archive them. The table of archived records provides a source of changes to a table that's easy to examine. To perform this kind of task, we need a table to hold the archived changes as well as a trigger to write the changes to that table.

The following procedure illustrates one approach to creating a table of archived records for the Shippers table. The ArchivedShippers table will store the ShipperID , CompanyName , and Phone column values for any updated or deleted records in the Shippers table. The ArchivedShippers table also includes a primary key with an IDENTITY property setting. This column provides a convenient way of tracking the order of changes to the Shippers table.

 SubCreateArchivedShippersTable()  Dimstr1AsString Dimcnn1AsADODB.Connection DimTableNameAsString     'PointaConnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'Deletethetableifitexistsalready TableName= "ArchivedShippers" Drop_a_tablecnn1,TableName     'Createthetable str1= "CREATETABLE " &TableName& " " &_  "(" &_  "ArchiveIDintIDENTITY(1,1)NOTNULLPRIMARYKEYCLUSTERED, " &_  "ShipperIDintNOTNULL, " &_  "CompanyNamevarchar(40)NOTNULL, " &_  "Phonevarchar(24)NULL " &_  ")" cnn1.Executestr1     EndSub 

The next code sample shows the VBA procedure to create the new trigger that does the archiving. This new version of the Shippers_Trigger1 trigger fires whenever an attempt to update or delete a record in the Shippers table occurs. The INSERT INTO statement in the trigger copies the values from the deleted table to the ArchivedShippers table. This action preserves the records before the delete or update action takes effect.

 SubCreateArchivingTrigger()  Dimstr1AsString Dimcnn1AsADODB.Connection DimTriggerNameAsString     'PointaConnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'Deletethetableifitexistsalready TriggerName= "Shippers_Trigger1" Drop_a_triggercnn1,TriggerName     'Addatriggertoarchivedeletedorupdatedrecords str1= "CREATETRIGGERShippers_Trigger1 " &vbLf&_  "ONShippers " &vbLf&_  "FORUPDATE,DELETE " &vbLf&_  "AS " &vbLf&_  "INSERTINTOArchivedShippers " &_  "(ShipperID,CompanyName,Phone) " &vbLf&_  "SELECTShipperID,CompanyName,PhoneFROMdeleted " cnn1.Executestr1     EndSub 

Our example concludes with a procedure that demonstrates the operation of the Shippers_Trigger1 trigger and the ArchivedShippers table. The next procedure merely calls procedures we've already discussed in this chapter. However, through the Shippers_Trigger1 trigger these procedures now preserve the unchanged values of records updated or deleted from the Shippers table.

The sample begins by creating a fresh copy of the Shippers table with a call to the CreateAndPopulateShippersTable procedure. Next it invokes the two procedures just discussed to create a fresh copy of the Archived Shippers table and the Shippers_Trigger1 trigger. Then the procedure successively invokes the InsertANewShipper , UpdateAShipper , and DeleteAShipper procedures. We discussed the operation of these procedures earlier in the chapter. In this demonstration, these three procedures add a new record to the original Shippers table and then modify and delete that record. This creates two records in the ArchivedShippers table. The first record represents the newly added record just before it gets modified. The second record in the ArchivedShippers table represents the changed record just before it's deleted from the Shippers table. The sample procedure concludes by opening the ArchivedShippers table so that you can click the View Microsoft Access button on the VBA code window toolbar and confirm the availability of the two records in the ArchivedShippers table.

 SubDemoArchivingWithTrigger()  'GenerateafreshcopyoftheShipperstable CreateAndPopulateShippersTable     'Createthetableforarchivingdeletedand 'updatedrecords,andthetriggertopopulate 'thetable CreateArchivedShippersTable CreateArchivingTrigger     'Addarecord,thenupdatetogenerateanarchivedrecord. 'Next,deletetheupdatedrecordtogenerateasecond 'archivedrecord. InsertANewShipper UpdateAShipper DeleteAShipper     'Openthearchivedtableforviewing. 'ClickViewMicrosoftAccesstoolbarcontroltoseetable.  DoCmd.OpenTable "ArchivedShippers"     EndSub