Database trigger introduction and instance, database trigger instance

Database trigger introduction and instance, database trigger instance
Trigger

It is a special stored procedure. A general stored procedure is called directly through the stored procedure name, and the trigger is mainly

Triggered by an event (add, delete, or modify) and executed. It is automatically enforced when the data in the table changes.

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

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

Instead of indicates that your operation is replaced by a previously written trigger before code execution.

 

Trigger Syntax:

Create trigger name on operation table

For | after instead

Update | insert | delete

As

SQL statement

 

Trigger implementation schematic

 

Trigger example

Example1

 

-- Prohibit users from inserting data (in fact, it is to insert data first and then delete it immediately !)

 

Create trigger tr_insert on bank

 

For -- for indicates the operation after execution

 

Insert -- the insert operation is performed first, and the insert record is saved in the temporary table.

 

As

 

-- After the insert operation is completed, delete the newly inserted record in the newly generated table,

 

-- At this time, the inserted Record id is obtained through the temporary table inserted.

 

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

 

 

 

After the above trigger is generated, when the user enters the insert statement again, it will not be able to see the effect!

 

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

 

Example2

-- If the account is deleted, the account will be charged with 10 yuan.

Create trigger tr_dalete on bank

Instead

Delete

As

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

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

 

For example, delete from bank where cid = '20160301'. After executing this statement, the account number 0002 will increase by 10 yuan.