22. Establish and use trigger procedures
What is a trigger program?
SQL Server 2000 trigger program enhancement function
When to use trigger programs
Establish a trigger program
Management trigger program
Summary of this chapter
The trigger program is a special type of pre-deployment. This chapter will learn the application and use of the trigger program, and the new features introduced by Microsoft SQL 2000 (T-SQL) trigger the program, and will demonstrate two ways to establish trigger programs, namely T-SQL statement, and use SQL Server Enterprise Manager, in addition, learning management and modify the trigger program.
What is a trigger program? Trigger is a special pre-depicting program that performs a specific statement (Update, Insert or delete) to start the trigger program. The trigger program is the same as other pre-depreciations, which can be composed of simple, or complex T-SQL state; as different from other pre-depreciation procedures, the trigger procedure is automatically performed when the designated data is modified, the trigger procedure is automatically executed, cannot Manually executed manually according to the name. When the trigger program is executed, it is called a fire. Although the trigger procedure is built in an existing database data sheet, it can access the information tables and objects of other databases. The trigger program cannot be built on a temporary information list or a temporary system data sheet, can only be built in the user's customized table or a self-reservation. Execute the data sheet or view table in which the trigger program is executed, called a trigger table. There are five types of triggers: Update, INSERT, DELETE, INSTEAD OF, and AFTER. With trigger procedures, as long as you update, insert or delete the table, you will touch the corresponding Update, Insert or DELETE trigger program. INSTEAD OF and AFTER is the new two triggers for SQL 2000. The original meaning of INSTEAD is "replace", the Instead Of trigger will replace insert, update, and delete operations. After the AFTER trigger program is touched after trigger, it can be considered as a mechanism for controlling the trigger startup time. Update, insertion, and deletion of information is considered as data modification event. You can design when one or more modified events are generated, that is, touch the trigger. For example, the trigger program is touched when the Update or INSERT statement is executed. This type of trigger program is called an UPDATE / INSERT trigger program. You can also create any modified event generation, perform a relative UPDATE / INSERT / DELETE trigger program. Below is some other provisions about the trigger program: The trigger program is only performed after the statement is triggered. For example, if the Update statement is successful, the Update trigger program will be touched. If the statement performs a violation of the condition constraint or causes an error in the data sheet, the trigger program will not be touched. The trigger program is considered part of a single transaction, so it can be restored from the original trigger program, and if a serious error is detected during the transaction, the entire transaction is automatically restored.
A statement can only touch a trigger program. When the trigger program is touched, if any result is generated, the result is transmitted back to its call. In general, INSERT, UPDATE or DELETE statements (statements of trigger programs) do not return the results; the result is usually transmitted by the SELECT query. Therefore, in order to avoid the trigger procedure to return the result to the application, do not introduce SELECT statements or assign variables in the trigger program definition. If you want to return the result from the trigger program, you must write a special program in each application that allows the modification of the trigger sheet, and the application is received and the correct process is performed. If you must assign variables in the trigger program, you can use the set nocount on the starting position of the trigger program to prevent back any result information columns. Set Nocount statement Specifies whether to pass information (for example, the 23 data columns) of the number of data columns (for example, the 23 data) is affected. Set NoCount's preset is set in OFF, that is, it will pass the message affected column. This setting does not affect the backup of the SELECT statement, only the count. SQL Server 2000 trigger program enhancements SQL Sever 2000 adds two INSTEAD OF and AFTER two triggers. The Instead of trigger program can replace the triggered SQL narrative, you can only define an INSTEAD OF trigger program for each statement (INSERT, UPDATE and DELETE). INSTEAD OF can be defined in a data sheet or in a visual table. If there is an Instead Of trigger program in several visual tables, you can connect the INSTEAD OF trigger program in series (Cascade) by defining the visual table. If you contain the WITH CHECK option in the update checklist, you cannot use the INSTEAD OF trigger program. If an INSTEAD OF trigger program is defined in the update checkpoint, you must use the alter view command to remove the WITH CHECK option from the updatable detection table. For more information on the establishment of the viewing table, please refer to Chapter 18 of this book. After the AFTER trigger program is touched after all the actions triggered in the SQL statement, it causes a reference checks and conditional constraints. You can specify multiple AFTER triggers for each trigger (INSERT, UPDATE, or DELETE). If the data table has multiple AFTER trigger programs, you can use sp_settriggerorder to define which After trigger program first troves, which last touches. All other trigger programs are inseparable from you in addition to the head-tailed two after trigger programs. In addition to new triggers, SQL Server 2000 allows trigger programs to be defined in the information list or view table. The previous version only allows trigger programs to be defined in the data sheet. The function definition of the trigger program in the test table is the same as the functionality in the data sheet. When the trigger program trigger program and condition constraint can be used to maintain the integrity and business rules of the information, but the trigger program cannot replace the conditional constraint (the conditional constraint is discussed in Chapter 16 of this book). For example, you don't need to create a trigger program to check if a value in the main index key in the data sheet can determine whether this value can be inserted into the corresponding data line in another data sheet (external index key condition constraints This is a good choice).
You should create a trigger program to initiate a series change of all relevant information tables in the database, for example, you may establish a DELETE trigger program on the Titles in the Pubs Database for Title_id data on the table, when you are in the Titles field When you delete a data, the data line of the corresponding information in Sales, Roysched and Titleauthor's tabs will also be deleted (in the next chapter we will see how to create the DELETE trigger program). You can also use trigger programs to perform more complex data detection than CHECK conditions (CHECK conditional constraints in Chapter 16). Since the trigger program can reference the data in other data sheets, complex data detection may be performed; in turn, the CHECK conditional constraint is limited to the information table defined therein. You can also create multiple trigger programs that touches all triggers when data is modified. (Remember, if you define multiple triggers for an event in the information list or viewpoint, each trigger must have a name). Or you can build a single trigger program that is touched when the information is modified. That is, every time the defined event occurs, the trigger program is touched once. Therefore, if the INSERT, UPDATE, and DELETE triggers are defined on the data sheet, each defined event is generated, the trigger program is touched. When establishing a trigger program, SQL Server will establish two temporary data sheets for the trigger program, you can refer to these two data sheets, write trigger programs definitions with T-SQL. These data sheets are fixed in memory with the trigger program, each trigger program can only access their temporary data sheets, and the temporary data sheet is a copy of the information sheet where the trigger program is located. You can use these data sheets to compare the data modification before and after. The next section will list these special information tables (called deleted and inserated data sheet). The establishment of a trigger program understands the trigger program and the purpose, and now understand the details of the establishment of the trigger program. First, we will establish a trigger program with T-SQL and then learn to build using Enterprise Manager. Using Enterprise Manager to establish trigger programs, you must also understand the T-SQL writing program, which is the same as the method of establishing other pre-depreciation using Enterprise Manager. CREATE TRIGGER statement To establish a trigger program with T-SQL, you need to use create trigger, the following is the basic syntax of Create Trigger: Create TriggerTrigger_Name
ON {Table | Name}
[With encryption]
{For | after | instead of}
{[Delete] [,] [Update] [,]}
[With append]
[Not for replication]
AS
SQL_STATEMENT [.... n]
As you look, you can establish a single or set of triggers for INSERT, UPDATE, DELETE, INSTEAD OF, or AFTER. If you use the For clause, you must specify at least one statement option, which will indicate which information modify the event type in the data sheet can cause the trigger. When the trigger program is called, the SQL statement or statement set after the AS key is executed, or a stylized structure, such as IF and While. The statements listed below are not allowed in the trigger program definition: ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE LOG RECONFIGURE RESTORE DATABASE RESTORE LOG has been mentioned before using Deleted and Inserted Data Sheet, when the trigger program is established When you can access two specific information sheets. Although these two data sheets are called data sheets, they are actually different from the general database data sheet, which are stored in the memory, not on a magnet. The two data sheets were named DELETED and INSERTED. The structure of the two data sheets is similar (the same information columns and the same type of information) to define the information sheet for the trigger program. DELETED data sheet will store a copy of the data column due to Delete and Update statements. When the data column is deleted or updated by the trigger program, the deleted or updated data sheet is transmitted to the DELETE data sheet; you can use the deleted data sheet in the trigger program. The Inserted Data Sheet will store a copy of the information column that is influenced by INSERT and UPDATE. When inserting or updating transactions, the new data column will be added to the trigger program table and the Inserted data sheet. Since the Update statement is executed, it is considered to insert or delete the transaction. The old data value will retain a copy in the deleted data sheet, and the copy of the new information column value is retained in the trigger program list and INSERTED information. table. When INSERT statement is executed, the new data column will not be transferred to the deleted data table. When INSERT statement is started, check the content of the deleted data sheet, although any copy is not yet, it will not have errors. message. Similarly, when executing DELETE statements, the deleted data column will not pass to the INSERTED data sheet. Therefore, if you want to see the status after modification, please refer to the correct information list. Note The value in the INSERTED and DELETED data sheet is limited to use in the trigger program. Once the trigger is complete, these two data sheets cannot be used.
Establishing the first trigger program Want to see how the trigger program works, first create a simple data table containing the trigger program, and defines a data update, you will print a statement. The following is T-SQL to establish this data sheet:
Use mydb
Go
CREATE TABLE BICYCLE_INVENTORY
(
Make_name char (10) Not null,
Make_id tinyint not null,
Model_name char (12) Not null,
Model_id tinyint not null,
In_Stock Tinyint Not Null,
ON_ORDER TINYINT NULL,
)
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "print_update" and
TYPE = "TR") DROP TRIGGER Print_Update
Go
Create Trigger Print_Update
On Bicycle_Inventory
For update
AS
Print "The Bicycle_INventory Table Was Updated"
Go
In order to test our trigger procedure, update the data sheet after adding information:
INSERT INTO BICYCLE_INVENVENTORY VALUES ("Trek", 1, "5500", 5, 1, 0)
Go
Update Bicycle_Inventory
Set make_id = 2
WHERE model_name = "5500"
Go
Demolition If you find an error while performing the above SQL statement, you can add the "SET Quoted_Identifier Off" statement before establishing a trigger program to close the use of quotation marks identification.
Since the trigger is started by Update, the "The Bicycle_Inventory Table Was Updated" message is passed. In this example, we define the trigger program to display the message when the trigger program is executed. In some specific cases, the trigger program can be required to return to the output result, for example, when you create an UPDATE trigger program, defined as a specific value to receive a specific value, but is worried that the data is updated is not correct. At this time, if there is a request to trigger procedure back transmission output (add a Print statement in the trigger program), you can find possible errors from the results; otherwise, in general, you do not need to ask the trigger procedure to return output results. Establishing a DELETE trigger program now to see a more complex example: Using the Delete statement, start the tandem update of the data sheet. We will create a trigger program that when you remove the data column from the Titles Data Sheet, other data sheets in the Pubs database (such as Sales, Roysched and Titleauthor), which are deleted with the information columns. We will use the DELETED data sheet to point out which information is removed from the relevant information table. (When the data is removed from the trigger program list, that is, there will be a copy of the data in the deleted data table, so you can visit the DELETED Data Sheet to view all the data deleted by all triggered programs). To operate the trigger program, we must delete the external index key condition constraint in the Titleauthor, ROYSCHED, and Sales table, because the external index condition constraints of these data sheets will refer to the information in the title_id column, if the external index key condition constraint is not deleted, The trigger will generate an error when deleting a title_id column.
Note If you don't mind if the Pubs database is modified, you can try the external index key condition constraint in your own delete database and establish a trigger program. The database chart using Enterprise Manager is deleted an external index key condition constraint is the easiest way (in Chapter 16). Please confirm that you have deleted in other data sheets, refer to the external index key condition of the title_id column data.
The T-SQL of this trigger program is as follows:
USE PUBS
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "delete_title" and
TYPE = "TR")
DROP TRIGGER DELETE_TITLE
Go
Create Trigger delete_title
ON Titles
For deleteas
Delete Sales
From sales, deleted
Where sales.title_id = deleted.title_id
Print "deleted from sales"
Delete Roysched
From roysched, deleted
Where roysched.title_id = deleted.title_id
Print "deleded from roysched"
Delete Titleauthor
From titleauthor, deleted
WHERE TITLEAUTHOR.TITLE_ID = deleted.title_id
Print "deleted from titleauthor"
Go
To test the trigger program, use the DELETE statement:
Delete Titles
Where title_id = "pc1035"
Go
Suppose you have deleted the external index key mentioned earlier. Once this DELETE statement is executed, the trigger program will touch, you will see the information modified the information impact column on the Title data sheet, followed by three triggers Row the message of the PRINT statement, and lists the number of columns in each data sheet, the output is as follows:
(Impact 1 data column)
Deleded from sales
(Impact 5 data columns)
Deleded from roysched
(Impact 1 data column)
Deleded from titleauthor
(Impact 1 data column)
Another use of the deleted data sheet is to store the data columns deleted in the data sheet to the backup data sheet for future data analysis. For example, you can use the following syntax, from the ROYSCHED data list, store the deleted data list in the data table named ROYSCHED_BACKUP:
USE PUBS
Go
Create Table ROYSCHED_BACKUP
(
Title_id Tid NOT NULL,
Lorange int NULL,
Hirange int NULL,
Royalty int NULL
)
Create Trigger TR_ROYSCHED_BACKUP
On ROYSCHED
For delete
AS
INSERT INTO ROYSCHED_BACKUP SELECT * from Deleted
Go
SELECT * ROYSCHED_BACKUP
Go
Please note that we have the same amount of data sheet name and data sheet for the backup data sheet. You can naming your data on your own, but the data patterns of the two data sheets should be the same to ensure the compatibility of the data. Building an Insert trigger program In this example, we will create an Insert trigger program (when the INSERT statement is executed, let this trigger the program touched), when we insert a new value in the QTY data column of the Sales table, the trigger program will Update the YTD_sales information in the Titles table. The trigger program queries the INSERTED data sheet to get the QTY value just inserted into the Sales table. We can reference the SELECT statement in the trigger to display the information contained in the Inser Terminal. The following is the T-SQL of the trigger program:
USE PUBS
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "update_ytd_sales" andtype = "tr")
DROP TRIGGER UPDATE_YTD_SALES
Go
CREATE TRIGGER UPDATE_YTD_SALES
On Sales
For insert
AS
SELECT *
From inserated
Update Titles
Set Ytd_sales = YTD_SALES Qty
From inserated
Where titles.title_id = INSERTED.TILE_ID
Go
You can notice that we use the fromTable_Source (from inserted) clause in the Update statement to indicate the QTY value from the Inserted Data Sheet. Do the following INSERT statements to view the results from the trigger program:
Insert Into Sales Values (7066, 1, "2000-03-07", 100, "Net 30",
"Bu1111")
Go
You will see the following results. The first set of results displays the data columns selected in the Inserted Data Sheet, and then display (affecting 1 data column) message (from Update statement), as shown below:
STOR_ID ORD_NUM ORD_DATE Qty Payterms Title_ID
---------------------------------------------------------------- ------------
7066 1 2000-03-07 00: 00: 00.000 100 Net 30 bu1111
(Impact 1 data column)
(Impact 1 data column)
Establishing an UPDATE trigger program Next we will establish an UPDATE trigger program that when updating the Price Data in the Titles Data table, the trigger checks the Price Data Line and verifying that the price increase does not exceed 10%. If more than 10% will be restored to the ROLLBACK statement, the statement of the trigger program and the call trigger program will be restored. If the trigger program is activated in a large transaction, the entire transaction will be restored. In this example, we use the deleted and inserated data sheets to test the price changes. The trigger program is defined as follows:
USE PUBS
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "update_price_check" and
TYPE = "TR")
DROP TRIGGER UPDATE_PRICE_CHECK
Go
Create Trigger Update_Price_Check
ON Titles
For update
AS
Declare @ORIG_PRICE MONEY, @new_price money
SELECT @orig_price = price from deleta
Print "Orig Price ="
Print Convert (VARCHAR (6), @orig_price)
SELECT @new_price = price from inserted
Print "new price ="
Print Convert (VARCHAR (6), @new_price) if (@new_price> (@ORIG_PRICE * 1.10))
Begin
Print "Rollback Occurred"
Rollback
End
Else
Print "Price is OK"
End
Go
Execute the following statement to view a price of Title_ID as bu1111, test the trigger program:
Select Price
From titles
WHERE TITLE_ID = "BU1111"
Go
The price is $ 11.95. Next, the following statement is performed, and the price increases by 15%:
Update Titles
Set price = price * 1.15
WHERE TITLE_ID = "BU1111"
Go
You will see the following results:
ORIG price =
11.95
NEW price =
13.74
Rollback Occurred
Trigger program touches, prints the original price and new price, but due to the price increase exceeds 10%, this transaction is restored. Now check the price and confirm if you will modify it. Please use the T-SQL below:
Select Price
From titles
WHERE TITLE_ID = "BU1111"
Go
Since the price reset to $ 11.95 (not new to $ 13.75), the modification is indeed replied. Now, let's test if the price changes below 10%, whether the price will be updated. Press 10% to modify the price, with the following T-SQL test results:
Update Titles
Set price = price * 1.09
WHERE TITLE_ID = "BU1111"
Go
Select Price
From titles
WHERE TITLE_ID = "BU1111"
Go
The price has been updated to $ 13.03, due to increased less than 10%, the trigger program will not be replied. When a UPDATE trigger program is created, you can specify that the trigger program only performs the statement when the specific data column is updated. For example, you can set only the information line only when the Price data row is updated, confirming that the trigger program is triggered correctly. Please use the following IF update clause:
USE PUBS
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "update_price_check" and
TYPE = "TR")
DROP TRIGGER UPDATE_PRICE_CHECK
Go
Create Trigger Update_Price_Check
ON Titles
For update
AS
If Update (Price)
Begin
Declare @ORIG_PRICE MONEY, @new_price money
SELECT @orig_price = price from deleta
Print "Orig Price ="
Print Convert (VARCHAR (6), @orig_price)
SELECT @new_price = price from inseratedprint "new price ="
Print Convert (VARCHAR (6), @new_price)
IF (@new_price> (@orig_price * 1.10)))
Begin
Print "Rollback Occurred"
Rollback
End
Else
Print "Price is OK"
End
Go
If the update of the Titles data sheet does not include the Price Data Line (Update Other Data Rows only), the trigger will skip the statement between the first Begin and End and skip the trigger. To test whether the above trigger programs are expected to perform statements between Begin and End, you can update a non-PRICE Data line with the following T-SQL statement. The following example is the value of the TiD_sales data line to update the Title_ID of Bu1111.
Update Titles
SET YTD_SALES = 123
WHERE TITLE_ID = "BU1111"
Go
You can see the update of the data line is not in the Price Data Row, the external IF condition returns FALSE, so the output result does not start the printed statement of the trigger program. This way can avoid unnecessary statements for SQL Server processing. Establishing an Instead Of Trigger Program Instead of trigger, allows you to control the events that occur when implementing Insert, Update, and Delete, which is mainly applicable to updates to the joint inspection table. In general, the joint inspection table is unrecognizable because SQL Server does not know which substrate information table wants to update. To allow the viewpoint support to update, you can define an Instead Of trigger program in the inspection table to modify multiple substrate information tables through the visual table. Please refer to the following example: The following example creates a view table named TitlesbyAut by using the T-SQL statement, which refers to the data of Author, Titles, and Titleauthor data sheet. (Chapter 18 of this book can find more information on the test list.)
USE PUBS
Go
Create View TitlesbyAuthor
AS
Select authors.au_id, authors.au_lname, Titles.Title
From authors inner join
Titleauthor on authors.au_id = title = a_id inner join
Titles On TitleAuthor.title_ID = Titles.title_ID
Go
After establishing a viewing table, we use T-SQL to display all information columns to reach inspecting qualifications:
USE PUBS
Go
SELECT *
From titlesbyauthor
Go
AU_ID au_lname Title
------------------------------------- -------------
238-95-7766 Carson But Is IT User Friendly?
724-80-9391 Macfeather Computer Phobic and non-phobic
Individuals:
756-30-7391 Karsen Computer Phobic and non-phobic
Individuals:
267-41-2394 O'Leary Cooking with Computers:
724-80-9391 Macfeather Cooking with computers:
486-29-1786 Locksley Emotional Security: a new algorithm
648-92-1872 Blotchet-Halls Fifty Years in Buckingham Palace
Kitchens
899-46-2035 Ringer Is Anger The Enemy?
998-72-3567 Ringer Is Anger the Enemy?
998-72-3567 Ringer Life without Fear
486-29-1786 LOCKSLEY NET ETIQUETTE
807-91-6654 PANTELEY Onions, Leeks, And Garlic:
172-32-1176 White Prolonged Data DEPRIVATION: FOUR CASE
STUDI
427-17-2319 DULL Secrets of Silicon Valley
846-92-7186 Hunter Secrets of Silicon Valley
712-45-1867 Del Castillo Silicon Valley Gastronomic Treats
274-80-9391 Straight Straight Talk About Computers
267-41-2394 O'Leary Sushi, Anyone?
472-27-2349 GringLESBY SUSHI, ANYONE?
672-71-3249 Yokomoto Sushi, Anyone?
213-46-8915 Green the busy Executive's Database Guide
409-56-7008 Bennet The Busy Executive's Database Guide
722-51-5454 Defrance the gourmet microwave
899-46-2035 ringer the gourmet microwave
213-46-8915 Green You Can Combat Computer Stress!
(Impact 25 data columns)
If you try to remove Carson in the AU_LNAME Data Row, the following message appears:
Server: message 4405, level 16, state 1, line 1
Since this modification affects multiple substrate information tables, checklists or functions 'TitlesbyAuth' cannot be updated.
To avoid the above message generation, we now use the INSTEAD OF trigger program to perform the deleted action. The following we will use an INSTEAD OF trigger program named DELETE_IT to perform the deleted action, and its T-SQL operation is as follows:
Note The following operation is not truly deleted in the Author's Data Sheet, but only renamed this information column.
USE PUBS
Go
IF exissits (SELECT NAME
From sysobjects
Where name = 'delete_it' andtype = 'tr')
Drop Trigger delete_it
Go
Create Trigger delete_it
ON Titlesbyauthor
INSTEAD of Delete
AS
Print 'Row from Authors Before deletion ...'
SELECT AU_ID, AU_LNAME, CITY, STATE
From authors
Where au_lname = 'CARSON'
Print 'Deleting Row from Authors ...'
Update Authors
Set au_lname = 'deleted'
Where au_lname = 'CARSON'
Print 'verifying deletion ...'
SELECT AU_ID, AU_LNAME, CITY, STATE
From authors
Where au_lname = 'CARSON'
Go
With the above way, the Carson is removed by using the T-SQL statement self-test table, this program makes the INSTEAD OF trigger program to get the following output results:
Row from Authors Before deletion ...
AU_ID AU_NAME CITY State
-------------------------------------------- ---- -
238-95-7766 Carson Berkeley CA
(Impact 1 data column)
Deleting row from authors ...
(Impact 1 data column)
Verifying deletion ...
AU_ID AU_NAME CITY State
-------------------------------------------- ---- -
(Impact 0 information columns)
Establishing an AFTER Trigger Procedure This chapter has been mentioned that the AFTER trigger program is touched after the SQL statement moves. You can specify multiple AFTER triggers for each trigger (INSERT, UPDATE, or DELETE). If the information form has multiple AFTER triggers, you can use sp_settriggerorder to define which After which After trigger programs are touched and which last touches. All other trigger programs are inseparable from you in addition to the head-tailed two after trigger programs. The following is this statement of grammar:
sp_settriggerorder [@triggername =] 'TriggerName',
[@ Order =] {'first' | 'Last' | 'None'}
First, look at an example. Assuming that we define four triggers in a data table, named myTrigger, My PortherTrigger, Anothertrigger, and YetAnothertrigger. When the statement is triggered, the ANOTHER TRIGGER is first touched, and myTrigger lasts.
sp_settriggerorder @triggername = 'Anothertrigger', @order = 'first'go
sp_settriggerorder @triggername = 'myTrigger', @order = 'last'
Go
sp_settriggerorder @triggername = 'my my my myoTHERTRIGGER', @order = 'none'
Go
Sp_settriggerorder @triggername = 'yetanothertrigger', @order = 'none'
Go
The "None" seen above makes the trigger program that is started between the first and last trigger programs to perform random order. Since the random execution order is performed according to the preset of the trigger program, the SP_SETTRIGERORDER definition order is not required. Building a neo-trigger program Nest-shaped trigger program (NESTED TRIGGER) is a trigger program that is started by other trigger programs, and a different place in the back trigger program is that the nest-like trigger program does not start, but when the modification event is generated. It is only started from the other trigger program that starts. The SQL Server 2000 and SQL Server 7.0 are the same. The first trigger program can start the second trigger program, and the second trigger program is connected to the third trigger program, and so on, it can be as high as 32 hierarchical trigger programs. In SQL Server 2000, the preset state of the neo-trigger program is set to enable, and the NESTED TRIGGERS server setting parameter can be used to control whether the trigger program can be nested. To stop the nest trigger program, you can perform the following instructions:
SP_Configure "Nested Triggers", 0
Go
When the NESTED TRIGGERS is set to 0, the nest trigger program is not activated; the Nested Triggers are set to 1, and the nest trigger program can be activated. Next is an example of a neo-trigger program: When a book is deleted from the Titles Data Sheet, the nest-like trigger program is caused by deleting the corresponding data column in series. In this chapter, it is created in the DELETE trigger program> section, we have established a single trigger program for information on the series deletion. We have to use the previous example, but first delete the trigger program in previous examples to avoid touch, then build three sets of trigger programs; second and third set trigger programs are nest trigger programs, the syntax is as follows:
USE PUBS
IF exissits (SELECT NAME
From sysobjects
Where name = "delete_title" and
TYPE = "TR")
DROP TRIGGER DELETE_TITLE
Go
Create Trigger TR_ON_TITLES
ON Titles
For delete
AS
Delete Sales
From sales, deleted
Where sales.title_id = deleted.title_id
Print "deleted from sales"
Go
Create Trigger TR_ON_SALES
On Sales
For delete
AS
Delete Roysched
From roysched, deleted
Where roysched.title_id = deleted.title_idprint "deleted from roysched"
Go
Create Trigger TR_ON_ROYSCHED
On ROYSCHED
For delete
AS
Delete Titleauthor
From titleauthor, deleted
WHERE TITLEAUTHOR.TITLE_ID = deleted.title_id
Print "deleted from titleauthor"
Go
To perform a trigger program, you must first delete the external index key condition constraint on the data sheet (as shown in this chapter
Delete
From titles
Where title_id = "ps7777"
Go
You can see the following results:
(Impact 2 data columns)
(Impact 1 data column)
Deleded from titleauthor
(Impact 2 data columns)
Deleded from roysched
(Impact 1 data column)
Deleded from sales
(Impact 1 data column)
When any of a group of neo-triggers fails, the fair will be canceled, and all data modifications will be restored to the state before the entire group trigger. To create a trigger program with Enterprise Manager To create a trigger program using Enterprise Manager, you only need to type T-SQL statements in the trigger properties window, the execution steps are as follows:
From Enterprise Manager, press the right button on the information table name you want to create a trigger program to turn on the fast-display window. Select all work / management triggers, the trigger property dialog will appear, as shown in Figure 22-1.
Figure 22-1 "Trigger Properties" dialogue
Enter the T-SQL syntax for the trigger program in the text (Figure 22-2). Press to check the syntax button to check the syntax. The grammar is successful if the syntax is successful. Windows (Figure 22-3). Press to determine to create a trigger program. Now the trigger program print_update appears in the name drop-down list, as shown in Figure 22-4. Trigger the status of the properties window to keep on, so that you can create additional triggers on the data sheet. If you don't have other trigger programs to be established, press Close.
Figure 22-2 The "Trigger Properties" dialogue after the trigger program syntax
Figure 22-3 Syntax Check Success Window
Figure 22-4 Name of the newly established trigger program in the pull-down list
Managing Triggers In this section, let's learn to use the T-SQL management trigger program, then learn how to use Enterprise Manager. Use the T-SQL management trigger program T-SQL instruction to manage the trigger program. You can view the trigger programming syntax, exist on the trigger program on a specific information list, change the trigger program syntax, delete the trigger program, start or stop the trigger. These options will be explained in this section. Check the trigger program syntax sp_helptext and sp_helptrigger two system pre-depreciation can provide information about the trigger program. To display text to create a program, SP_HELPTEXT can be performed before the pre-deprecated name. For example, if you want to display a text definition in this trigger program, you can use the following instructions:
Use mydb
Go
SP_HELPText Print_UPDatego
The output is as follows:
TEXT
-----------------------------
Create Trigger Print_Update
On Bicycle_Inventory
For update
AS
Print "The Bicycle Inventory Table Was Updated."
Checking the existing trigger programs in the data table To view the trigger program in the specific data sheet (or see if the trigger exists) can perform the sp_helptrigger pre-depicting program before the data table name. To see an example of the sample information table MyTable trigger, please use the following instructions:
Use mydb
Go
Sp_helptrigger myTable
Go
The output is as follows:
Trigger_name Trigger_owner isupdate isdelete IsInsert Isufactiv isinsteadof
------------ -------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------
Print_Update DBO 1 0 0 1 0
(Impact 1 data column)
The output shows that the trigger program name, the trigger program owner, and the data modification event type that activates the trigger program. If the trigger program is scheduled for this type of data modification event, the output result information line isUpdate, isdelete, isinsert, ISDETER, and is 1, if not started, 0. If the trigger program modifies the event, there will be a number of data lines containing a value of 1 for multiple types of data. Using ALTER TRIGGER To change the previously established trigger programs definition, you can remove the trigger program after delete, or use the ALTER TRIGGER statement, this statement uses the same syntax as described in Create Trigger. If you want to change the trigger, you must redefine the entire trigger program. For example, it is necessary to change the Print_UPDATE trigger program to BiCyle_inventory's data modification mode (when INSERT or UPDATE statements are executed), the following syntax can be performed:
Use mydb
Go
Alter Trigger Print_Update
ON MyTable
For update, insert
AS
Print "Bicycle_Inventory Was Updated or A Row Was Insert"
Go
Now the previously defined trigger program has been replaced by the updated version. Now if you perform Update or Insert on Bicycle_Inventory, the trigger program is touched, the following is the syntax executed:
INSERT INTO BICYCLE_INVENVENTORY VALUES ("TREK", 1, "Lance S.E.", 1, 0, 1)
Go
Update Bicycle_Inventory
SET IN_STOCK = 1
WHERE model_name = "Lance S.E."
Go
Use Drop Trigger to fully delete triggers from the data table, which can use the DROP TRIGGER statement. The desired syntax is as follows:
Drop triggertrigger_name
To delete the Pring_UPDate trigger, you can use the following statement:
Use bicycle_inventory
Go
Drop Trigger Print_Updatego
If all triggers existing on the MyTable data sheet are now viewed, it will find that the trigger programs have no longer exist, and you can use the following symptoms:
Use mydb
Go
Sp_helptrigger myTable
Go
Note If you delete a data sheet, all triggers on the table will be automatically deleted.
Starting and stop trigger programs utilizing the ALTER TABLE statements, you can change the trigger programs without deleting the trigger programs, because each trigger program is defined on a specific data table, so using the ALTER TABLE statement instead of the ALTER TRIGGER statement. Use the following syntax to rebuild the triggers of the first example in this chapter:
Use mydb
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "print_update" and
TYPE = "TR")
Drop Trigger Print_Update
Go
Create Trigger Print_Update
On Bicycle_Inventory
For update
AS
Print "The Bicycle_INventory Table Was Updated"
Go
The trigger program is automatically enabled at the beginning. Turning off the trigger program allows it to be not enabled (until it is re-enabled), but its definition still exists on the data sheet. You can use the following Disable Trigger option to turn off the trigger:
Alter Table Bicycle_Inventory
Disable Trigger Print_Update
Go
The print_update trigger program will not be touched when updating occurs on BiCyCle_Inventory. To restart the trigger program, you can use the enable trigger, the syntax is as follows:
Alter Table Bicycle_Inventory
Enable Trigger Print_Update
Go
Enable Trigger and Disable Trigger clauses allow the trigger program to be defined unchanged, and the trigger program is turned on or off views. You can also use Enterprise Manager to manage triggers using Enterprise Manager management triggers, although you can use the graphical use interface management trigger program, you still need to write T-SQL program syntax. Deleting a trigger program To remove the trigger program with Enterprise Manager, follow the steps below:
Press the right button on the data table name to call the fast display function table, select all work / management triggers. Select the trigger program name from the name drop-down list, then click Delete.
When you ask if you determine if you want to delete a trigger program, press OK (as shown in Figure 22-5).
Figure 22-5 Determine whether to delete the dialogue block
Modify the trigger program Follow the steps below to use Enterprise Manger to modify the trigger program
Press the right button on the data table name to call the fast display function table, select all work / management triggers. Select the trigger program name from the name drop-down list. Edit the T-SQL syntax in the text block, use the Ctrl Tab key to complete the text of the trigger program. You can use Create Trigger or ALTER TRIGGER statement, whether using which of them Or ISQL, the error message will appear. After completing editing, press OK, SQL Server will automatically modify the trigger program definition. This chapter summarizes this chapter, what we have learned to trigger the program and meet five types of trigger procedures, which are INSERT, UPDATE, DELETE, ALTER, and INSTETETETER, and also learned to use T-SQL and Enterprise Manager build and manage triggers. program. Next chapter, we will use ADO and XML to access SQL Server 2000 through the Internet.
