SQL Server trigger creation, deletion, modification, and viewing sample code

1. A trigger is a special stored procedure. It cannot be explicitly called. Instead, it is automatically activated when a record is inserted, updated, or deleted into the table. Therefore, triggers can be used to implement complex integrity constraints on tables.

2. SQL Server creates two special tables for each trigger: The inserted Table and the deleted table. These two tables.

1. A trigger is a special stored procedure. It cannot be explicitly called. Instead, it is automatically activated when a record is inserted, updated, or deleted into the table. Therefore, triggers can be used to implement complex integrity constraints on tables.

2. SQL Server creates two special 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 structure of these two tables is always the same as that of the table to which the trigger is applied. After the trigger is executed, the two tables related to the trigger are also deleted.
The deleted table stores all rows to be deleted from the table due to executing the delete or update statement.
The inserted Table stores all rows to be inserted into the table for execution of the insert or update statement.
3. Instead of and after triggers
SQL Server2000 provides two triggers: instead of and after. The difference between the two triggers is that they are activated in the same way:

The instead of trigger is used to replace the T-SQL statement that causes the trigger to execute. In addition to tables, the instead of trigger can also be used for views to expand the update operations supported by views.

The After trigger is executed after an insert, update, or deleted statement and performs a constraint check before the after trigger is activated. The After trigger can only be used for tables.

Each modification action (insert, update, and delete) of a table or view can have an instead of trigger. Each modification action of a table can have multiple after triggers.
4. Trigger Execution Process
If an insert, update, or delete statement violates the constraints, the after trigger will not be executed because the check on the constraints occurs before the after trigger is excited. Therefore, the after trigger cannot exceed the constraints.

The instead of trigger can be executed instead of the action that inspires it. It has just been created in the inserted and deleted tables, and is executed when no other operations have been performed. Because the instead of trigger is executed before the constraint, it can pre-process the constraint.

5. Use T-SQL statements to create a trigger

The basic statement is as follows:
Create trigger trigger_name
On {table_name | view_name}
{For | after | instead}
[Insert, update, delete]
As
SQL _statement

6. delete a trigger:

The basic statement is as follows:

Drop trigger trigger_name

7. view existing triggers in the database:

-- View existing triggers in the database
Use jxcsoftware
Go
Select * From sysobjects where xtype = 'tr'

-- View a single trigger
Exec sp_helptext 'trigger name'

8. Modify the trigger:

The basic statement is as follows:
Alter trigger trigger_name
On {table_name | view_name}
{For | after | instead}
[Insert, update, delete]
As
SQL _statement

9. Example:
1: Create a trigger in the orders table. When an order record is inserted into the orders table, check whether the status of goods in the goods table is 1 (being sorted) yes, you cannot add this order to the orders table.
Create trigger orderinsert
On orders
After insert
As
If (select status from goods, inserted
Where goods. Name = inserted. goodsname) = 1
Begin
Print 'the goods is being processed'
Print 'The order cannot be committed'
Rollback transaction -- roll back to avoid joining
End
2: Create an insert trigger in the orders table to reduce the inventory in the corresponding item records of the goods table when adding an order.
Create trigger orderinsert1
On orders
After insert
As
Update goods set storage = storage-inserted.quantity
From Goods, inserted
Where
Goods. Name = inserted. goodsname
3: Create a deletion trigger in the goods table to achieve cascading deletion of the goods table and the orders table.
Create trigger goodsdelete
On Goods
After Delete
As
Delete from orders
Where goodsname in
(Select name from deleted)
4: Create an update trigger in the orders table to monitor the orderdate column of the orders table, so that it cannot be manually modified.
Create trigger orderdateupdate
On orders
After update
As
If Update (orderdate)
Begin
Raiserror ('orderdate cannot be modified', 10, 1)
Rollback transaction
End
5: Create an insert trigger in the orders table to ensure that the goods name inserted to the orders table must exist in the goods table.
Create trigger orderinsert3
On orders
After insert
As
If (select count (*) from goods, inserted where goods. Name = inserted. goodsname) = 0
Begin
Print 'NO ENTRY in goods for this order'
Rollback transaction
End

6: Create an insert trigger for the orders table to ensure that the product information inserted to the orders table must be added to the order table.

Alter trigger addorder
On orders
For insert
As
Insert into order
Select inserted. ID, inserted. goodname, inserted. Number from inserted