Use of SQL Server triggers

Create:

[SQL]View PlainCopy

1. Create Trigger Trigger_name
2. ON {table_name View_name}
3. {for after Instead of }
4. [ Insert, Update,delete]
5. As
6. Sql_statement

To delete a trigger:

[SQL]View PlainCopy

1. Drop Trigger Trigger_name

To view a trigger already in the database: [SQL]View PlainCopy

1. Use Jxcsoftware
2. Go
3. SELECT * from sysobjects where xtype=' TR '

To modify a trigger: [SQL]View PlainCopy

1. Alter Trigger Trigger_name
2. ON {table_name View_name}
3. {for after Instead of }
4. [ Insert, Update,delete]
5. As
6. Sql_statement

triggers disabled and enabled

Disable:

[SQL]View PlainCopy

1. ALTER TABLE trig_example DISABLE TRIGGER trig1
2. GO

Recovery:

[SQL]View PlainCopy

1. ALTER TABLE trig_example ENABLE TRIGGER trig1
2. GO

--Disable all triggers on a table

[SQL]View PlainCopy

1. ALTER table DISABLE TRIGGER All

--Enable all triggers on a table

[SQL]View PlainCopy

1. ALTER table Your table enable TRIGGER All

--Disable all triggers on all tables

[SQL]View PlainCopy

1. exec sp_msforeachtable ' ALTER TABLE? DISABLE TRIGGER All '

--Enable all triggers on all tables

[SQL]View PlainCopy

1. exec sp_msforeachtable ' ALTER TABLE? Enable TRIGGER all '

knowledge points for triggers

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So triggers can be used to implement complex integrity constraints on a table.

Two: SQL Server creates two dedicated tables for each trigger: the inserted table and the deleted table. These two tables.

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So the trigger can be used to implement complex completeness about the table to the ' bundle.

Two: 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 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 that the trigger acts on. The two tables associated with the trigger are also deleted after the trigger execution is complete.
The deleted table holds all rows that are to be removed from the table because of execution of the DELETE or UPDATE statement.
The inserted table holds all rows to be inserted into the table because of an INSERT or UPDATE statement execution.

Three: Instead of and after triggers
SQL Server2000 provides two types of triggers: Instead of and after triggers. The difference between the two triggers is that they are activated in the same way:

Instead of triggers are used to replace T-SQL statements that cause trigger execution. In addition to tables,instead of triggers can also be used for views that extend the update operations that the view can support.
After triggers are executed after a insert,update or deleted statement, and actions such as constraint checking occur before the after trigger is activated. After triggers can only be used for tables.

Each modification action (insert,update and delete) of a table or view can have a instead of trigger, and each modification action of a table can have more than one after trigger.

Workflow for both triggers:

Instead of:

SQL Server receives an Execute SQL statement request, and establishes a temporary inserted table with the deleted table, trigger instead OF trigger, end. Note: This trigger will replace the INSERT, Delete, or update operation that was originally performed;

For example, a table has a trigger on instead of insert, when inserting a row of data into a table, the trigger will replace the "insert a row of data into a table", as to what the action is determined by the contents of the trigger;

Use:

1. Multi-table connected view Data update, you can write the corresponding instead of statements to update multiple tables;

2. Some tables in the database prohibit insert, delete, update, you can write Instead of INSERT, Instead of Delete, Instead of update to block the original Operation .

After/for:

SQL Server receives an Execute SQL statement request, and establishes a temporary inserted table with the deleted table, execute SQL statement, trigger instead OF trigger, end.

. When an operation exists in Instead of and after, the Instead of is bound to execute, and after is not necessarily executed. If a non-activated type of operation is performed in the instead, then the same type of operation in after is not performed. For example, if the INSERT statement exists in instead of and after, then after insert does not execute if the INSERT statement operation is performed in instead of insert that is not the table.

Four: The execution of the trigger process
If a insert﹑update or DELETE statement violates the constraint, then the after trigger does not execute because the check for the constraint occurs before the after trigger is agitated. So after triggers cannot go beyond constraints.

The Instead of trigger can be executed in place of the action that fired it. It is executed when the inserted table and the deleted table have just been created and no other operations have occurred. Because the instead OF trigger executes before the constraint, it can perform some preprocessing on the constraint.

Instance:

1: When a trigger is established in the Orders table, when an order record is inserted into the Orders table, the item status status of the goods table is checked for 1 (being collated), yes, it cannot be added to the Orders table.

[SQL]View PlainCopy

2. Create Trigger OrderInsert
4. On orders
6. After insert
8. As
10. if (select status from goods,inserted
12. where goods. name=inserted.goodsname) =1
14. Begin
16. Print ' The goods is being processed '
18. Print ' The order cannot be committed '
20. Rollback Transaction --rollback, avoid joining
22. End

2: Create an INSERT trigger in the Orders table to reduce inventory in the corresponding item record in the goods table when an order is added.

[SQL]View PlainCopy

1. Create Trigger Orderinsert1
2. On orders
3. After insert
4. As
5. Update goods set storage=storage-inserted.quantity
6. From goods,inserted
7. where
8. Goods. Name=inserted.goodsname

3: Create a DELETE trigger on the goods table to implement cascading deletions of the goods table and the Orders table.

[SQL]View PlainCopy

1. Create Trigger Goodsdelete
2. On goods
3. After delete
4. As
5. Delete from orders
6. where Goodsname in
7. (Select name from deleted)

instead of the use of triggers

The main advantage of the INSTEAD of triggers is that views that cannot be updated support updates. Views based on multiple base tables must use the

INSTEAD of triggers to support INSERT, UPDATE, and delete operations that reference data from multiple tables. Another advantage of the INSTEAD of triggers is that you can write logic code that rejects portions of the batch while allowing other parts of the batch to succeed.

Transact -SQL statements Create two base tables, a view, and a INSTEAD of trigger on a view . The following table separates personal data from business data and is the base table for the view.

/* Define the instead of INSERT trigger on the view to insert data into one or more base tables. */

--Department table

CREATE TABLE Dept

(

d_id int PRIMARY KEY,

D_name varchar (20)

)

--Employee table

CREATE TABLE EMP

(

e_id int PRIMARY KEY,

E_name varchar (20),

d_id int References Dept (d_id)

)

SELECT * FROM emp

Drop View V

Create VIEW V

As

Select E_id,e_name,d.d_id,d_name from emp e,dept D where e.d_id=d.d_id

SELECT * FROM V

Insert into v values (1001, ' Zhang Shan ', 101, ' sales department ')

/******************* using instead OF triggers ******************/

Drop Trigger De_em_insert

Go

Create Trigger De_em_insert

On V

Instead of insert

As

Begin

if (Not EXISTS (select d.d_id from Dept d, inserted i where d.d_id = i.d_id))

INSERT INTO Dept Select D_id,d_name from inserted

if (Not EXISTS (select e.d_id from emp E, inserted i where e.d_id = i.d_id))

INSERT INTO EMP Select e_id,e_name,d_id from inserted

Else

Update emp Set e_id = I.e_id,e_name = I.e_name from emp e, inserted i where e.d_id = i.d_id

End

You can define INSTEAD of delete triggers in a view or table in place of the standard operation of a DELETE statement. Typically, you define INSTEAD of DELETE triggers on A view to modify data in one or more base tables.    

The INSTEAD of UPDATE trigger can be defined on the view in place of the standard operation of the UPDATE statement. Typically, you define INSTEAD of update triggers on a view to modify data in one or more base tables

Use of SQL Server triggers