Use of SQL Server triggers _mssql

SQL Server creates two private tables for each trigger: the inserted table and the deleted table. The two tables are maintained by the system, and they exist in memory rather than in the database. The structure of the two tables is always the same as the structure of the table to which the trigger is acting, and the two tables associated with the trigger are also deleted after the trigger has finished executing.  

tr>

actions on a table	inserted logical table	deleted logical table
Add record (insert) /p>	store added records	no
Delete record (delete) "	no	Keep deleted records
Modify record (update)	Store more New records	store records before update

Third, for, after, instead OF triggers

After: Triggers execute after the statement that triggers them completes. If the statement fails due to an error, the trigger will not execute. After triggers cannot be specified for a view, only the trigger can be specified for the table. You can specify multiple after triggers for each trigger action (INSERT, UPDATE, delete). If a table has multiple after triggers, you can use Sp_settriggerorder to define which after triggers are fired first and which are last fired. Except for the first and last triggers, the firing order of all other after triggers is indeterminate and uncontrollable.

For: equivalent to After

Instead of: This trigger executes instead of triggering the action. You can specify the instead of triggers on tables and views. Only one instead of trigger can be defined for each trigger action (INSERT, UPDATE, delete). The instead OF triggers can be used to perform enhanced integrity checks on the data values provided in the INSERT and UPDATE statements.

Iv. use of Triggers

1. Create triggers:

Create Trigger trigger_name

On {table_name | view_name}

{for|  After | Instead of }

[ Insert, Update,delete ]

As

Sql_statement

2, delete the trigger:

Drop trigger trigger_name

3, view the existing triggers in the database:

Select * from sysobjects where xtype='TR'

4. View a single trigger:

exec sp_helptext ' trigger name '

5, modify the trigger:

Alter Trigger trigger_name

On {table_name | view_name}

{for|  After | Instead of }

[ Insert, Update,delete ]

as

Sql_statement

V. Examples of triggers

1 . Set up triggers in the Orders table, and when inserting a record into the Orders table, check that the status of the goods in the goods table is 1. Yes, you cannot add the order to the Orders table.

If(object_id('Tgr_orders_insert','Tr')IsNotNull)
Drop Trigger[Tgr_orders_insert];
Go
CreateTrigger[Tgr_orders_insert]
On[Orders]
after  insert
as 
if   ( select  [ status ]  from  [ goods ] , [ inserted ]  where  [ goods ] . Name = [ inserted ] . Name = 1
begin
     print  ' the goods is  being processed '
     print  ' the order  cannot be committed '     
     rollback  transaction    -- rollback, avoid joining

end