How to Develop your own SQL Server 2000 trigger

This article starts from the definition and explains how to develop your own triggers in detail.

Create a simple trigger

A trigger is a special stored procedure, similar to an event function, SQL Server? Allows the creation of triggers for INSERT, UPDATE, and DELETE, that is, one or a series of T-SQL statements that are triggered when a record is inserted, updated, or deleted in a table.

Triggers can be created in the query analyzer, you can also right-click on the table name-> "all tasks"-> "manage triggers" to create, but are to write T-SQL statements, only in the query analyzer, you must first determine the database for the current operation.

CREATE TRIGGER

Create trigger name
ON Table Name
For insert, UPDATE, or DELETE
AS
 

A T-SQL statement

Note: The trigger name is not enclosed in quotation marks.

The following is an example of online books. When you change the record on the titles table, an email notification is sent to MaryM.

Create trigger reminder
ON titles
For insert, UPDATE, DELETE
AS
EXEC master .. xp_sendmail 'marym ',
'Don't forget to print a report for the distributors .'
 

 

Rename a trigger

Rename with query Analyzer:

Exec sp_rename original name, new name

Sp_rename is SQL Server? A stored procedure is used to change the name of the object created by the user in the current database, such as the table name, list, and index name.

Rename with Enterprise Manager:

Right-click a table and choose "all tasks"> "manage triggers". Select the trigger to be renamed, modify the trigger name in the trigger statement, and click "OK ".

Delete trigger

Delete with query Analyzer:

Use the drop trigger name in the query analyzer to delete the trigger.

You can also delete multiple triggers: drop trigger name, trigger name...

Note: The trigger name is not enclosed in quotation marks. Before deleting a trigger, you can check whether the trigger exists:

If Exists (select name from sysobjects where name = trigger name and xtype = 'tr ')

Delete with Enterprise Manager:

In Enterprise Manager, right-click a table and choose "all tasks"> "manage triggers". Select the trigger to be deleted and click "delete ".

How to view the content of a trigger

View with the query Analyzer:

Use Database Name
Go
Exec sp_helptext 'trigger name'

The trigger content is displayed in a table style.

In addition to triggers, sp_helptext also displays the text of rules, default values, unencrypted stored procedures, user-defined functions, and views.

Use the Enterprise Manager to view:

Right-click a table and choose "all tasks"> "manage triggers". Select the trigger to be viewed.

How to view the triggers in the current database

Run the following command in the query Analyzer:

Use Database Name
Go
Select * from sysobjects where xtype = 'tr'
Sysobjects
Stores database objects. Records with xtype as TR are trigger objects.
In the name column, we can see the trigger name.

Sp_helptrigger

The stored procedure sp_helptrigger is used to view the attributes of a trigger.

Sp_helptrigger has two parameters: the first parameter is the table name, the second parameter is the trigger type, char (6) type, can be INSERT, UPDATE, DELETE, if this parameter is omitted, the attributes of all types of triggers in the specified table are displayed.

Example:

Use Database Name
Go
Exec sp_helptrigger tbl

 

More triggers

Instead:

Execute the trigger statement, but do not execute the SQL statement that triggers the trigger. For example, if you try to delete a record, the statement specified by the trigger is executed, and the delete statement is no longer executed. Example:

Create trigger f
On tbl
Instead of delete
As
Insert into Logs

If update (column name ):

Check whether a column is updated for insert or update. It cannot be used for delete. Example:

Create trigger f
On tbl
For update
As
If update (status) or update (title)
SQL _statement -- the status or title column is updated.

Inserted, deleted:

This is two virtual tables. inserted stores the tables that are affected by the insert or update operations. deleted stores the tables that are affected by the delete or update operations. Example:

Create trigger tbl_delete
On tbl
For delete
As
Declare @ title varchar (200)
Select @ title = title from deleted
Insert into Logs (logContent)
Values ('deleted record with title: '+ title + ')

Note: If the field value of text or image is obtained from the inserted or deleted virtual table, the obtained value is null.

Trigger rollback

Example:

We can see that many registration systems cannot change the user name after registration, but most of them are determined by the application. If you directly open the database table to change the user name, you can also change the user name, by using rollback in a trigger, the user name cannot be changed.

Use Database Name
Go
Create trigger tr
On Table Name
For update
As
If update (userName)
Rollback tran

The key lies in the last two sentences. The explanation is: if the userName column is updated, the transaction will be rolled back.

Recursive and nested triggers

Recursive trigger:

There are two types of recursion: Indirect recursion and direct recursion. For example, if Table 1 and Table 2 are named T1 and T2, And the triggers G1 and G2 are on T1 and T2 respectively.

Indirect recursion: T1 operations trigger G1, G1 operations on T2 to trigger G2, G2 operations on T1 to trigger G1...

Direct recursion: The T1 operation triggers G1, And the G1 operation triggers G1...

Nested triggers:

Similar to indirect recursion, indirect recursion must form a ring, while nested triggers do not have to form a ring. It can be T1-> T2-> T3... in this way, a maximum of 32 layers can be nested.

Set direct recursion:

Direct recursion is disabled by default. You can set this parameter to allow two methods:

T-SQL: exec sp_dboption 'dbname', 'recursive trigger', true EM: Right-click on the database-> properties-> option. Set Indirect recursion and nesting

Indirect recursion and nesting are allowed by default. There are two ways to disable this function:

T-SQL: exec sp_configure 'nested trigger', 0 -- the second parameter is 1 to allow EM: register right-click on-> properties-> server settings.