Stored Procedure 6-trigger and Stored Procedure 6

Stored Procedure 6-trigger and Stored Procedure 6

I. triggers

A trigger is a table-related named database object. This object is called when a specific event occurs on the table. It is a special stored procedure related to table events. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution.

Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionary.
A good feature of a trigger is that the trigger can disable or roll back changes that violate the integrity of the reference, thus canceling the data modification.

1. Create

Syntax:

Create trigger trigger_name trigger_time trigger_event ON tbl_name for each row trigger_stmt

Trigger_time: the trigger action time. It can be BEFORE or AFTER to indicate that the trigger is triggered BEFORE or AFTER its statement is activated.

Trigger_event: specifies the type of statement used to activate the trigger. Trigger_event can be one of the following:

• INSERT: the trigger is activated when a new row is inserted into a table, for example, through INSERT, load data, and REPLACE statements.
• UPDATE: activate a trigger when changing a row. For example, use the UPDATE statement.
• DELETE: the trigger is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

For a given table with the same trigger action time and event, there cannot be two triggers. For example, a table cannot have two before update triggers. However, there can be one before update trigger and one before insert trigger, one before update trigger and one after update trigger.

Trigger_stmt: The statement executed when the trigger is activated. If you want to execute multiple statements, you can use the in... END compound statement structure. In this way, you can define the trigger for executing multiple statements.

A trigger cannot CALL a stored program that returns data to the client, nor use a dynamic SQL statement that uses the CALL Statement (allow the stored program to return data to the trigger through parameters ). A trigger cannot START or end a TRANSACTION explicitly or implicitly, such as start transaction, COMMIT, or ROLLBACK.

2. View

Show triggers; -- View All triggers show create trigger ins_sum; -- view a specific TRIGGER

3. Delete

Drop trigger trigger_name; -- delete a TRIGGER

Note: When you upgrade MySQL versions earlier than MySQL 5.0.10 to version 5.0.10 or later (including all MySQL 5.1 versions), you must discard all triggers before the upgrade, and re-create them later. Otherwise, the drop trigger will not work after the upgrade.

Example:

Drop table if exists 'account'; create table account (acct_num INT, amount DECIMAL (10, 2); create trigger ins_sum before insert on accountFOR each row set @ sum = @ sum + NEW. amount; -- adds up the values inserted into the amount column.
SET @ sum = 0; insert into account VALUES (137, 14.98), (141,193 7.50), (97,-100.00); SELECT @ sum AS 'total amount inserted ';

After the INSERT statement is executed, the value of @ sum is 14.98 + 1937.50-100, or 1852.48.
The execution result is as follows:

Ii. old and NEW

Differences between keywords new and OLD:

INSERT: only NEW
UPDATE: before old, AFTER NEW
DELETE: only OLD

Columns named after OLD are read-only. You can reference it, but you cannot change it. If you have SELECT permission for a column named NEW, you can reference it. You can use "set new. col_name = value" to change its value. This means that you can use a trigger to change the value to be inserted into a new row or update the value of a row. OLD and NEW are MySQL extensions for triggers.

Iii. Instances

The table structure is as follows:

DROP TABLE IF EXISTS `person`;CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(255) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `password` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

1. insert

DROP TRIGGER IF EXISTS trigger_insert;-- beforeCREATE TRIGGER trigger_insert BEFORE INSERT ON personFOR EACH ROW  SET @info = new.username, new.age = new.age + 2; INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');SELECT @info;

Called before insertion, age is added with 1, and the username of the record is recorded. Only before can be used for insert to modify the value of new. age. It cannot be modified after.

2. update

1 drop trigger if exists trigger_update_before; 2 create trigger trigger_update_before before update on person 3 for each row 4 BEGIN 5 SET @ infoname1 = new. username; -- Record the new name 6 SET new. age = new. age + 3; -- modify age 7 END; 8 9 drop trigger if exists trigger_update_after; 10 create trigger trigger_update_after update on person11 for each row 12 SET @ infoname2 = old. username; -- record the name before modification 13 14 SELECT * FROM person WHERE id = 1; 15 UPDATE person SET username = 'lisi', age = 0 WHERE id = 1; 16 SELECT * FROM person WHERE id = 1; 17 SELECT @ infoname1 AS newname, @ infoname2 AS oldname;

After 14 rows are executed, the results are as follows:

The results of Lines 15 and 16 are as follows:

Age changed by trigger

After 17 rows are executed, the result is as follows:

The trigger has obtained the user name before and after the change.

3. delete

1 drop trigger if exists trigger_delete_after; 2 create trigger trigger_delete_after delete on person3 for each row 4 SET @ infoname3 = old. username; -- get the name of the deleted user 5 6 delete from person WHERE id = 1; 7 SELECT * FROM person WHERE id = 1; 8 SELECT @ infoname3 AS oldname;

The result of the execution of the 7th rows is as follows:

The record has been deleted.

The result of the execution of the 8th rows is as follows:

Displays the User Name of the deleted record.

4. Trigger call Stored Procedure

You can use the BEGIN... END structure in a trigger to define a trigger that executes multiple statements. You can also use other syntaxes, such as conditions and loops, In the BEGIN block. We can encapsulate these statements into the Stored Procedure for the trigger to call.

Example:

1 drop table if exists 'person '; 2 create table 'person' (3 'id' int (11) not null AUTO_INCREMENT, 4 'username' varchar (255) default null, 5 'age' int (11) default null, 6 'Password' varchar (255) default null, 7 primary key ('id') 8) ENGINE = InnoDB AUTO_INCREMENT = 18 default charset = utf8; 9 insert into person (username, age, password) VALUES ('hangsan ', '21', '123 '); 10 11 drop procedure if exists pro_person_update; 12 create procedure pro_person_update (13 INOUT age INT (11) 14) 15 BEGIN16 IF age <0 THEN -- age less than 017 SET age = 0; 18 ELSEIF age> 100 THEN -- age greater than 10019 SET age = 100; 20 end if; 21 END; 22 23 drop trigger if exists trgger_proceduce_person_update; 24 create trigger trgger_proceduce_person_update before update on person25 for each row 26 CALL pro_person_update (new. age); 27 28 SELECT * FROM person WHERE id = 1; 29 UPDATE person SET age =-3 WHERE id = 1; 30 SELECT * FROM person WHERE id = 1;

Because the trigger generated in the above three examples will have an impact on this example, the table structure is re-created.

The result of the execution of the 28th rows is as follows:

After the 29th and 30th rows are executed, the result is as follows:

Age is 0, and the stored procedure has been called by the update before trigger.

During the trigger execution, MySQL handles errors as follows:

• If the BEFORE trigger fails to be executed, the SQL statement also fails.
• The AFTER trigger is executed only when the BEFORE trigger (if any) and SQL statement are successfully executed.
• If an error occurs during the execution of the BEFORE or AFTER trigger, the entire statement of the trigger will fail to be called.
• For transactional tables (the tables created on innodb are transactional tables, which are transaction-safe .), If the trigger fails (and the entire statement fails as a result), all changes made to the statement will be rolled back. This type of rollback cannot be performed for non-transactional tables. Therefore, any changes made before the failure even if the statement fails.

What is stored procedure and trigger?

A stored procedure is equivalent to a packaged SQL syntax and can contain complex SQL operations. When a program is called, the stored procedure can be executed in one sentence to complete complex database operations.
A trigger is also a packaged SQL syntax, but the condition for executing the trigger is that the table of the modified trigger is changed. for example, I can write a trigger and set it to synchronize another table when inserting a piece of data into this table. in the program, I just need to insert a piece of data into the database, and the other table can be updated at the same time.
The advantage of using stored procedures and databases is that you can make full use of database resources and reduce program code. The programmer's work is simpler and the code written is more concise and clearer. of course, to really understand what the program is doing, you still need to understand the stored procedures and triggers.
These are my own experiences. You can have a choice.

How to Create a trigger in the Stored Procedure

(1). Stored Procedure:
Create procedure for_select (name of the trigger you want to use)
Delare @ name char (8) output (for output), @ age int output (for output), @ phone_num char (11) (input)
As
Select @ name = name (column name), @ age = age (column name) from table_name (table name)
Where phone_num (column name) = @ phone_num

(2) triggers
Create tigger for_update
On table_name
After update
As
If (update (phone_num ))
Update table_name2
Set phone_num = I. phone_num
From deleted d, inserted I
Where table_name.phone_num = d. phone_num
End
Deleted and inserted are the default temporary tables in SQL. Stores the original and inserted values. This trigger is used to update phone_num in table_name1 when phone_num in table_name is updated! Note that the trigger can only be used in update. insert. delete. select and cannot be used to create a table ............