SQL Server database development trigger application 1

Re: http://bbs.51js.com/viewthread.php? Tid = 52487 & fpage = 2

 

[Original: database] SQL Server database development trigger application

Question: SQL Server database development trigger application
Author: cultivator
Date: 2006-01-10
Note: Due to limited personal abilities, errors or omissions may inevitably occur in the article. Please forgive me! At the same time, you are welcome to point out so that I can modify it in time to avoid misleading the next viewer. Finally, I hope this article will help you.

I don't know how many friends in the jar use the trigger. If you already know the trigger, skip this article. If you have not used the trigger before, let's get to know each other.

Reference: Definition:
A trigger is a special type of stored procedure that you cannot directly call. When you use one or more of the following data modification operations to modify data in a specified table, the trigger will take effect: Update, insert, or delete. Triggers can query other tables and contain complex SQL statements. They are mainly used to force complex business rules or requirements.

A trigger application maintains and maintains data integrity and legitimacy. How can this problem be understood? That is to say, you can submit any data in the program, and then use the trigger to judge the integrity and validity of the data. Of course, this is just an example. This is not recommended in actual applications, data integrity and legitimacy should be verified by applications.

Next, I will describe the trigger application as an instance.

Set: There are two tables in the current database: "umateriel" and "urecord", which are used to save the item information and the entry and exit records of the item respectively. The structure is as follows:

Reference: umateriel
----------------
Mid int
Mname nvarchar (40)
Mnum int default 0

Urecord
----------------
Rid int
Mid int
Rnum int
Rdate datetime default getdate ()
Rmode bit default 0

Now, the data table is available. Now let's take a look at the actual application.

Now, we want to purchase item A with a quantity of 100. The time is the day and the number of known item A is 1. Generally, we need to perform the following two steps:

Reference: 1. Add an item a purchase record in the urecord record table:
Insert into urecord (MID, rnum, rmode) values (1,100, 0)

2. Update the number of item A in the umateriel inventory table:
Update umateriel set mnum = mnum + 100 Where mid = 1

That is to say, the above two statements must be processed in the Code to ensure inventory accuracy. Take ASP code as an example:

Reference: On Error resume next

'// Set adoconn to the connected ADODB. connection object
With adoconn
'// Start the transaction. Because the multi-step data update operation is involved, the transaction is used here.
. Begintrans
'// Insert the entry record
. Execute ("insert into urecord (MID, rnum, rmode) values (1,100, 0 )")

'// Update the item inventory record
. Execute ("Update umateriel set mnum = mnum + 100 Where mid = 1 ")

'// Determine whether an error has occurred
If err. Number <> 0 then
'// If an error occurs, the transaction is rolled back.
. Rollbacktrans
Response. Write "error! "
Err. Clear
Else
'// If no error occurs, the transaction is committed.
. Committrans
End if
End

The above code can update a warehouse receiving record, but today we want to know the trigger application. What content should be written in the trigger to simplify the above Code? Create a trigger.

The syntax for creating a trigger is long and simplified:

Reference: Create trigger name on table name/view name
{For | after | instead of} {[delete] [,] [insert] [,] [update]}
As
Trigger content (SQL statement)

Reference: SQL Server books online description:
After
The specified trigger is triggered only when all specified operations in the SQL statement are successfully executed. This trigger can be executed only after all reference cascade operations and constraints check are completed successfully.
If only the for keyword is specified, after is the default setting.
You cannot define an After trigger on a view.

Instead
Specify the trigger to be executed, instead of the SQL statement to be triggered, to replace the trigger statement operation.
In a table or view, each insert, update, or delete statement can define up to one instead trigger. However, you can define a view on each view with an instead of trigger.
The instead of trigger cannot be defined in the updatable view with check option. If an instead of trigger is added to the updatable view with the check option specified, SQL Server generates an error. You must use alter view to delete this option before defining the instead of trigger.

{[Delete] [,] [insert] [,] [update]}
It specifies which data modification statements will be executed on a table or view to 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.
For an instead of trigger, the delete option cannot be used on a table with the on Delete cascade operation reference relationship. Similarly, you cannot use the update option on a table that has an on update cascade reference relationship.

Now we create a trigger based on the above syntax (note that the trigger is attached to a table or view, so it can only be created in the table or in the query analyzer ), this trigger is used to automatically update the inventory quantity.

Reference: Create trigger [trupdatematerielnum] on [DBO]. [urecord]
-- Indicates that the trigger is executed after the record is inserted.
After insert
As
-- Number of the current update
Declare @ intid int
-- Number of Current updates
Declare @ intnum int
-- Current Mode
Declare @ intmode int

-- Determines whether a record is updated. @ rowcount is a system function and returns the number of rows affected by the previous statement.
If @ rowcount> 0
Begin
-- Get the number and quantity of items currently inserted. The inserted Table is used to store copies of rows affected by the insert and update statements.
Select @ intid = mid, @ intnum = rnum, @ intmode = rmode from inserted

-- Judge the current mode (0 indicates warehouse receiving, 1 indicates warehouse picking) to update the current item quantity
If @ intmode = 0
Update umateriel set mnum = mnum + @ intnum where mid = @ intid
Else
Update umateriel set mnum = mnum-@ intnum where mid = @ intid
End

Let's take a look at this trigger. First, use the create trigger statement to define a Trigger Based on the urecord table trupdatematerielnum. After insert, this trigger will be executed after the record is inserted, that is to say, when we execute the insert into urecord (MID, rnum, rmode) values (1,100, 0) Statement in the program, the content in the trigger trupdatematerielnum will be automatically executed, that is to say, the inventory will be automatically updated. Now let's change the ASP code.

Reference: On Error resume next

'// Set adoconn to the connected ADODB. connection object
'// Insert the entry record
Adoconn. Execute ("insert into urecord (MID, rnum, rmode) values (1,100, 0 )")

'// Determine whether an error has occurred
If err. Number <> 0 then
Response. Write "error! "
Err. Clear
End if

Is it much simpler? Yes, there is no need to consider inventory here. You only need to insert a sequential account, and the inventory update will be handled by the trigger.

The preceding example is an application of a trigger. The trigger parameters include Delete and update, which are executed at or after deletion and update respectively. The following is an example of a delete trigger.

We add a table to the database to record logs. Its structure is as follows:

Reference: usyslog
--------------
Lid int
Levent nvarchar (200)
Ltime datetime default getdate ()

Now we assume that this table is used to record system logs. When we delete a journal account and record an event in the log table, we create a trigger for deleting a urecord table.

Reference: Create trigger [trdeleterecord] on [DBO]. [urecord]
-- Indicates that the trigger is executed after the record is inserted.
For Delete
As
-- The currently deleted serial number
Declare @ intid int
-- Number of deleted items
Declare @ intnum int
-- Current Mode
Declare @ intmode int

-- Determines whether a record is updated. @ rowcount is a system function and returns the number of rows affected by the previous statement.
If @ rowcount> 0
Begin
-- Obtains information about the row currently deleted. The deleted table is used to store copies of rows affected by the delete and update statements.
Select @ intid = RID, @ intnum = rnum, @ intmode = rmode from deleted

-- Insert a simple deletion event log to the log table.
Insert into usyslog (Levent) values ('the user deleted the serial number: '+ Cast (@ intid as nvarchar (20) +', quantity: '+ Cast (@ intnum as nvarchar (20) +', direction: '+ case @ intmode when 0 then' warehouse receiving 'else' warehouse picking 'end)
End

After the trigger is created, an event log is added to the system log as long as we delete a record in the urecord table.

Through the above brief introduction, I hope that friends who have never used a trigger will have a general idea and an impression on the trigger. If you want to know more about it, SQL Server books online is a good helper for you. The simple application of the trigger will be introduced here, and we will continue later.