1.6 SQL Server 2000 trigger ManagementIn database management and maintenance, it is essential to ensure the integrity of the database in the database. A trigger is a special stored procedure that implements complex integrity constraints. When the insert, delete, and update commands are used to modify the data protected by the trigger, the data can be automatically activated by the system, it is used to prevent incorrect, unauthorized, or inconsistent modification of data. Unlike a general stored procedure, a trigger cannot be executed using an execute statement. The following describes how to create, modify, and delete a stored procedure.
1.6.1 create a triggerWhen creating a stored procedure, consider the following factors. ● The create trigger statement must be the first statement in batch processing. Interpret all other subsequent statements in the batch as part of the create trigger statement definition. ● The create trigger permission is assigned to the table owner by default and cannot be transferred to other users. ● A trigger is a database object and its name must follow the naming rules of the identifier. ● Although triggers can reference objects other than the current database, they can only be created in the current database. In the Enterprise Manager, expand the database of the specified instance and click the "table" folder. In the details pane, right-click the table on which the trigger is created, point to the "all tasks" menu, and select the "manage trigger" command to open the "trigger properties" dialog box, see Figure 4-21. Figure 4-21 select the "new" list item from the "name" drop-down list box of the trigger attribute. Enter the trigger text in the text box. Press Ctrl + TAB to indent the trigger text. When creating a trigger, consider: ● name. ● Define the trigger table on it. ● When the trigger is triggered. ● Activate the data modification statement of the trigger. Valid options include insert, update, and delete. Multiple data modification statements can activate the same trigger. For example, a trigger can be activated by an insert or update statement. ● Execute the program statement that triggers the operation. After entering the syntax, click "Check Syntax" to check whether the syntax is correct. A message is displayed. If the prompt is correct, click OK to create the trigger. The syntax for using CREATE trigger to create a trigger is as follows: Create trigger
Trigger_nameOn {
Table|
View} [With encryption] {for | after | instead of} {[insert] [,] [update] [,] [delete]} as [{If Update (
Column)…] [{And | or} Update (
Column)…]
SQL _statement[... N] the parameters are described as follows. ●
Trigger_name: Specifies the name of the trigger to be created. ●
Table|
View: It is the table or view on which the trigger is executed, sometimes called the trigger table or trigger view. You can choose whether to specify the table or view owner name. ● {[Insert] [,] [update] [,] [delete]}: Specifies which data modification statements on the table or view will activate the trigger keywords. You must specify at least one option. These keywords can be combined in any order in the trigger definition. If more than one option is specified, separate them with commas. ● If Update (
Column): The insert or update operation on the specified Column cannot be used for the delete operation. ●
SQL _statement: The database operations performed by the Trigger After the trigger is triggered. It can contain any number of SQL statements. For example, create the trigger tg_insertstudents to prevent data from being inserted into the studentsinfo table. The syntax is as follows: Create trigger failed on studentsinfofor insertas raiserror ('unauthorized',) rollback Trango later, when a user inserts data into the studentsinfo table, the trigger is triggered. The syntax is as follows: insert into studentsinfo (stu_id, sname, sgender, sage, sdpt) values ('2016 ', 'Dong gang', 'male', 20, 'computer system ')
1.6.2 modify a triggerThe trigger can be changed based on user requirements or the definition of the base table. You can modify the trigger in the "trigger properties" dialog box. You can also use the alter trigger statement to change the trigger that was previously created by executing the create trigger statement, without changing the permission or affecting the stored procedure or trigger. The syntax is as follows: Alter trigger
Trigger_name[; Number] on {
Table|
View} [With encryption] {for | after | instead of} {[insert] [,] [update] [,] [delete]} as [{If Update (
Column)…] [{And | or} Update (
Column)…]
SQL _statement[... N] The parameter meanings are basically the same as those used when the trigger is created.
1.6.3 delete a triggerYou can delete a trigger in Enterprise Manager: Right-click the trigger to be deleted and select the DELETE command from the shortcut menu that appears. You can also use the drop command to delete a trigger. The drop command can delete one or more triggers or trigger groups from the current database. The syntax is as follows: Drop trigger {
Trigger _ name}[,... N] where,
Trigger_nameUsed to specify the name of the trigger to be deleted. In addition, you can use this command to delete multiple triggers at the same time. You only need to use commas to separate the names of the triggers to be deleted. For example, to delete the trigger named _ showstudents in the studentsinfo table, the syntax is as follows: Drop trigger tg_insertstudents
