Shulou(Shulou.com)06/01 Report--

trigger

It is a special storage procedure. General stored procedures are called directly through stored procedure names, while triggers are mainly

Executed triggered by an event (addition, deletion, modification). It is automatically enforced when the data in the table changes.

There are two common triggers: after(for), instead of, for insert, update, delete events.

after(for) indicates that the trigger is executed after the code is executed

Instead of means to replace your actions with triggers that have already been written before executing the code

Trigger syntax:

create trigger name on action table

for|after instead of

update|insert|delete

as

SQL statement

Trigger characteristics:

1. Trigger is a storage process that is automatically executed when adding, deleting or modifying a table. Triggers, often used to enforce business rules, are high-level constraints that are triggered by events to be enforced.

Trigger is a special transaction unit that can reference columns in other tables to execute special business rules or data logic relationships. When an error occurs, you can perform a rollback transaction to roll back the entire trigger along with the T-SQL statement that triggered it (without displaying the declaration begin transaction).

3. Two temporary tables will be used by each trigger:

deleted temporary table: used to temporarily store copies of deleted record rows (including data rows affected by delete and update statements);

Note: Deleted rows are first deleted from the original table and saved to the trigger table. It is then deleted from the trigger table and saved to the deleted table.

inserted temporary table: used to temporarily store inserted record line copies (including data lines affected by insert and update statements);

Characteristics of deleted and inserted tables:

> The table structure of these two tables is the same as the table that the trigger acts on;

> These two tables are logical tables and managed by the system;

> These two tables are dynamically resident in memory (not stored in the database) and are deleted when the trigger completes its work;

> These two tables are read-only, that is, they can only be viewed using select statements (users cannot change them directly);

4. The trigger created (insert, delete, update) is triggered after the data row of the original table has been modified. Therefore, triggers are executed after constraint checking.

--Prevent users from inserting data (actually insert first, then delete immediately!)

create trigger tr_insert on bank

for --for indicates the action after execution

insert --i.e. the insert operation was performed first and the insert record was saved in the temporary table

as

--After performing the insertion, delete the record just inserted in the newly generated table.

--and the id of the record just inserted obtained at this time is obtained through temporary table inserted

delete * from bank where cid=(select cid from inserted)

After generating the trigger above, when the user enters the insert statement, the effect will not be seen!

For example, insert into bank values ('0004 ', 10000) is not inserted into the database.

--Add 10 yuan to the account of whoever deletes it

create trigger tr_dalete on bank

instead of

delete

as

update bank balance=balance+10 where cid=(select cid from deleted)

After this trigger is generated, when the user enters the delete statement, the corresponding id is not deleted, and his account is increased by 10 yuan.

delete from bank where cid='0002', after executing this sentence, the account number 0002 will increase by 10 yuan

Reference article: www.cnblogs.com/xugang/archive/2010/02/20/1669619.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.