MySQL Learning Notes (12) - SQL Trigger:

12. SQL triggers

MySQL triggers, like stored procedures, are a program embedded in MySQL. Triggers are events that trigger an action, including INSERT, UPDATAE, and DELETE statements. If triggers are defined, they will be triggered when the database executes these statements to perform the appropriate actions. A trigger is a named database object related to a table that will be activated when a specific event occurs on the table.

12.1 Create triggers

A trigger is a special stored procedure, except that the execution stored procedure is called with a CALL statement, and the execution of a trigger does not need to be called with a CALL statement or started manually, as long as a predefined event occurs, it is automatically called by MySQL.

  • Create a trigger with only one execution statement
    The syntax for creating a trigger is as follows:
create trigger trigger_name trigger_time trigger event on tb1_name for each row trigger_stmt

Where trigger_ The name represents the trigger name, which is specified by the user. trigger_time indicates the trigger time and can be specified as before or after; Trigger_ Events represent trigger events, including INSERT, UPDATE, and DELETE; tbl_name denotes the name of the table on which the trigger was created. trigger_stmt is a trigger execution statement.

[Example] Create a trigger for a single-execute statement with the following code:

create table account(acct_num int,amount decimal(10,2));
create trigger ins_sum before insert on account
for each row set @sum=@sum+NEW.amount

First, create an account table with two fields, acct_num field (defined as int type) and amount field (defined as floating point type); Next, create a name called ins_sum trigger, triggered by summing newly inserted amount field values before inserting data into the data table account.

  • Create triggers with multiple execution statements
create trigger trigger_name trigger_time trigger_event
on tb1_name for each row
begin
 Statement Execution List
end

[Example] Create triggers with multiple execution statements

create table test1(a1 int);
create table test2(a2,int);
create table test3(a3 int not null,auto_increcement primary key);
create table test4(
	a4 int not null auto_increment primary key,
	b4 int default 0
);

delimeter 
create trigger testref before insert on test1
for each row begin
	insert into test2 set a2=NEW.a1;
	delete from test3 where a3=NEW.a1;
	update test4 set b4=b4=1 where a4=NEW.a1
end
delimiter;

insert into test3 (a3) values
(null),(null),(null),(null),
(null),(null),(null),(null),
insert into test4 (a4) values
(0),(0),(0),(0),(0),(0),(0),(0),;

12.2 View Triggers

  • SHOW TRIGGERS statement
show triggers;
  • View trigger information in the triggers table
    In MySQL, INFORMATION_exists for all trigger definitions The TRIGGERS table in the SCHEMA database can be viewed by the query command SELECT with the following syntax:
select * from information_schema.triggers where condition;

From the execution above, you can see that TRIGGER_SCHEMA represents the database in which the trigger is located; TRIGGER_NAME is followed by the name of the trigger; EVENT_OBJECT_TABLE indicates on which data table was triggered; ACTION_STATEMENT represents the specific action performed when the trigger is triggered; ACTION_ORIENTATION is a ROW, meaning triggered on each record; ACTION_TIMING indicates that the trigger time is AFTER; What's left is system-related information

You can also leave the trigger name unspecified so that you can view all the triggers. The command below displays all the trigger information in the TRIGGERS table.

select * from information_schema.trigger \G

Use of 12.3 Triggers

Triggers are table related and activate when INSERT, DELETE, or UPDATE statements are executed on a table. Trigger can be set to activate before or after executing statement
[Example] Create a trigger to update the myevent table after the account table inserts a record

create trigger trig_insert after insert on account for each row insert into myevent values(2,'after insert')

The code above creates a trig_insert trigger that inserts a set of data to table myevent after inserting data to table account

12.4 Delete Trigger

The DROP TRIGGER statement allows you to delete triggers already defined in MySQL. The basic syntax for deleting trigger statements is as follows:

drop trigger [schema_name.] trigger_name

Where schema_name represents the name of the database and is optional. If schema is omitted, the trigger will be discarded from the current database; trigger_name is the name of the trigger to be deleted.
Delete a trigger, code as follows

drop trigger test_db.ins_num;

In the code above, test is the database where the trigger is located, ins_sum is the name of a trigger.

Tags: Database MySQL SQL

Posted on Wed, 24 Nov 2021 12:26:07 -0500 by Frame