Introduction to database triggers and examples

Trigger

It is a special kind of stored procedure. The general stored procedure is called directly through the stored procedure name, and the trigger is primarily

Executed through the triggering of events (add, delete, change). It is automatically enforced when the data in the table has changed.

There are two common types of triggers: After (for), instead of, for INSERT, UPDATE, delete events.

After to represents the execution of the code, the trigger is executed

Instead of means to replace your operation with a written trigger before executing the code

Trigger Syntax :

Create trigger trigger name on action table

For|after instead of

Update|insert|delete

As

SQL statements

Schematic diagram of trigger implementation

Trigger Example

Example1

--Prevent users from inserting data (actually inserting it first, then deleting it immediately!) )

Create Trigger Tr_insert on bank

For--for represents the action after execution

Insert – Inserts are performed first, and insert records are saved in the staging table

As

--After the insert is executed, delete the record that you just inserted in the newly generated table.

--and the ID of the record you just inserted is obtained through the temp table inserted.

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

After you generate the above trigger, you won't see the effect when the user enters the INSERT statement again!

For example: INSERT into bank values (' 0004 ', 10000) are inserted into the database.

Example2

--delete who's account plus 10 yuan

Create Trigger Tr_dalete on bank

Instead of

Delete

As

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

After the trigger is generated, when 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= ' 0002 ', after executing this sentence, the account number 0002 will increase by 10 yuan

Introduction to database triggers and examples