Database Integration trigger

A trigger is a special stored procedure that is triggered when data in a specific table is inserted, deleted, or modified. It has more refined and complex data control capabilities than the standard functions of the database. The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE statement. SQL

A trigger is a special stored procedure that is triggered when data in a specific table is inserted, deleted, or modified. It has more refined and complex data control capabilities than the standard functions of the database. The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE statement. SQL

A trigger is a special stored procedure that is triggered when data in a specific table is inserted, deleted, or modified. It has more refined and complex data control capabilities than the standard functions of the database.

The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE statement.

In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers affect the execution of multiple data definition language statements, including create, alter, and drop statements.

DML triggers are divided:

1. after trigger (triggered later)

A. insert trigger

B. update trigger

C. delete trigger

2. instead of trigger (previously triggered)

The after trigger requires that the trigger be triggered only after an insert, update, or delete operation is executed and can only be defined on the table. The instead of trigger does not execute the defined operations (insert, update, delete) but only executes the trigger itself. You can either define an instead of trigger on a table or a view.

The trigger has two special tables: The Insert table and the delete table ). These two tables are logical tables and virtual tables. Two tables created by the system in the memory are not stored in the database. Both tables are read-only and can only read data but cannot modify data. The results of these two tables are always the same as the structure of the table applied by the modified trigger. After the trigger is complete, the two tables will be deleted. The data in the Inserted Table is the Inserted or modified data, and the data in the deleted table is the data before or after the update.

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

When updating data, you delete the table record and add a record. In this way, the inserted and deleted tables have updated data records. Note: The trigger itself is a transaction, so you can perform some special checks on the modified data in the trigger. If not, you can use transaction rollback to cancel the operation.

Purpose:

A. Allow/restrict table modifications

B. automatically derive columns, such as auto-increment Fields

C. Forced Data Consistency

D. provide audit and logging

E. Prevent invalid transaction processing

F. Start complex business logic

Examples: instead of is similar to after

1. Create an insert trigger

-- Create the insert trigger if (object_id ('tgr _ classes_insert ', 'tr') is not null) drop trigger tgr_classes_insertgocreate trigger tgr_classes_inserton manage for insert -- insert trigger as -- Define the variable declare @ name varchar (20), @ temp varchar (20 ); -- query the inserted records in the inserted Table. select @ name = m_name, @ temp = m_password from inserted; set @ name = @ name + 'buyingfei '; insert into t_director values (@ name, @ temp, 'male', '2017-10-10 ', '2017 112 ', 1); print' added successfully! ';

Run:

Insert into manage values ('manager', '000000', 'male', '2017-10-10', '2017010', 2)

2. Create a delete trigger

CREATE TRIGGER t1 ON  managefor deleteAS If  (select count(*) from t_cash_housewoker  where m_id=t_cash_housewoker.m_id)>0    BEGIN            Rollback tran      Print 'the manager is userful, you can not delete it'        END

Trigger statement:

delete from manage where m_id='3'

3. Create an update trigger

If (object_id ('tgr _ classes_update_column ', 'tr') is not null) drop trigger tgr_classes_update_columngocreate trigger tgr_classes_update_columnon manage for updateas -- column-Level trigger: if (update (m_name) begin raisError ('system prompt: the class creation time cannot be modified! ', 16, 11); rollback tran; end

Trigger statement:

update manage set m_name='buyingfei' where m_id='1'