SQL Server triggers

From:http://www.cnblogs.com/kissdodog/p/3173421.html

Triggers can do a lot of things, but it also brings a lot of problems. The trick to using it is to use it at the right time, rather than using it at an inappropriate time.

Some common uses for triggers are as follows:

• Elastic referential integrity: Implements operations that many DRI cannot implement (for example, referential integrity across databases or servers, and many complex relationship types).
• Create a divine trace: This means that the written record not only tracks most of the current data, but also includes the historical data that was actually modified for each record. With the advent of change data tracking in SQL Server2008, creating an audit trail is no longer popular, but it used to be a trigger.
• Perform functions similar to check constraints, but across tables, across databases, and even across servers.
• Use your own statement instead of the user's action statement.

First, the concept of triggers

A trigger is a special type of stored procedure that corresponds to a specific event. There are two types of triggers: Data definition language (DDL) triggers and data Manipulation language (DML) triggers.

DDL triggers respond when the user modifies the database structure in some way (CREATE, ALTER, drop, or similar statement). In general, DDL triggers are only used when there is a very rigorous audit of the database structure or history.

DML triggers are snippets of code that are attached to a particular table or view. Unlike stored procedures that require an explicit call to code, the code in the trigger runs automatically whenever there are additional triggers that occur in the table. You cannot actually invoke the trigger explicitly-the only way to do so is to perform the desired action in the specified table.

In addition to not being able to explicitly invoke a trigger, you can also discover what two other triggers do not have in the stored procedure: parameters and return codes.

What events can I attach a trigger to? Because you can use 3-class action queries in SQL, there are 3 types of triggers, plus a mix of trigger types that mix and match these times and are activated for time timing.

• Insert Trigger
• Delete Trigger
• UPDATE trigger
• Any type of hybrid later

Attention:

It is important to note that sometimes the trigger is not activated even if the action being performed is one of the preceding types. The question is whether the action being performed is in the recorded activity. For example, a DELETE statement is a normal record activity that activates any delete trigger, and TRUNCATE table has the effect of deleting rows, but only frees the space used by the table-no single row deletion is recorded, so no triggers are activated. Bulk operations do not activate the trigger by default, and you need to explicitly tell the bulk operation to activate the trigger.

Syntax for creating triggers:

CREATE TRIGGER <trigger name> on [<schema name>.] <table or view name> [with Encryption | EXECUTE as <caller | Self | <user> >] {{{for | After} < [delete][,][insert][,][update] >} | INSTEAD of}[with Append][not for REPLICATION] as < <sql statements> | EXTERNAL NAME <assembly Method specifier> >

The ON clause is used for the table where the trigger will be attached and when to activate the trigger.

　　1. ON clause

This section simply names the object on which the trigger was created. Remember, if the type of trigger is an after trigger (using a for or after to declare a trigger), then the target of the ON clause must be a table-after the trigger does not support the view.

　　2. With ENCRYPTION option

Encryption triggers. If you add this option, you can make sure that no one can see your code (or even yourself). and views as with stored procedures, using the WITH encryption option is important to remember that the option must be re-applied each time an ALTER statement is used on the trigger, if you use the ALTER statement statement but do not include the WITH ENCRYPTION option, Then the trigger is no longer encrypted.

　　3, for| After clause and instead of clause

In addition to determining the type of query that activates the trigger (INSERT, UPDATE, DELETE), make a selection of the trigger's activation time. Although people often consider using a for trigger, you can also use the instead OF trigger. The choice of two triggers will affect whether the trigger is entered before or after the data is modified. The meaning of for and after is the same.

　　for| After

The for (or after) clause indicates what type of action the trigger is expected to activate. Triggers can be activated when there is an INSERT, update, or delete or triple blending operation.

For Insert,delete--or: for Update,insert--or: for DELETE

　　1. Insert Trigger

When someone inserts a new row into the table, the code for the trigger marked for INSERT executes. for each row that is inserted, SQL Server creates a copy of the new row and inserts the copy into a special table that exists only within the scope of the trigger, which is known as the inserted table. It is important to note that theinserted table only exists when the trigger is active. It is considered that the representation does not exist before or after the trigger is opened.

　　2. Delete Trigger

It works the same way as an insert trigger, except that inserted is empty (after all, it is deleted rather than inserted, so there is no record for inserted). Instead, a copy of each deleted record is inserted into another table, called the deleted table, similar to the inserted table, which only exists during the time the trigger was activated.

　　3. Update Trigger

Except for a little change, the update trigger is very similar to the previous trigger. When you modify an existing record in a table, the code for the trigger that is declared for update is activated. The only change is that there is no update table. SQL Server considers each row as if it had deleted an existing record and inserted a new record. the trigger declared for update does not contain only one table, but two special tables, called inserted tables and deleted tables. Of course, the rows of the two tables are exactly the same.

　　4. With APPEND option

With APPEND option is not commonly used, to be honest, the likelihood of using it is very small; the WITH APPEND option can only be applied in 6.5 compatibility mode.

If you have declared a trigger called Trgcheck to enforce data integrity when updating and inserting, you cannot create another trigger for cascading updates. Once you create an update (or INSERT, delete) trigger, you cannot create another trigger of the same action type. To solve this problem, the WITH APPEND clause explicitly tells SQL Server that you can add a new trigger even if you already have this type of trigger on the table. When an appropriate trigger action (INSERT, UPDATE, DELETE) occurs, two triggers are activated at the same time.

　　5. Not FOR REPLICATION option

If you add this option, the rules about when to activate the trigger are slightly changed. Using this option in the appropriate location does not activate the trigger, regardless of when the replication-related task modifies the table. Typically, triggers (operations such as housekeeping or cascading) are activated when the original table is modified and no further modifications are made.

　　6. As clause

is exactly the same as it is used in stored procedures, which is exactly what triggers are. The AS keyword tells SQL Server that the code will start.

Ii. implementing data integrity rules using triggers

Although triggers are not the first choice, triggers can perform the same functions as check constraints and even default constraints. Use a trigger or a check constraint? The answer is: it depends on the situation. If a check constraint can be completed, then a check constraint may be a more favored choice. However, there are times when a check constraint cannot complete a task, or some intrinsic content in the check process makes it more desirable than a trigger.

Examples of triggers that you want to use instead of check constraints include:

• Business rules need to refer to data in a single table.
• Business rules need to check for changes in updates.
• A custom error message is required.

　　1. Processing requirements from other tables

Check constraints are fast and effective, but they are not omnipotent. Perhaps when you need to cross-table verification, its biggest drawback is exposed.

To demonstrate a cross-table constraint, the new two tables are used for testing:

　　

The foreign key column here is ProductID. What we want to test here is that when the Product table Pruductnumber (inventory, Word does not write) is less than or equal to 0, it is not allowed to add 1 product orders.

Create a trigger as follows:

CREATE TRIGGER Productnumcheck on [Order] for INSERT as DECLARE @i int. SELECT @i = ProductId from Inserted        --inse RTed represents the last inserted record of the table IF (select ProductNumber from Product WHERE ProductId = (select ProductId from Inserted)) <=0 Prin　　T @i BEGIN    PRINT ' out of stock, no buy! '    ROLLBACK TRANSACTION    --rollback to avoid inserting END

Now let's add a product:

INSERT into [Order] VALUES (3,2,getdate ())

The message is displayed as follows:

　　

We see that when product inventory is low, orders are not allowed to be added.

　　2. Use triggers to check for changes in updates

Sometimes, you may not care about the value of the past and the current value, just want to know the amount of change. Although no columns or tables give this information, you can use the inserted table and the deleted table in the trigger to calculate. 、

For example, the product table just now assumes that the product inventory is modified when the order is placed, and we do not allow more than 10 update products at a time.

CREATE TRIGGER productnumupdate on Product for UPDATE as IF EXISTS (SELECT * from Inserted as I INNER JOIN Deleted as D　　On i.productid = D.productid WHERE i.productnumber-d.productnumber >) BEGIN    PRINT ' more than 10, not allowed to update ';    ROLLBACK TRANSACTION    --rollback to avoid inserting END

When you add more than 10 articles,

UPDATE Product SET productnumber = productnumber + one WHERE ProductId = 1

The results appear as follows:

　　

When you add less than 10 bars,

UPDATE Product SET productnumber = productnumber + 1 WHERE ProductId = 1

The results appear as follows:

　　

3. Use triggers for custom error messages

It is convenient to use triggers when you want to control error messages or error numbers that are passed to a user or client application.

For example, if you use a check constraint, you can only get a standard 547 error, and there is no exhaustive explanation. Typically, this is useless information for users who want to know about specific errors-missing, and client applications often fail to make only and helpful responses on behalf of users because they do not have enough information.

In short, a trigger can be created when data integrity is already available, but there is not enough information to process it.

Attention:

Although it is useful to pass custom error codes, there is a relatively small demand for custom error messages in SQL Server. Why not pass a custom error message? The reason is that some users think that there is an application layer on top of the custom error message and may need more contextual information about the error, so SQL Server-specific text is not fully functioning. At this point, if you use a specific error code, it can be helpful for the application to determine exactly what happened and to apply the correct client error handling code.

Iii. Common uses of triggers

1. Triggers can be nested

Nested triggers are triggers that are not activated directly by the issuing statement, but are activated by statements issued by another trigger.

This actually causes a sequence of events, one trigger activates another trigger, and the other triggers another.

The depth at which a trigger can be activated depends on several factors:

• Whether the nested trigger has been opened in the system (this is a system-level, not a database-level option; You can set it by using sp_configure, which is open by default).
• Whether there is a nesting depth of no more than 32 layers.
• Whether the trigger has been activated. The trigger defaults to each trigger transaction being activated only once. Once activated, the trigger ignores any other calls and makes them part of the same trigger action. Once a completely new statement is executed, the process begins again.

Note that if the rollback operation is performed anywhere in the nested chain, the entire chain is rolled back. In other words, the entire trigger chain is like a transaction.

2. Trigger can be recursive

What is a recursive trigger? If a trigger does something that eventually activates itself, then the trigger is recursive. Can be triggered directly (by setting the trigger's table for the action query to complete), or can be triggered indirectly (through the nesting process).

Recursive triggers are relatively uncommon, and by default, recursive triggers are turned off. Recursion is a database-level option that can be set using the sp_dboption system stored procedure.

The risk of a recursive trigger is that it may fall into some non-predetermined loop. This ensures that the process can be stopped in the form of recursive checks when necessary.

　　3. Triggers do not prevent changes to the architecture

Triggers Help to modify the architecture more easily. In fact, it is common to use triggers to enforce referential integrity early in the development cycle, but later, when you want to enter a production environment, change it to DRI.

　　4. You can turn off the trigger without deleting it

Sometimes, like check constraints, you want to turn off the integrity feature to make it easier to perform actions that violate constraints but are effective (the most common is to import data).

You can use the ALTER statement to close the trigger with the following syntax:

ALTER TABLE <table name>    <enable| disable> TRIGGER <all|<trigger name>>

If the trigger is turned off to import data, it is recommended that all users be kicked out and into single-user mode. Dbo-only mode, or enter both modes simultaneously. This way, when the trigger is closed, it is guaranteed to be foolproof.

　　5. Activation sequence OF triggers

For any given table (only after triggers can specify the order of activation), the given view (only instead OF triggers can specify the order of activation). You can select a trigger priority activation (first only one). Similarly, you can select a trigger to be activated (last, only one is selected). There is no priority activation order between all the other triggers, that is, the order of the None trigger is not guaranteed except for first and last activation.

The first and last triggers are created the same as any other triggers, using the stored procedure sp_settriggerorder to declare the activation order after the trigger has been created.

The sp_settriggerorder syntax is as follows:

    sp_settriggerorder[@triggername =] ' <trigger name> ',    [@order =] ' {first| Last| NONE} ',    [@stmttype =] ' {insert| update| DELETE} '    [, [@namespace =] {' DATABASE ' | ' SERVER ' | NULL}]

There can only be a unique first trigger for any special operation (INSERT, UPDATE, DELETE). Similarly, there can only be a unique last trigger for any particular operation. The number of other triggers can be seen as none-that is, there is no limit to the number of triggers that do not have a special activation order.

　　

　　Why to control the activation order

1. Control the order of activation for logical reasons

Why activate another trigger before activating a trigger. The most common reason is that the first trigger is the basis of a subsequent trigger or a preceding trigger that makes the subsequent trigger valid.

2. Control activation sequence for performance reasons

In terms of performance, first triggers are the only key trigger, and if there are multiple triggers, but only one of them can produce a rollback, then you need to consider marking the trigger as a first trigger, which can make the outer rollback less of an operation.

Iv. Performance Considerations

　　1, the passive type of trigger

This means that the trigger occurs after the transaction. When the trigger is activated, the entire query is already running and the transaction has been logged (but not committed, just to the statement point where the trigger was activated). This means that if a trigger needs to be rolled back, all the work already done must be undone. This is different from constraints, where constraints are active and constraints occur before statements are actually executed. This means that the constraint detects actions that might fail, and blocks them early in the process. So constraints usually run faster-faster in more complex queries. Note that the constraint is significantly faster only when a rollback occurs.

If a small number of rollbacks are being processed, and the affected statements are less complex, and the execution is shorter, there is not much difference between the trigger and the constraint. However, it is more efficient to persist in using constraints when the number of rollbacks is not predictable.

　　2. There is no concurrency problem between the trigger and the active process

If the activation statement is not part of the display of a transaction, the statement is still part of its own but statement transaction. In either case, the rollback TRAN emitted inside the trigger will still roll back the entire transaction.

Another consequence of this same transaction is that the trigger inherits the locks that have been opened on the transaction to which they belong. This means that no special processing is required to avoid locks created by other statements in the transaction. It is freely accessible within the scope of the transaction and can be discovered based on modifications made by previous statements in the transaction.

　　3. Use if UPDATE () and Columns_update ()

In an update trigger, you can limit the amount of code that executes in a trigger by checking that the column of interest is modified. To achieve this, you can use the UPDATE () or column_update () function.

　　1. UPDATE () function

The UPDATE () function applies only within the scope of the trigger. Its sole purpose is to provide a Boolean value that indicates whether the special column has been updated. Use this function to determine whether a particular block of code needs to be run-for example, the code runs only when a particular column is updated.

Build a table as follows:

　　

Create the trigger as follows:

CREATE TRIGGER UpdateCheck on Tb_money for update as if update (Mymoney)-    -If update Mymoney is triggered begin    PRINT (' My money changed　　Changed! '); END

Execute statement:

UPDATE Tb_money SET Mymoney = ' 101 ' WHERE Id = 1--changed Mymoney activated trigger

The output is as follows:

　　

Notice that the Mymoney column was changed and the trigger was activated.

Execute statement:

UPDATE Tb_money SET Name = ' Zhang Fei ' WHERE Id = 1

The results appear as follows:

　　

2. Columns_update () function

　　This function and update () run differently, but with the same purpose. The Columns_update () function can check multiple columns at once. To achieve this, the function uses a bitmask that associates a single bit in one or more bytes of the varbinary data with a single column in the table.

　　

For the case, a single byte of data indicates that the 2nd, 3rd, and 6th columns have been updated, while the other columns are not updated.

For cases with more than 8 columns, SQL Server adds another byte to the right and continues counting.

　　

For, this time is with the heart of the 2nd, 9th and 14th column.

How does this information work?

• | Represent or
• & Express and
• ^ Denotes xor or

Example:

Column_update () >0 checks if a column has been updated.

Column_update () ^21=0 checks whether all specified columns (1, 3, 5) have been updated.

Or the table just now:

　　

Create the trigger as follows:

CREATE TRIGGER UPDATECHECK2 on Tb_money for update as if columns_updated () &7 = 3    --If Name,mymoney is updated at the same time to trigger BEG　　    in PRINT (' My money and name changed! '); END

Execute the statement with the following description:

Update Tb_money Set name = ' Zhang Fei ' where id = 1 UPDATE tb_money SET name = ' Zhao Yun ', Mymoney = 102 WHERE id = 1    --This row activates the trigger --The calculation process is as follows--id    name    tb_money--1        1        1    7 (all updated to 7)--0        1        1    Name and Tb_money are simultaneously updated to (with 3=3 )

5, try not to roll back in the trigger

If you use a lot of rollback TRAN statements in a trigger, be sure to pre-check for errors before executing the statement that activates the trigger. SQL Server is passive in this case, but you can take the initiative. Time to check for errors instead of waiting for rollback.

Because the cost of rolling back is expensive.

V. Delete a trigger

The delete trigger and the normal delete operation are slightly different, and the problem with the table is that the name of the trigger is limited to the pattern level. This means that a trigger can have two objects with the same name, as long as the object in the way trigger is in a different pattern than the trigger another object with the same name. Once again, the trigger is named in the pattern in which it is located, not the object associated with the trigger.

The syntax for deleting triggers is as follows:

DROP TRIGGER [<schema>.] <trigger name>

In addition to schema issues, deleting a trigger is as easy as removing other objects.