trigger

trigger

1. The concept trigger of the trigger is a special type of stored procedure. SQL Server will automatically perform triggers when data is modified as data in the table. Use triggers to implement more complex data integrity constraints. In SQL Server, the tables of the database can be declared with reference integrity constraints, namely primarykey, and foreignKey constraints. Simple reference integrity in the same database is usually implemented by primary keys and foreign key constraints. However, since the declaration reference integrity constraint cannot refer to the object in other databases, the reference integrity constraint between the database is only implemented by the trigger. Reference integrity constraints and triggers are referenced, and data operation constitutes a dual validity constraint. When a table with a constraint and the trigger is simultaneously modified, SQL Server will perform constraint check and then perform the trigger check. If the operation of these statements conforms to constraints, the system will complete the data operation, then activate the trigger; if the statement does not pass the constraint check, the data operation statement will not be executed, which is not possible to activate the trigger. If a statement passes the constraint check, the trigger will also cancel the statement that has been completed. The main advantage of the trigger is that any one of the operations modified in the trigger table (including the modification of the operator entry data or other applications) can automatically activate the trigger, which triggers integrity checking for these operations. . (1) The inspection and operation of the trigger can be implemented is more complicated than the primary key and foreign key constraints, CHECK constraints, and rule objects. For example, the CHECK constraint can only check the validity of the specified column value according to the other column values ​​in the same table or the other column values ​​in the same table, and when using the trigger, you can refer to the column values ​​in other tables. (2) The trigger is established at the table level, which corresponds to a specific data modification event. All three operations of INSERT, UPDATE, and DELETE may cause data to modify, so the triggers in SQL Server can be divided into three types into INSERT triggers, UPDATE triggers, and delete triggers. Moreover, the SQL Server 2000 version allows the same data object to modify the event, and the same data operation defines multiple triggers, which is disabled in the previous version before SQLServer 7.0. The trigger can only be created by the database owner. Because the relationship between the table's access method and its objects is also changed when the trigger is created in a table or the column of a table. That is, the creation of the flip-flop changes the database mode, so the right to create a trigger should be reserved to the owner of the database to prevent ordinary users from being modified to modify the system pattern. 2. Create trigger of the trigger creates the CREATE TRIGGER statement in Transact-SQL, and the statement should contain the defined trigger-based table, activating the data operation event name of the trigger, and the corresponding operation to be performed by the trigger . The CREATE TRIGGER statement does not allow with parameters, nor is it not allowed to be called directly, but can only be automatically activated by the system. Create Trigger definition trigger has two formats. The first format defines the triggering event of the trigger to INSERT, UPDATE, DELETE, etc. The grammatical format is as follows: create trigger [oowner,] trigger_name on [oner,] table_name [with encryption] {for {[insert] [, update] [, delete]} [not forrePlication] AS SQL_STATEMENTS} in the above statement: 1 Trigger_name: For the created trigger name, it must comply with the SQL Server naming rules, and the same database is not allowed to appear the same. 2 TABLE_NAME: Table based on the trigger defined in this statement, also known as trigger table.

8 with encryption option: SQL Server saves the definition text of the trigger in System Classification Table SysComments, select SQL Server to encrypt the trigger to define text to prevent third-party users from reading the trigger from the SysComments table. Defines the content of the text. 4 NOT for Replication option: Description When modified during the replication process, the trigger is not activated. 5 for {[INSERT] [, UPDATE] [, Delete]}: Define the trigger event of the trigger. When a trigger is triggered by multiple events in the table, you can use "," symbols as the interval between INSERT, UPDATE, DELETE. 6SQL_STATEMENTS: Defines the operation to be performed when the trigger is triggered (when activated). The triggered event in the second definition format of the trigger is limited to INSERT and UPDATE. Its grammatical format is: create trigger [owner,] trigger_name on [oer,] table_name [with encryption] {for {[INSERT] [, UPDATE]} [not for replication] as {if update (column) [{i Update (color) [{ } Update (Column)] | if (Columns Updated () {bitwise_operator} Updated_bitmask {comparison_operator color column_bitmask} SQL_Statements}} In the second format, you need to use the IF clause to further illustrate the trigger condition of the trigger is limited to the IF statement specified. The column value is modified. IF clauses The modified column specified by the modified column has the following format: if Update (Column) [{AND | OR} Update (Column)] and IF (Column_updated () {bit operator) Updated_bitmask (L [ Compared to COLUMN_BITMASK) Where: 1Column: Indicates that the data will be modified by INSERT or UPDATE in trigger conditions. 2Updated bitmask: Defines the bit shielding value of you. 8Column Bitmask: Define all pending columns The value of the bit shield value. The mask of each column is the serial number of the column in the trigger table. 4Columns_updated (): is used to detect whether the specified column value is inserted or modified. Requires the operator, comparison operator and Updated_bitmask (Position shield value), several parameters such as column__bitmask (value of bit shield values ​​of each pending column) are explained together. If there are 5 to be detected in a trigger table, each column corresponding to the block From 1 to 5, the corresponding bit shield value is 20 to 24. For example, if the IF clause of the column value to detect column 1 and column 3 is: IF (Column Updated () & (1 4)) <> 0 Note: Pay attention to two points when using the with Encryption option: First, the original trigger The file loss will not be re-saved from the SysComments table; Second, after the text is encrypted, you cannot modify when the database is upgraded to a new version, and it cannot be re-deposited into the new version. That is, if the trigger text may need to be modified, do not encrypt it casually.

3. Use, limit, and nested nested when the trigger is executed, two special temporary tables: INSERTED and DELETED are generated. They are the same as the trigger table, for test trigger conditions, and users cannot change directly. SQL Server When performing an Insert statement, SQL Server is inserted into the new record line of the trigger table. Inserting the INSERTED table: When executing the DELETE statement, the row to be removed will be deleted in the deleted table. When executing an UPDATE statement, remove the old row from the table and insert the deleted row into the deleted table. Then insert a new row and insert the new line into the inserted table. Tip: Before using the trigger, sometimes you need to check the global variable @@ rowcount to determine the status before using the information. Because most triggers affect data in multi-line or even multiple tables. Insert a NULL value to a column or use the default key when assigning a column, will activate the trigger. If you do not provide a column value for a column when using an Insert statement, the flip-flop is activated when an implicit NULL value or default value is inserted into the column. Tip: For a certain data operation statement, even if the multi-line data is affected, the same trigger can only be activated once. 3.1 Creating a trigger 1. Created in Enterprise Manager. 2. Execute the SQL statement creation in Query Analyzer. 3.2 INSERT Trigger INSERT and UPDATE triggers are often used to detect columns of the triggers and whether their data meet the defined rules. They can make a constraint check that cannot be completed when defining reference integrity before the data input table. Below the student database Student as the example to introduce the use of the INSERT trigger. The database consists of three tables, which describe the "student file" table of the student situation, describing the "student grade" table of student achievements. Describe the "packet case" table of the packet condition. Student Archives Table Sample S-ID Size Name Gender Class Group 1 001 Wang Xiaoyong Male First Twenty Class 1 2 002 张柳 女 初 二 二 1 3 003 Purple Yunfei Female First Twenty Class 2 4 004 Huang Tianlong Men Junior Two classes 1 student transcript sample S_ID account score 1 language 67 1 Mathematics 88 1 English 98 2 language 45 2 Mathematics 89 2 English 83 3 Chinese 94 3 Mathematics 78 4 English 81 Group Situation Class Group The number of people first two first class 1 2 2 1 2 1 1 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3 into the "Student Archive" table created an Insert trigger INSTRG, and its role is to insert a new line to the "Student File" table every new student. The number of people in the group is automatically increased in the "Packet Act" table.

Use studentgo Create Trigger Instrg On [DBO]. [Student Archives] for Insertas Declare @ 级 VARCHAR (50), @ 小 v vytselect @ 人 = INSERTED. Class, @ 小 = inserted. Group from Inserted IF EXISTS (SELECT number from group case where @ 级 = group situation. Class and @ 小 = group situation. Group) Begin - BG1 Select @ = number of people from packet Where @ class = group situation. Class And @ 小 = grouping situation . Group SET @ 人 = @ 人 1 UPDATE Packet situation set packet situation. Number = @ 人 WHERE @ class = Grouping situation. Class and @ 组 = Packet situation. Group END - BG1ELSE BEGIN - BG2 INSERT INTO Packet situation ( Class, group, number of people) VALUES (@ class, @ 小, 1) end - b2 should be considered as much as possible when designing the trigger, otherwise the execution result is incorrect. In the above example, a trigger temporary table INSERTED is used. When inserting a new row in the "Student File" table, the trigger is activated and will automatically detect the new increase in the Inserted table, and increase the number of people in the "packet case" table according to the class and the group number. Test SQL statement: INSERT INTO student file values ​​('001', 'Wang Xiaoyong', 'male ",' First 2nd Class', '1') Insert INTO Student Archives Values ​​('001', 'Zhang Liufei",' ',' First 2nd Class', '1') Insert Into Student File Values ​​('001', 'Ziyunfei', 'Female', 'First 2nd Class',' 2 ') Insert Into Student Archives VALUES '001', 'Huang Tianlong', 'Male', 'Junior 2nd Class', '1') 3.3 Update Trigger For the aforementioned "Student Archives" situation database, if the class is changed, you need to create an UPDATE The trigger changes the "packet case" table.

UPDATE statement trigger is defined as:. CREATE TRIGGER updataStu ON [dbo] [student records] FOR UPDATEAS declare @stuGrade varchar (50), @ stuGroup int, @ stuCount int select @ stuGrade = class, @ stuGroup = group from inserted if Exists. Class = @ stugrade and group. Group = @ stugroup) Begin - BG1 Update packet set packet. Number of people = group. Ticket 1 WHERE group. Class = @ stugrade and group. Group = @ STUGROUP END --BG1 Else Begin - BG2 INSERT INTO Packet (Class, Group, Number) VALUES (@ stugrade, @ stuGroup, 1) end --bg2 select @ stugrade = class, @ stuGroup = group from deleted select @ stucount = Packet. The number of people from group. Class = @ stugrade and group. Group = @ stuGroup if @stucount> 1 Begin - BG3 Update packet set group. Number of people = Group -1 WHERE group. Class = @ stugrade and group. Group = @ stuGroup end - bg3 else begin - BG4 DELETE Group WHERE group. Class = @ stugrade and group. Group = @ stuGroup end - BG4 User From the definition statement of the above trigger, you can also experience two temporary tables in SQL Server as the trigger. INSERTED and DELETED Usage and superiority, as well as the subtle of the query using Transact-SQL language. 3.4 DELETE Trigger Delete trigger can be used to prevent some data from deleting certain data (such as records used as a foreign key) to ensure data integrity. It can also be used to nested delete operations, ie, while deleting the parent record, deletion sub-record. In the "Student File" table, if a student is transferred, you can create a DELETE trigger to achieve the corresponding modification in the "Packet Act" table, and delete the corresponding record in the "Student Grade" table.

The definition statement of the trigger is as follows: Create Trigger DELR ON [DBO]. [Student File] For delete as delete student grade WHERE student grades. Learn = (Select deleted. Learn from deleted) 3.5 ROLLBACK trigger ROLLBACK trigger is one Special triggers, its function is "Cancel" all trigger statements, SQL Server will stop executing triggers while deactivating data modifications that may activate triggers. The ROLLBACK trigger is: Rollback Trigger [with raiserror error_number [message]]] Where: 1ERROR_NUM BER: is an error number that appears. 2Message: Select information when an error occurs. 8raisError: The detailed, special error message provided to the user, can include error text, severe level, and status information to further illustrate the error. In addition, RaisError can also be used separately for writing a general error statement. Note: When using the ROLLBACK TRIGGER statement, you must give an error number or select information as long as you do not use the RaisError option, otherwise the call routine cannot know that the operation has been terminated. The ROLLBACK trigger cannot terminate batch operations used to modify. Because the code to modify the database automatically detect the return value of the modified operation (by detecting global variable @@ Error) to ensure that the modification operation is completed. Example 1: Partial Roll Roll To make some rollback in the trigger, you must use the Save Transaction statement. Tip: Transaction Save Point Save Point provides a mechanism for rollback some transactions. You can use the Save Transaction SavePoint_name statement to create a save point, then execute the Rollback Transaction SavePoint_name statement roll back to the save point so that there is no need to roll back to the beginning of the transaction. Create Trigger Testtrig On [DBO]. [Student File] For Updateas Update Packet Sitting Situ Number = 10 Where Group = '1''SAVE Transaction MyNameUpdate Packet Sitting Sitting Situ Number = 15 WHERE Class = 'First Two Two Class' - IF (@@ @error <> 0) Begin Rollback Transaction MyNameEnd Tip: Using @@ error If the last Transact-SQL statement is successful, the @@ ERROR system function returns 0; if this statement creates an error, @@ error Returns the error number. When each Transact-SQL statement is complete, the value of @@ ERROR will change. Because each Transact-SQL statement is executed, @@ error will get a new value, @@ error can handle the following two ways: After the Transact-SQL statement, immediately detect or use @@ Error. After the Transact-SQL statement is completed, store the @@ error to a integer variable immediately. The value of this variable is available later.

Test SQL statement Update Student Set School number = '010' Where s_id = 1 3.6 SQL Server The most important constraint for the trigger is delete, insert, update operation activation and performs a trigger, can fall back. And the general SQL statement operation cannot fall back. In addition, SQL Server also has some restrictions: (1) When the CREATE TABLE statement is called in the batch (a set of TRANSACT SQL statements ending with the GO statement, the statement must be the first statement in the batch. (2) The trigger set established by the trigger can only be a table of the base table or generated view, and cannot be a view. (3) It is recommended not to return a set of results without using the SELECT statement and variable assignment statement within the trigger. Because the user needs to be implemented, the user uses the program code to complete, and all the data of the above statement operations can be read from the trigger defined variable. If you really need to assign a value to the variable within the trigger, you should use the set nocount statement in the start section of the trigger to prohibit SQL Server return results. · (4) Setting the connection option using the SET Options will change the operating environment, but the set setting is only valid within the trigger. After the trigger is executed, each connection option will return to the status of the trigger activation. (5) When the data type of the large binary object table is TEXT and Image, the data processing thereof does not activate the trigger. (6) Additionally, WRITETEXT and TRUNCATE statements do not activate triggers for data operations that touch the Truncate statement. When using the following Transact SQL statement in the trigger definition text, SQL Server will refuse to compile and store: (1) All databases and its objects creation statement: createDatabase / table / index / procedure / default / rule / trigger / view / schema And create a temporary table of the temporary table by CREATE TABLE or SELECTINTO. (2) All ALTER statements: alter dataBase / Table / Procedure / Trigger / View. (3) All DROP statements: DropDatabase / Table / INDEX / Procedure / Default / Rule / Trigger / View. (4) All physical disk modification statements: diskinit / resize. (5) All databases are loaded with statements: loadDatabase / log. (6) All reinstall statements: restoredatabase / log. (7) Object authority statement: GRANT and REVOKE statement. (8) UpdateStatistics statement. (9) RECONFIGURE statement. (10) DENY statement. 3.7 Nested Trigger of the trigger is also referred to as the recursive call of the trigger, refers to a trigger to modify the contents in the touch form, activated another trigger on the table: Another trigger is similarly modified to activate the third trigger when modifying other touch tables: This layer is passed. In version of SQL Server 7.0, the nesting is allowed to be up to 16 flip flops. The user can also disable the trigger nested function by setting the nested trigger option in sp_configure.

The setting statement format of the Trigger nested function is prohibited in the sp_configure system stored procedure: sp_configure [configuration_option, [configure_value)] Where: 1Configuration_Option: Server options you need to set. SQL Server uses a Like operator when it looks out, so it is not necessary to enter a full name in the above statement or you can find this option correctly. However, in the above statement, if the Configuration_Option parameter containing spaces or other formats, you need to use quotation marks "". 2configure_value: Set the value set for the selected server option. The option to set the trigger nested function in sp_configure is NestedTrigger, thus prohibiting the trigger nesting statement can be written as follows (set the value of the value.): Sp_configure "NestedTrigger", 0 or sp_configure " Nested ", 0 and sp_configure" Trig ", 0 of these format settings are the same. 3.8 Trigger information Displays two methods to display trigger information to illustrate the operation of trigger on trigger table: using SQL Enterprise, Manager (Enterprise Manager, SQL-EM) or System Process SP Help, Sp_Depends, and SP Helptexto 1 . Using SQL ENTERPRISE Manager display trigger information: (1) First, run SQL Enterprise Managero (2) Select the Server where the trigger to display is located. (3) Select a worksheet on the server and its database and table o. (4) Select [All Triggers] command in the [Action] menu. After completing the above steps, a TRIGGER Properties window will appear, which will list all activated triggers in the TEXT text box of the window. 2. Use the system process system process sp_help to illustrate if the trigger exists, sp_depends description reference object, sp_helptext is actual text or original code. 1) Using SP_HELP Using System Process SP_HELP to list information about all objects in the database, including the founder of the flip-flop, the time created. Its syntax format is: sp__help [object_name] where Object_name is the object to be displayed. If this item defaults, SQL Server will list the information of the objects used in the system classification table SYSOBJECT. 2) Use sp_depends to use the system stored procedure sp_depends to return the dependency between the database objects, the view and the stored procedure. Its syntax format is: sp_depends object_name 3) Use sp_helptext to use the sp_helptext can easily access the storage text of all users defined in the system classification table SysComments, such as rules, default, view, triggers, and stored procedures. Its syntax format is: sp_helptext object_name process sp_depends and sp_helptext do not allow the object name options. 4. Decipsence of the trigger, decrypt 4.1 Trigger's encryption software issued, we hope that intellectual property rights can be protected, the stored procedure is a scripting language, different from the compile language, the scripting language is a text file, the scripting language is explained , The protection of scripting languages ​​is generally encrypted.

The executable code of the compile language is binary code, theoretically irreversible, and encrypted files can be completely decrypted. The trigger is encrypted is very simple, just need to add with encryption after the table name. USE studentGO CREATE TRIGGER instrg1 ON [dbo]. [Student records] WITH ENCRYPTIONFOR INSERTAS declare @ class varchar (50), @ team varchar (50), @ number tinyintselect @ class = inserted. Class, @ group = inserted. Group from inserted If EXISTS (SELECT number from group case where @ 级 = group situation. Class and @ 小 = group situation. Group) Begin - BG1 Select @ = number of people from packet case where @ class = grouping situation. Class And @ Group = Grouping Situation. Group set @ 人 = @ 人 1 Update packet situation set group situation. Number = @ 人 WHERE @ class = grouping situation. Class and @ 小 = Packet situation. Group END - BG1ELSE BEGIN - BG2 INSERT INTO Packet situation (Class, group, number) VALUES (@ class, @ 小, 1) end - b2 When we try to open the encrypted trigger, the system shows: / ****** Encrypted Object Is Not Transferable, and Script CAN not be generated. ****** / 4.2 The decryption of the flip-flop refers to the encrypted trigger to decrypt, online can find a lot of decryption tools, and we use Sqldecry2000 as an example to demonstrate the decryption process of the trigger.