Types of Triggers

Types of Triggers AFTER Triggers After triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified only on tables. INSTEAD OF Triggers INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support. INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two virtual tables called Inserted and Deleted. For a DELETE trigger, the virtual table Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. (For INSTEAD OF triggers, the Deleted table holds the rows that would have been deleted and the Inserted table holds the rows that would have been inserted had the modification taken place.) An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version. With all triggers, the Inserted and Deleted tables change their structure to reflect the columns and data types of the table the trigger is attached to. For example, if you have a trigger on the Titles table in the Pubs database, the Inserted and Deleted tables will have the same columns as the Titles table with the same names and data types. Table 11.1 compares an INSTEAD OF trigger with an AFTER trigger. Following is a simple example. Suppose you want to make sure that when someone updates any prices in the Titles table in the Pubs database, the change is no more than 10 percent of the original price. You need to look at both the old price value and the new value to see whether the change is more than 10 percent. You can get the old value from the Deleted table and the new value from Inserted. You must also make sure that the primary key title_id doesn't change so that you can use that key to relate rows in Deleted to the corresponding rows in Inserted. T-SQL Code 1 contains the AFTER trigger that performs the price check. In SQL Server 2000, you can use the word AFTER instead of FOR; the meaning is equivalent. If your application includes both kinds of triggers, you might use the word AFTER for consistency and to clearly distinguish the two kinds of triggers. And triggers you created with the word FOR in previous SQL Server releases will still work. Because the AFTER trigger fires after a client updates the Titles table, the trigger must force a rollback if it encounters an error. If no ROLLBACK TRANSACTION occurs, SQL Server has accepted the change and lets it persist. Table 11.1. Comparison of AFTER and INSTEAD OF triggers Function AFTER trigger INSTEAD OF trigger Applicability Tables Tables and views Quantity per table or view Multiple per triggering action (UPDATE, DELETE, and INSERT) One per triggering action (UPDATE, DELETE, and INSERT) Cascading references No restrictions apply Not allowed on tables that are targets of cascaded referential integrity constraints. Execution After: Constraint processing Declarative referential actions Inserted and deleted tables creation Triggering action Before: Constraint processing In place of: After: The triggering action inserted and deleted tables creation Order of execution First and last execution may be specified Not applicable text, ntext, and image column references in inserted and deleted tables Not allowed Allowed CREATE TRIGGER AFTER_UPDATE_TITLES ON titles FOR update AS -- We will assume that more than one row could have been updated. -- The inserted and deleted tables will have to be joined on their -- primary key, so we can't allow changes to the primary key. IF @@rowcount = 0 RETURN IF UPDATE(title_id) BEGIN PRINT 'updates to primary key title_id are not allowed' ROLLBACK TRAN RETURN END IF UPDATE (price) IF (SELECT MAX(ABS((i.price - d.price)*100/d.price)) FROM inserted i JOIN deleted d ON i.title_id = d.title_id) > 10 BEGIN PRINT 'Price Change not allowed' ROLLBACK TRAN RETURN END GO Now look at the INSTEAD OF trigger that T-SQL Code 2 shows. This trigger fires before the changes happen, but the rows that would have been affected are available in the Inserted and Deleted tables. The basic comparison for price changes greater than 10 percent is the same in this trigger as in the AFTER trigger. CREATE TRIGGER INSTEAD_OF_UPDATE_TITLES ON titles INSTEAD OF update AS IF @@rowcount = 0 RETURN IF UPDATE(title_id) BEGIN PRINT 'updates to primary key title_id are not allowed' RETURN END IF UPDATE (price) BEGIN IF (SELECT MAX(ABS((i.price - d.price)*100/d.price)) FROM inserted i JOIN deleted d ON i.title_id = d.title_id) > 10 BEGIN PRINT 'Too big of a price change' RETURN END -- Because the update passed the tests, we have to -- actually do the update; this section updates only -- the price field; if other columns also changed, -- we would have to account for them. UPDATE t SET price = i.price FROM titles t join inserted i ON i.title_id = t.title_id WHERE t.title_id = i.title_id END RETURN The most difficult aspect of using the INSTEAD OF trigger is determining what to have SQL Server do if it deems the update acceptable. If you want to carry out the intended update, you could delete all the rows that match the rows in the Deleted table and insert all the rows from the Inserted table. But in this case, the Titles table has foreign-key references from other tables, so you can't delete rows from Titles. My simple solution, which the T-SQL Code 2 shows, works on the premise that the price was updated, so the trigger can update the price values in the Titles table based on the Inserted table's contents. If the original UPDATE had set many columns to new values, this trigger would be more difficult to write. Both AFTER and INSTEAD OF triggers prevent updates to the primary key of the Titles table, and both triggers prevent updates to the price column that would change the price by more than 10 percent. Of course, you probably wouldn't have both these triggers on the Titles table; you would choose to have one or the other because you need to validate the price change only once. Core Difference Between INSTEAD OF and AFTER Triggers Besides using the words INSTEAD OF in place of FOR or AFTER, INSTEAD OF triggers behave differently from AFTER triggers. For example, you can have only one INSTEAD OF trigger on each table for each action (INSERT, UPDATE, and DELETE), and you can't set a firing order for INSTEAD OF triggers. (In SQL Server 2000, you can specify which AFTER trigger should execute first and which should execute last.) Also, you can't combine INSTEAD OF triggers and foreign keys with CASCADE on a table. For example, if the Sales table has a foreign-key constraint that references the Titles table and specifies CASCADE as the response to DELETE operations on Titles, you'll get an error message if you try to create an INSTEAD OF trigger for DELETE on Sales. However, you can have INSTEAD OF triggers for INSERT or UPDATE. Similarly, if you already have an INSTEAD OF trigger on Sales, you can't alter the table to add a foreign-key constraint with the CASCADE action for the same data-modification operation. Another difference is that INSTEAD OF triggers can never be recursive, regardless of the setting of the Recursive Triggers database option. For example, if you execute an INSTEAD OF trigger for INSERT into Titles and the trigger performs an INSERT into Titles, the second INSERT won't invoke the INSTEAD OF trigger. Instead, SQL Server will process the INSERT as if no INSTEAD OF trigger existed for INSERT, and any constraints and AFTER triggers will take effect. Although you might think INSTEAD OF triggers and AFTER triggers could be interchangeable in some situations, the purpose and the real power of INSTEAD OF triggers is to let you update a certain class of views that aren't usually updatable. In fact, you can't create an AFTER trigger on a view, but you can create an INSTEAD OF trigger. For example, you usually can't execute DELETE operations on a view that is based on a join. However, you can write an INSTEAD OF DELETE trigger. The trigger has access (through the Deleted table) to the rows of the view that would have been deleted had the view been a real table. Similarly, in an INSTEAD OF UPDATE or INSTEAD OF INSERT trigger, you can access the new rows through the Inserted table. Which Trigger to Pick? The AFTER trigger might appear less efficient because it sometimes needs to undo work that has already been done. If you think your table will have numerous violations that your trigger will need to correct, you might want to choose the INSTEAD OF trigger. However, if the vast majority of your updates will be acceptable, the INSTEAD OF trigger will have more work to do and thus be less efficient. In the case of AFTER triggers, the contents of the Inserted and Deleted tables are directly available from the transaction log. By the time the AFTER trigger executes, the data modification has happened, SQL Server has logged the changes, and the changed records are available internally to SQL Server. In fact, when you use AFTER triggers, you can think of the Inserted and Deleted tables as views of the transaction log. When INSTEAD OF triggers fire, SQL Server hasn't yet made any changes and consequently hasn't logged any changes. SQL Server builds worktables to hold the inserted and deleted records as if the modification had occurred. Then, if the modification takes place, SQL Server must make the changes and log them, adding to the work of creating the worktables. Besides the extra work, another reason INSTEAD OF triggers are the less desirable type has to do with updates, such as those in the price-changing example. If many of the table's columns could be included in the SET clause in the UPDATE statement, the trigger would be cumbersome to write, and each column would have to be checked to see whether it had a different value in the Deleted or Inserted tables. Designing INSTEAD OF Triggers The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable to support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed. An INSTEAD OF trigger can take actions such as ignoring parts of a batch, not processing a part of a batch and logging the problem rows, and taking an alternative action if an error condition is encountered. (Note: INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with a DELETE or UPDATE action.) Coding logic as part of an INSTEAD OF trigger prevents all applications accessing the data from having to reimplement the logic. In the following sequence of Transact-SQL statements, an INSTEAD OF trigger updates two base tables from a view. In addition, two approaches to handling errors are shown. (1) Duplicate inserts to the Person table are ignored, and the information from the insert is logged in the PersonDuplicates table. (2) Inserts of duplicates to the EmployeeTable are turned into an UPDATE statement that retrieves the current information into the EmployeeTable without generating a duplicate key violation. The Transact-SQL statements create two base tables, a view, a table to record errors, and the INSTEAD OF trigger on the view. These tables separate personal and business data and are the base tables for the view. CREATE TABLE Person ( SSN char(11) PRIMARY KEY, Name nvarchar(100), Address nvarchar(100), Birthdate datetime ) GO CREATE TABLE EmployeeTable ( EmployeeID int PRIMARY KEY, SSN char(11) UNIQUE, Department nvarchar(10), Salary money, CONSTRAINT FKEmpPer FOREIGN KEY (SSN) REFERENCES Person (SSN) ) This view reports all relevant data from the two tables for a person: CREATE VIEW Employee AS SELECT P.SSN as SSN, Name, Address, Birthdate, EmployeeID, Department, Salary FROM Person P, EmployeeTable E WHERE P.SSN = E.SSN You can record attempts to insert rows with duplicate social security numbers. The PersonDuplicates table logs the inserted values, the name of the user who attempted the insert, and the time of the insert. CREATE TABLE PersonDuplicates ( SSN char(11), Name nvarchar(100), Address nvarchar(100), Birthdate datetime, InsertSNAME nchar(100), WhenInserted datetime ) The INSTEAD OF trigger inserts rows into multiple base tables from a single view. Attempts to insert rows with duplicate social security numbers are recorded in the PersonDuplicates table. Duplicate rows in the Employee Table are changed to update statements. CREATE TRIGGER IO_Trig_INS_Employee ON Employee INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON -- Check for duplicate Person. If no duplicate, do an insert. IF (NOT EXISTS (SELECT P.SSN FROM Person P, inserted I WHERE P.SSN = I.SSN)) INSERT INTO Person SELECT SSN,Name,Address,Birthdate,Comment FROM inserted ELSE -- Log attempt to insert duplicate Person row in PersonDuplicates table. INSERT INTO PersonDuplicates SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE() FROM inserted -- Check for duplicate Employee. If no duplicate, do an insert. IF (NOT EXISTS (SELECT E.SSN FROM EmployeeTable E, inserted WHERE E.SSN = inserted.SSN)) INSERT INTO EmployeeTable SELECT EmployeeID,SSN, Department, Salary,Comment FROM inserted ELSE -- If duplicate, change to UPDATE so that there will not -- be a duplicate key violation error. UPDATE EmployeeTable SET EmployeeID = I.EmployeeID, Department = I.Department, Salary = I.Salary, Comment = I.Comment FROM EmployeeTable E, inserted I WHERE E.SSN = I.SSN END Summary In this chapter we covered the topic of indexed views and the advantages of using them and the differences between the indexed views and the traditional AFTER views. Triggers The constraints we've discussed to this point?NOT NULL, CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE?are known as declarative constraints. Triggers are not declarative because they contain procedural code. They're slightly less pure in the relational sense, and early DBMSs didn't support them, but today seven of the Big Eight support both declarative constraints and triggers. Portability The exception is MySQL, which supports only PRIMARY KEY and UNIQUE as declarative constraints, and doesn't support triggers at all. The gains shown throughout this section are for only seven DBMSs. In our tests, we found that triggers are slower than declarative constraints. For example, firing this trigger with a compound SQL statement: CREATE TRIGGER Trigger1 AFTER INSERT ON Table1 REFERENCING NEW ROW AS New FOR EACH ROW BEGIN IF New.column1 NOT BETWEEN 1 AND 10 THEN SIGNAL SQLSTATE = '23000' END IF END is slower than leaving the checking up to the CHECK constraint in this table definition: CREATE TABLE Table1 ( column1 SMALLINT, CHECK (column1 BETWEEN 1 AND 10) ...) GAIN: 4/7 Portability Because triggers were around long before the SQL Standard got around to requiring them, every DBMS uses slightly different syntax to define triggers. A discussion of the differences is beyond the scope of this book. However, before glibly saying "declarative constraints are better than triggers," we should note that the trigger has flexibilities that might be more important to you than performance. For example, a trigger statement can log an incident and return a customized warning, while all a declarative constraint can do in the same instance is return an error with a fixed error message. An old saw, which we've never heard before, goes?The more inflexible the server is, the more adaptable the client must be. Nevertheless, the trigger involves so many steps that it can't be fast. Consider the steps shown in Listing 10-3. Listing 10-3 Steps involved in executing a trigger A trigger must: Make a savepoint. For each row: { Perform "before each row" trigger code. Copy the row as it exists before update, to a BEFORE IMAGE. Update the row. Copy the row as it exists after update, to an AFTER IMAGE. } At end of statement (after constraints are checked): { For each row: { Perform "after each row" trigger code. If (fatal error) restore to savepoint. } Cleanup. } Listing 10-3 shows that a trigger does two "for each row" loops. In theory the DBMS doesn't have to do the second loop if the trigger is only "before each row." In practice, though, before- and after-triggers are equally fast. At this time, there is support only for INSERT, UPDATE, and DELETE triggers (except with Informix, which also provides SELECT triggers). To make a SELECT trigger, you can employ a trick that works like this?Suppose you want to keep a log of each time that a row from Table1, which contains the string WASHINGTON in a column named city, is retrieved. The solution is to mandate that all selections must go via a view of Table1, called View1, and to define View1 with an external function call. The SELECT statement would thus look like this: SELECT * FROM View1 WHERE city = 'WASHINGTON' The CREATE VIEW statement would look like this: CREATE VIEW View1 AS SELECT Table1.*, Function1(city) AS Function_return FROM Table1 And the CREATE FUNCTION statement would look like this: CREATE FUNCTION Function1 (parameter1 CHAR(10)) RETURNS SMALLINT LANGUAGE C PARAMETER STYLE GENERAL NOT DETERMINISTIC RETURN NULL ON NULL INPUT NO SQL EXTERNAL Finally, the external function code would look like Listing 10-4. Listing 10-4 External function code for SELECT trigger short* __export __cdecl FUNCTION1 (char* c) { static short return_value; HANDLE FileHandle; HANDLE MutexHandle; unsigned long ret; if (lstrcmp(c,"Washington")==0) { MutexHandle=CreateMutex(0,TRUE,"MUTEX"); FileHandle=CreateFile( "T", GENERIC_WRITE, 0, 0, OPEN_ALWAYS, FILE_ATTRIBUTE_NORMAL, 0); SetFilePointer(FileHandle,0,0,FILE_END); WriteFile(FileHandle,"it happened",11,&ret,0); CloseHandle(FileHandle); CloseHandle(MutexHandle); return_value=ret; } else return_value=0; return (&return_value); } The matter is not simple?you must persuade the DBMS that the function is unpredictable and can change the column value. Otherwise, the DBMS will optimize the function call and only do it once. To persuade the DBMS not to optimize, write the function in a language the DBMS doesn't understand natively, make the column input/output (in fact the column isn't touched), and above all declare the function as NOT DETERMINISTIC. A word of caution about NOT DETERMINISTIC functions. In real triggers, it is possible to call stored procedures with nondeterministic functions, but there is a peril. Our favorite one triggers an action that depends on the time of day, because "for some reason" time of day can't be used in a CHECK clause. Well, some people would think that reason is important?suppose the database becomes corrupt, and the recovery procedure involves rerunning a log of transactions. In such a case, the recovery isn't run at the same time of day as the original transaction?and thus a transaction that succeeded before the crash will fail during the recovery. Remember that some DBMSs handle functions in select lists when they open the cursor; others handle them when they fetch from the cursor. The Bottom Line: Triggers If speed is your only concern, use declarative constraints instead of triggers. Use views with an external function call to write a SELECT trigger. Declarative constraints differ from triggers in that constraint conditions guarantee that data always fulfills a specific requirement, while trigger conditions are enforced only when data changes. That is, constraints are passive requirements for correctness, while triggers are responses only to specific actions. Because trigger checking happens after constraint checking, it's tricky to change the value of a table column within the trigger. That could lead to loops (because the change would cause another constraint check) or to subversions (because the change wouldn't go through the same checking that a regular data-change statement would go through). The SQL Standard has special rules that are designed to prevent such tricks. Triggers fire on INSERT, UPDATE, or DELETE statements but not on "implied" inserts like the DBMS's nonstandard database LOAD statement and not on "implied" deletes like the DBMS's nonstandard table TRUNCATE statement.