| Downloads |
|---|
| 103299.zip |
SQL Server 2005 introduced the EVENTDATA function. When called from within a Data Definition Language (DDL) trigger, this function generates an XML string that provides details (e.g., login name, host name, object name) about the statement that caused the trigger to fire. However, you can't use the EVENTDATA function within a Data Manipulation Language (DML) trigger. This limitation makes it difficult to filter UPDATE and other DML statements based on the details of the triggering statement, such as the login or host name. Because of this limitation, I created a workaround.
The easiest way to understand the workaround is with a simple example, which the script in Listing 1 demonstrates. (You can download Trigger-Test.sql by clicking the Download the Code Here link near the top of the page.) This script provides two levels of filtering:
- The script ignores UPDATE statements from a specific computer (srv-dba01).
- UPDATE statements run from other computers against the specified table (TableA) can only update a specific column (Name).
Let's look at the script in more detail. Callout A in Listing 1 creates a sample database called Trigger-Test, creates a table within it called TableA, and populates the table with three rows. The code in callout B creates an UPDATE trigger on TableA. Using @@SPID within the trigger will reveal the server process ID (SPID) of the session that’s running the UPDATE statement. Once you know the session's SPID, you can use it to look up other details (e.g., login name, host name) in the sys.sysprocesses or sys.dm_exec_sessions table. You can then use this information, for example, with a simple IF condition to rollback the update initiated from a specific host name such as srv-dba01. Also, you can use the UPDATE() function, which is operational only within a trigger, to verify if the updated column is the one in which updates are permitted.
Now that you know how the script works, let's look at some sample results. If you run the following UPDATE statement from srv-dba01 against the newly created table, it will produce the error message shown in Figure 1:
Running the UPDATE statement
against the newly created table from any other machine (i.e., not srv-dba01) will produce the error message shown in Figure 2.
The error message is the result of the UPDATE() function of the name column, and it will prevent an ID or any other column from being updated. The only column that’s updatable in the UPDATE statement
is Name, as Figure 3 shows.
Using this technique, UPDATE, INSERT, and DELETE statements can be filtered by login name, host name, and more. This functionality ultimately gives you more control over queries.
