SQL Server 2005 Trigger Synopses

 The trigger in fact is one kind of special type memory process, it is when carries out certain specific T-SQL sentence the automatic execution one kind of memory process.In SQL Server 2005, according to the SQL sentence difference, divides into the trigger two kinds: One kind is the DML trigger, one kind is the DLL trigger.
 In SQL Server 2005, may use two methods to guarantee the data the validity and the integrity: Restrains (check) and trigger (Trigger).The restraint is the direct establishment in the data sheet, only can the reality some quite simple function operation, for example: The realization field validity and the unique inspection, fills in the default value automatically, guarantees the field data not non-repeatedly (i.e. primary key), guarantees the integrity which the data sheet corresponds (i.e. foreign key) and so on the functions.
 The trigger is aims at the data sheet (storehouse) the special memory process, when this table has had Insert, Update or the Delete operation, can activate the execution automatically, may process each kind of complex operation.In SQL Server 2005, the trigger had the further function, (storehouse) has Create, Alter and the Drop operation when the data sheet, also can activate the execution automatically.
 The trigger commonly used some functions are as follows
 1．Completes compared to restrains the more complex data restraint: The trigger may realize compared to restrains the more complex data restraint.
 2．Inspecting offices do whether SQL does permit: The trigger may inspect the operation which SQL does whether is permitted.For example: Keeps in stock the outside and inside in the product, if must delete a product record, when deletes the record, the trigger may inspect this product stock quantity whether is the zero, if does not cancel this deletion operation for the zero.
 3．Revises other data outside and inside the data: When a SQL sentence carries on to the data sheet operates, the trigger may act according to this SQL sentence the operation situation to come to another data sheet to carry on the operation.For example: An order form cancels, then the trigger may revise the product stock table automatically, subtracts in the order quantity field by cancel order quantity.
 4．Transfers the more memory process: Restraint itself cannot transfer the memory process, but trigger itself is one kind of memory process, but saves the process is may the nesting use, therefore the trigger also may transfer or many memory process.
 5．Transmits SQL Mail: Carries out after the SQL sentence, the trigger may judge has changed the recording whether achieves the controlled condition, if achieved this condition the speech, the trigger may transfer SQL Mail to transmit the mail automatically.For example: After an order form paying fee, may the physical distribution personnel transmit Email, informs him to deliver goods as soon as possible.
 6．Returns from definition error message: The restraint cannot return to the information, but the trigger may.When for example inserts a repetition recording, may return to a concrete friendly error message to give the onstage application procedure.
 7．The change must operate originally SQL sentence: The trigger may revise the SQL sentence which must operate originally, for example the originally SQL sentence is must delete the data outside and inside the record, but this data outside and inside recording most wants the record, does not permit the deletion, then the trigger may not carry out this sentence.
 8．Prevented the data sheet construction ties the change or the data coating deletion: In order to protect already constructed the good data sheet, the trigger might in receive in the SQL sentence which Drop and Alter began, did not carry on to the data sheet operation.
 DML trigger principle of work: In SQL the Server 2005 miles, have all defined two special tables for each DML trigger, one inserts the table, one deletes the table.These two tables are construct in the database server memory, is by the system administration logical table, but is not the true memory in the database physical table.Regarding these two tables, the jurisdiction which the user only then reads, does not have the revision jurisdiction. These two table structure and the trigger in the data sheet structure are completely consistent, after trigger completion of work, these two tables will also be able to delete from the memory.Inserts outside and inside depositing is before the renewal record, regarding inserts the record operation, inserts data which outside and inside depositing is must insert; Regarding renews the record operation, inserts record which outside and inside depositing is must renew. After deletes outside and inside depositing is the renewal recording: Regarding renews the record operation, after deletes outside and inside depositing is before the renewal recording (renews namely is deleted); Regarding deletes the record operation, deletes the outside and inside to store is the old record which deletes.
 After trigger principle of work: The After trigger is changes after the recording only then is activated the execution.Take deletes the record as the example, when SQL Server receives must carry out the deletion operation SQL sentence to one, SQL Server first is going to delete the record deposits in deletes the outside and inside, then the data outside and inside record deletion, then activates the After trigger, carries out After in the trigger SQL sentence. After the execution finished, deletes in the memory the deletion table, the withdrawal entire operation.
 Instead Of trigger principle of work: Instead the Of trigger and the After trigger are different.The After trigger is after Insert, Update and the Delete operation completes only then activates before, but Instead the Of trigger, was carried on in these operations activated, and no longer carried out the original SQL operation, but moved the trigger itself SQL sentence.
 The trigger is commends the union with the database and the data the special memory process, when the data sheet has Insert, Update, the Delete operation or the database has Create, Alter, Drop operate, may activate the trigger, and moves T-SQL sentence.2005 triggers divide into the DML trigger and the DDL trigger two kinds in the SQL Server 2005 DML trigger divides into the After trigger and Instead the Of trigger two kinds。After the After trigger is a trigger which the amendment record activates first; Instead the Of trigger is “the substitution” the trigger.The DDL trigger may divide into the function according to the sphere of action in the database trigger and the function in the server trigger two kinds. The After trigger only can use in the data sheet, but Instead the Of trigger namely may use in the data sheet, also may use in the view.Uses CREATE the TRIGGER sentence to be possible to found the trigger, uses ALTER the TRIGGER sentence to be possible to revise the trigger, uses Drop the Trigger sentence to be possible to delete the trigger.The trigger permission nesting and the recursion, the nesting are most may be 32.