11-11c# base--Trigger of database

Trigger

Meaning: is essentially a special stored procedure, but not by exec to invoke execution, but by adding or deleting the operation in the database to execute.

Effect: 1. Change the data between the associated tables and add or delete
2. Trigger can manipulate view, build trigger on view
3. Forget the name of the table that created the trigger, disable all

ALTER TABLE teacher disable trigger all--all the triggers of a disabled table with a forgotten name, remember that the name changes all to the name of the table
ALTER TABLE teacher enable trigger all--turn on all triggers of the data table

----Create a Trigger

Format:

Create trigger Tr_ (table name) _ (Add/delete/change operation)

on + (table name)

For,after/instead of + (increment/delete/change operation)---the operation here must be consistent with additions and deletions in the name of the created trigger

As

--the entire process of executing the trigger

Go

(Add/delete/change operation) from (table name) where (condition)---additions and deletions must be consistent with the above additions and deletions

"Caution 1"

The difference between for and instead of:

For/after-refers to a trigger triggered after a delete operation, for which you can use after instead.

Instead of--refers to the deletion before triggering, when the deletion of the first trigger, with the action in the trigger to replace the original operation.

"Caution 2"

1. When operating, be careful to keep the redaction action consistent when using triggers.

2, when there is a primary foreign key constraint between the associated tables, you cannot directly delete the data of the primary key table, you need to first delete the foreign key table data.

"Instance 1"

CREATE TRIGGER Tr_student_delete---creating triggers
On student---Choose which table to build the trigger on
--for Delete--reason: For a trigger triggered after a delete is executed, for can be replaced with after
Instead of delete--instead of +delecte/insert/update
--instead of Delete before triggering, trigger is triggered when delete, replace original operation with action in trigger
As
--delete from score where sno= ' 109 '---writing stored procedures
INSERT into student values (' 108 ', ' Zeng Hwa ', ' Male ', ' 1977-09-01 ', ' Professor ', ' Computer Department ')
--delete from student where sno= ' 109 '
Go
Delete from student where sno= ' 109 '---the primary key table data cannot be deleted when there is a primary foreign key constraint between the table and the table
Select *from Huizong

"Note" When the above error occurs because the primary foreign KEY constraint exists between the student table and the score table, the primary key table cannot be directly manipulated for pruning.

The correct method: First, the Foreign Key table score table in the sno= ' 109 ' column data is deleted before the primary key table operation.

---delete (with instead of)

---delete (with for)

---modified (with instead of)

---modification (with for)

---insert (with for)

---insertion (with instead of)

"Instance 2"

Case 3 Inserts a row of data into the teacher table, changing the gender of the tno= Harry in the inserted data.

11-11c# base--Trigger of database