SQL Server triggers

1. A trigger is a special stored procedure and cannot be explicitly called. It can only be automatically activated when an insert, update, or delete operation is performed on a table. Therefore, triggers can be used to implement complex integrity constraints on tables.

2. SQL Server creates two dedicated tables for each trigger: The Inserted Table and the Deleted table. These two tables are maintained by the system and exist in the memory instead of in the database. The structures of these two tables are always the same as those of the table to which the trigger applies. After the trigger is executed, the two tables associated with the trigger are also deleted.

Table operations	Inserted logical table	Deleted logical table
Insert)	Store added records	None
Delete)	None	Store deleted records
Update)	Store updated records	Store records before update

3. for, after, and instead of triggers

After: triggers are executed after their statements are triggered. If the statement fails due to an error, the trigger will not execute. You cannot specify an after trigger for a view. You can only specify this trigger for a table. You can specify multiple after triggers for each trigger operation (insert, update, delete. If the table has multiple after triggers, you can use sp_settriggerorder to define which after trigger is first triggered and which is last triggered. Except the first and last triggers, the excitation sequence of all other after triggers is uncertain and uncontrollable.

For: equivalent to after

Instead of: This trigger is used to trigger the operation. You can specify the instead of trigger on tables and views. Only one instead of trigger can be defined for each trigger operation (insert, update, delete. The instead of trigger 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 a trigger:

Create trigger trigger_name

On {table_name | view_name}

{For | After | Instead}

[Insert, update, delete]

As

SQL _statement

2. delete a trigger:

Drop trigger trigger_name

3. view 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}

[Insert, update, delete]

As

SQL _statement

5. Trigger instance

1. Create a trigger in the Orders table. When a record is inserted into the Orders table, check whether the item status of the goods table is 1. Yes, you cannot add this order to the Orders table.

If (object_id ('tgr _ orders_insert ', 'tr') is not null)
Drop trigger [tgr_orders_insert];
Go
Create trigger [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 -- roll back to avoid joining

End