16, SQL Basic collation (trigger. Easy Backup)

Trigger (Easy Backup)

is essentially a stored procedure, but not through exec to invoke execution, but by adding and deleting the operation of the database to execute (can manipulate the view)

Disable all triggers

ALTER TABLE teacher disable trigger All

Turn on Trigger all

ALTER TABLE teacher enable trigger all

Create TRIGGER tr_student_delete--Default trigger name

On student--which table to operate on

instead of Delete--for what to do with the table, ' for ' is used after the external statement is executed, ' instead of ' is the statement to be executed directly to execute as inside the statement

As

Delete from score where Sno =108

Delete from student where Sno = 108

Go

Delete from student where Sno = 108

Select *from Student

108 lines are erased.

ALTER TRIGGER tr_student_delete--DEFAULT trigger name

On student--which table to operate on

for Delete

As

INSERT into student values (108, ' Peng Zeng ', ' Male ', 1905-05-22,95033)

Go

Delete from student where Sno = 108

Select *from Student

* Zeng Hua has become a Peng Zeng

Dynamic triggering

Create Trigger Dongtaichufa

On teacher

Instead of delete

As

Begin

DECLARE @tno varchar (20)

Set @tno = (select TNO from Deleted)--select TNO in the execution statement @tno

Update teacher set Tname= ' Zhang San ' where TNO = @tno

End

Go

Delete from teacher where tno= ' 831 '

deleted Store the deleted information, inserted store the changed information.

Execution Delete statement when the tno= inside the trigger. TNO , but not @tno

Create Trigger Tr_teacher_insert

On teacher

For insert

As

Begin

DECLARE @tno varchar (20)

Set @tno = (select TNO from inserted)

---delete from teacher where [email protected]

DECLARE @sex varchar (10)

Set @sex = (select Tsex from teacher where [email protected])

if (@sex = ' male ')

Update teacher set tsex= ' female ' where TNO [email protected]

Else

Update teacher set tsex= ' man ' where TNO [email protected]

End

Go

Insert into teacher values (800, ' Harry ', ' Male ', 1990-09-09, ' Professor ', ' Computer Department ')

16, SQL Basic collation (trigger. Easy Backup)