Creating Triggers

Triggers, like most SQL Server objects, are created using T-SQL code, in Management Studio, and the SQL-SMO triggers collection. You can also write triggers in C# or any other .NET language and run them on the SQL Server CLR. This is covered in Chapters 11 and 14. Depending on your needs, the SQL-SMO and .NET Framework object model provides an alternative to T-SQL, and a very useful one at that. For the most part, however, the typical path to trigger creation and management is via T-SQL, so whip out Management Studio, or whatever tool you use to write and test T-SQL code, and get cracking.

Trigger Deployment

Deploying a trigger requires more than just writing the code and assigning the trigger to a table or view and then crossing your fingers that your landing gear is down. This is engineering, so you need to approach this as an engineer. The following steps, illustrated in the flow chart in Figure 13–2, document the process of trigger creation and deployment from beginning to end. Create your own deployment plan, which can act as a checklist that will take you from concept to deployment in a logical, well-controlled manner. I do trigger work for a number of clients, and thus each one has a file and trigger deployment plan for one or more triggers (and the overall trigger plan).

Figure 13–2: The steps to trigger deployment

Step 1: Obtain trigger requirements   The requirement specs are obtained from the trigger plan. Your system may be large enough to warrant formal trigger assignment, as would large OLTP or e-commerce systems that will require several developers working on the project.

The following specification is an example of a trigger requirement on a call center application that logs the date and time and the agent connects to SQL Server on a number of tables. I could use the logging capability of the profiler, but the output is difficult to work with from the viewpoint of the call center equipment, such as the ACD scheduler that needs to have information about the CSR’s open case load, and from the call center analyst who needs to export the data to decision support systems.

• CSR Shift Log AFTER Trigger   Record the date and time the agent (via the Web service proxy) initially logs on to SQL Server and runs Open-Shift. The Open-Shift session gets the agent oriented, reviews shift objectives, considers past shift performance, and so on. The trigger also records the Close-Shift data…the date and time when the agent has concluded Close-Shift and logs off the system.

  The trigger can only be fired when the OpenCloseShift table is accessed, so the information records when the CSR logged on to the CRM application and when the agent logged off the CRM application. The data allows the call center manager to monitor shift duration and (not shown here) check how long agents are spending in Open-Shift and Close-Shift sessions.

• Check Credit INSTEAD OF Trigger   Check the credit rating of the client. If the credit rating is green, allow the CSR to take the order on account. If the credit rating is red, the CSR must advise the caller that only a money order or credit card can be accepted. The trigger can then return call center scripting (by calling a procedure) that the CSR can read back to the caller.

Step 2: Craft trigger logic or solution in pseudocode   This may be done in any custom or preferred pseudocode. The idea is to outline the trigger and sketch the scope and flow of the trigger.

In the CSR shift log specification just described, the pseudocode could be as follows:

 Get CSR name from Windows security or login service Function get datetime Strip datetime into two values representing a date and the time Write CSR name, date, and time to record

The check credit INSTEAD OF pseudocode could be as follows:

 On open customer record Query customer credit rating color If red then restrict to cash If green then allow to account

Step 3: Model, write, and test trigger against development system   Once all pseudocode is written and you have cross-checked it with IS managers, supervisors, or yourself, the stage is set for modeling and writing the trigger in T-SQL, testing against the development system, checking the performance of the trigger (especially under load), and so on. The next section goes into the actual trigger code.

Step 4: Deploy trigger to target system   This step entails installing the trigger (the job of a DBA) to the target system if your processes have been approved by quality assurance and your trigger testing program. You can copy objects to the production system or script out the code for execution against the target system from Management Studio.

Step 5: Encrypt trigger   Encrypt your trigger in the development system. This is obviously not essential, but it is advisable if the systems might come under attack or cannot be secured. One of my projects entails installing SQL Server in a number of data centers spread throughout the United States where I cannot guarantee that the servers are off-limits or that they are safe from access by unauthorized use of query tools.

Step 6: Verify permissions   You do not install permissions on triggers per se, but you need to verify that users on the connections that fire the triggers have permission either to query the table or to insert, update, or delete from it. This also applies to permissions on a view on which INSTEAD OF triggers are installed. This stage of your trigger deployment plan is critical. When I first started out installing triggers, I was so concerned with the actual trigger and how impressed my clients would be that, after testing it with my super-DBA/ developer rights, I f orgot to make sure the users would be able to access the tables on the production system. SQL Server schema-level security is thus a great improvement in this area.

Creating a Trigger UsingT-SQL

The principal T-SQL statement is CREATE TRIGGER, as follows (the full explanation and usage of the arguments are documented in SQL Server Books Online):

 CREATE TRIGGER [schema_name .]trigger_name ON {table | view} [WITH <dml_trigger_option> [ ,...n]] {FOR | AFTER | INSTEAD OF} { [INSERT] [ ,] [UPDATE] [ ,] [DELETE]} [WITH APPEND] [NOT FOR REPLICATION] AS {sq1_statement [ ;] [ ,...n] | EXTERNAL NAME <method specifier [ ;] >}  <dml_trigger_option> ::=     [ENCRYPTION]     [EXECUTE AS Clause]  <method_specifier> ::=     assembly_name.class_name.method_name CREATE TRIGGER trigger_name ON {ALL SERVER | DATABASE} [WITH <ddl_trigger_option> [ ,...n]] {FOR AFTER} {event_type | event_group} [ ,...n] AS {sq1_statement [ ;] [ ,...n] | EXTERNAL NAME < method specifier > [ ;]}   <ddl_trigger_option> ::=     [ENCRYPTION]     [EXECUTE AS Clause]  <method_specifier> ::=     assembly_name.class_name.method_name

You need to provide each trigger with a name and define it for a particular table or view in a database. You also have the option of encrypting the trigger so that no one (not even you, ever) can look at the original code. Triggers are secured with permissions (see Chapter 6) so that only you, the trigger creator, or the schema, can alter or drop the trigger.

After you have specified which table or view is to be the “beneficiary” of the trigger, you need to define the trigger as an AFTER or INSTEAD OF trigger. This specification may seem a little late in the syntax because you cannot define an AFTER trigger for a view.

Another important argument, NOT FOR REPLICATION, has serious implications in distributed database scenarios. This argument specifies that the trigger should not be executed when replication is the cause of table manipulation (see the chapter that covers replication, Chapter 8).

Following the AFTER or INSTEAD OF argument, you must specify the DML event the trigger fires on. This can be either DELETE, INSERT, and UPDATE. Finally, following the AS keyword, you enter the segment of T-SQL code to be executed every time the DML statement lands on the table or view. So your basic create trigger statement will look something like the following:

 CREATE TRIGGER myTrigger   ON Employees   AFTER, INSERT, UPDATE AS RAISERROR (500289, 16, 10)

This same trigger code specified as an AFTER or INSTEAD OF trigger would look like this in its most basic form:

 CREATE TRIGGER myTrigger   ON Employees     INSTEAD OF INSERT, UPDATE, DELETE AS . . .

Creating and Testing the Trigger

To create a trigger, drill down to the table on which you want to apply the trigger. Expand the table so that the Trigger folder is exposed. Right-click the folder and select New Trigger…. The script for creating a trigger is loaded into a query window. (If you want to add header and revision information, you can edit the template and install new template parameters, as discussed in Chapter 11.)

Write and test your trigger here to a development system or table. When you are ready to install the trigger to a table, all you need to do is extract the script and execute the query to the production table.

To alter the trigger at any time, you can drill down to the trigger as you did to create it and either choose the Modify option or the Script Trigger As option. Both routes load the Alter Trigger code into a query window. To replace the old trigger with the new one, simply execute the query

Note 

You can step through trigger code in the Visual Studio debugger.

To create and manage triggers on views, simply repeat the process just described on the views in SSMS.