SQL Server (eight)--triggers

A trigger is a special kind of stored procedure that executes automatically when a table Update,insert or DELETE statement is executed, with no parameters and no return value;

First, trigger type

1. For Trigger

Triggered after an action is executed (the code in the trigger executes after the deletion is completed), the for trigger cannot be specified for the view, and the trigger can be specified only for the table.

2. Instead OF trigger

Can be understood as an alternative to triggering the execution, after writing this, write the execution code is useless, the trigger code is covered by the

Second, create a trigger

Create trigger Insert_student-- naming conventions

On student-- for which table

For insert-- to which action to trigger

As

Code snippet to trigger execution

Go

For example:

Create Trigger Delete_info  on  ofdeleteas    gocreatetrigger  Delete_ Nation onforDeleteas Go    

View Code

NOTE: Triggers are commonly used for cascading deletions:

Create Trigger Delete_student
On student
Instead of delete
As

-- If you want to delete student table data, you need to cascade delete
DECLARE @sno varchar (20);
Set @sno = Sno from deleted--deleted fixed format, in order to delete the data that can be deleted, do not delete, but show them, in this get the data to be deleted Sno, and then first delete the other table in this SNO data
Delete from score where Sno = @sno;
Delete from student where Sno = @sno;

Go

For example:

Create TriggerDelete_info onInfoinstead of Delete as    Declare @c varchar( -)    Select @c =Code fromdeletedDelete  from  Work whereInfocode=@c    Delete  fromFamilywhereInfocode=@c    Delete  fromInfowhereCode=@cGo

View Code

SQL Server (eight)--triggers