Introduction to pre-trigger and post-trigger (downmoon)

Introduction to pre-trigger and post-trigger (downmoon)

A trigger is a special stored procedure. When Insert Update or Delete
Statement to modify one or more rows in a table. Because SQL Server calls a trigger for each specified operation on a specific table, you can use the trigger to extend the SQL
Sever's built-in integrity and data manipulation functions.
Note: Unlike the Delete statement, the Trancate Table statement does not activate the trigger, and the Write Text statement does not activate the trigger.

SQL Sever 2000 supports two types Of triggers: Instead Trigger and After Trigger.
Trigger ). The trigger is activated before the statement is executed, and then the trigger is activated after the statement is executed. You can use the FOR clause to select which trigger to use.
When you create a trigger for each operation, you can create a trigger for all three operations and use the corresponding programming technology to process each operation. The following example lists the three types of statements in the For clause and inserts the corresponding trace value into the CustUpdLog table using the Condition Statement. Create Trigger TrackCustomerUpdates
On AppDta. dbo. Customer
For Insert, Update, Delete
As
Declare @ InsertedCount Int
Declare @ DeletedCount Int
Set @ InsertedCount = (Select Count (*) From inserted)
Set @ DeletedCount = (Select Count (*) From deleted)
If (@ InsertedCount> 0) Begin
Insert Into AppDta. dbo. CustUpdLog
(CustID,
Action,
UpdUser,
UpdDateTime)
Select CustId,
Case
When (@ DeletedCount> 0) Then
'Update'
Else 'insert'
End,
Current_User,
Current_TimeStamp
From inserted
End
Else If (@ DeletedCount> 0) Begin
Insert Into AppDta. dbo. CustUpdLog
(CustId,
Action,
UpdUser,
UpdDateTime)
Select CustId,
'Delete ',
Current_User,
Current_TimeStamp
From deleted
End

As shown in this example, when an Insert or Update statement affects one or more rows, inserted
Temporary tables have record rows. No matter when the Delete or Update statement affects one or more rows, the deleted temporary table has record rows. For an Update
Statement. The deleted temporary table has old rows and the inserted temporary table has new rows. This example also reflects another important aspect of the trigger: For an Update or Delete operation on a table, the trigger is activated even if the statement does not affect the row (that is, the row that does not meet the Where clause ). The trigger stored procedure should predict this possibility.

You can not only create multiple triggers for a table, but also create multiple triggers for the same SQL statement (for example, Update statement) of a table.
Statement to create multiple front triggers. Each new Create Trigger
Statement to add the trigger to the existing triggers of the specified table and statement. You can use the system stored procedure sp_settriggerorder to create multiple triggers.
To specify the first activated trigger and the last activated trigger. For other triggers, the sequence of activation cannot be specified. The features of this trigger do not cause any special
Problem. Because various actions can always be implemented as normal stored procedures and called from a trigger in the required order.

Although a trigger is a stored procedure, it cannot be called using the Execute statement,
If you want to share the encoding of a trigger and a normal stored procedure, you only need to put the shared code in the stored procedure and call it from the trigger. If a trigger modifies a table, these modifications may be activated.
Another trigger, or itself. By default, SQL Server
The call depth of such nested triggers is 32 layers. Although we recommend that you allow nested and stacked triggers, you can use the system stored procedure to disable this. The following statement prevents the stacked trigger on the specified database:
Sp_dboption AppDta, 'cursive trigger', 'false'
To prevent nested trigger calls (including stacked calls) in all databases, you can use the following statement:
Sp_configure 'nested trigger', 0

The trigger is used as an example to introduce the basic content of the trigger. The following describes the differences between the trigger and the trigger. To create a Trigger, you must use Instead Of to explicitly declare the Trigger, as shown in the following example: create Trigger TrackCustomerUpdates
On AppDta. dbo. Customer
Instead Of Update
As
Insert Into AppDta. dbo. CustUpdLog
(CustId,
Action,
UpdUser,
UpdDateTime)
Select CustId,
'Update ',
Current_User,
Current_TimeStamp
From inserted

Unlike the post-trigger, the pre-trigger can be created in both tables and views, but only one pre-trigger can be created in one statement. Therefore, the pre-trigger does not have the issue of activation sequence.

Trigger application example: Synchronize the Insert operation of the Shop table on the current database server to the Shop on the other server.

Create trigger Trigger_SynShopForInsert1
ON dbo. Shop
FOR INSERT
AS
Insert into OtherServer. dbo. shop
(
LngShopID, strShopCode, strName, strShopName, strDescription, lngIndex
)
Select lngShopID, strShopCode, strName, strShopName, strDescription, lngIndex
From shop where lngShopID in (select lngshopid from inserted)

Or:

Create trigger Trigger_SynShopForInsert2
ON dbo. Shop
FOR INSERT
AS
Insert into OtherServer. dbo. shop
(
LngShopID, strShopCode, strName, strShopName, strDescription, lngIndex
)
Select lngShopID, strShopCode, strName, strShopName, strDescription, lngIndex
From inserted