最後更新: 2022-08-15

目錄

• Trigger
• Set
• Definer
• Delimiter
• BEGIN ... END
• Event

 

---

Trigger

 

Create

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

• trigger_time: { BEFORE | AFTER }
• trigger_event: { INSERT | UPDATE | DELETE }
• trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

 * A trigger is associated with a table

Permission

• CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger
• If the DEFINER clause is present, the privileges required depend on the user value
• If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege

Drop

DROP TRIGGER [IF EXISTS] [database.]trigger_name

 * DROP TRIGGER requires the TRIGGER privilege for the table

Show

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

 

---

DEFINER

 

The DEFINER clause specifies the MySQL account to be used when

checking access privileges at event execution time.

 

---

Delimiter

 

By default, mysql itself recognizes the semicolon as a statement delimiter,

so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

This is so you can write ";" in your trigger definition

In the mysql command-line client, this is handled with the delimiter command

Example

mysql> delimiter //

mysql> create trigger au_t1
-> after update
-> on t1
-> for each row
-> begin
-> insert into u1 values(old.id, old.name, new.id, new.name);
-> end//

mysql> delimiter ;

 

 

---

AFTER 與 BEFORE

 

The keyword AFTER / BEFORE indicates the trigger action time.

Indicate that the trigger activates before or after each row to be modified.

e.g.

BEFORE

CREATE TRIGGER update_created_time
BEFORE INSERT ON your_table_name
FOR EACH ROW
SET NEW.created_time = NOW();

AFTER

CREATE TRIGGER update_created_time
AFTER INSERT ON your_table_name
FOR EACH ROW
UPDATE update_created_time
SET created_time = NOW()
WHERE id = NEW.id;

 

---

BEGIN ... END

 

As with stored routines, you can use compound-statement syntax

  in the "DO" clause by using the BEGIN and END keywords

=> 一次過 execute multiple statements

 * BEGIN ... END blocks can be nested

Example

BEGIN
    SET New.create_time = NOW();
    SET New.server = (select server FROM gateway ORDER BY RAND() LIMIT 1);
END

 

---

Example

 

CREATE TABLE IF NOT EXISTS `booking` (
  `id` int(11) NOT NULL,
  `createDateTime` datetime DEFAULT NULL,
  `modifyDateTime` datetime DEFAULT NULL,
  `remark` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- for update record
DELIMITER $$
CREATE TRIGGER `booking_trigger1` 
 BEFORE INSERT ON `booking` FOR EACH ROW 
 SET New.createDateTime = NOW();
$$

-- for update record
CREATE TRIGGER `booking_trigger2`
 BEFORE UPDATE ON `booking` FOR EACH ROW
 SET New.modifyDateTime = NOW();
$$
DELIMITER ;

DELIMITER

由於 "DO" clause 的 statements 要用上 ";", 所以要轉 "DELIMITER"

FOR EACH ROW

which occurs once for each row affected by the triggering event.

修改前後的 Value

• NEW.fieldname
• OLD.fieldname

Notes

 * 在 phpMyAdmin 建立 TRIGGER 時, 在 Definition 直接輸入

SET NEW.createDateTime = NOW();

不用 "FOR EACH ROW"

 

---

New 與 Old

 

Case: Insert data 到 Table "booking" 時

Trigger

update booking set createDateTime = CURRENT_TIMESTAMP() where createDateTime=NULL;

Error

Can't update table in ? trigger because it is already used by statement ...

解決

We can access the new values by using New.fieldname (if doing an INSERT)

The old values with Old.fieldname (if doing an UPDATE)

 

---

---

Event

 

# Help

mysql> help create event

Name: 'CREATE EVENT'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

 

AT                                                       # 一次性的觸發

EVERY                                                 # 如果是多次的觸發

ON COMPLETION NOT PRESERVE          # 也就是在事件完成後, 該事件就會自動被刪除。

 

啟動 scheduler

# 查看 scheduler

select @@event_scheduler;

# Enable

set global event_scheduler = ON;

# Event scheduler thread

show processlist\G;

* 會見到有 User event_scheduler@localhost, State: Daemon

# State

Waiting on empty queue

# Permanently activate scheduler

/etc/my.cnf

[mysqld]
event_scheduler=ON

service mysqld restart

# Grant permission

# CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created.

# It might also require the SUPER privilege, depending on the DEFINER value

grant event on stanley.* to 'bob'@'localhost';

REVOKE EVENT ON myschema.* FROM jon@ghidora;

* Important *

Revoking the EVENT privilege from a user does not delete or disable any events that may have been created by that user.

An event is not migrated or dropped as a result of renaming or dropping the user who created it.

 

# Create Event

 

# 停止 Event

ALTER EVENT my_event DISABLE;

 

# Checking

use DB_Name

#

show events;

#

SHOW CREATE EVENT

# 詳細 Events 內容

Event Information: mysql.event table

* the times just mentioned are retrieved as UTC values.

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='group_list'

# 刪除 Events

DELETE FROM mysql.event WHERE db = 'mydb' AND name = 'group_list'

 

Example

# Saves total number of sessions then clears the table each day

Delimiter |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

| ;

# Mail List

BEGIN
SET @@group_concat_max_len = 20480;
UPDATE alias SET goto=(
        SELECT GROUP_CONCAT( DISTINCT username ORDER BY username ASC )
        FROM mailbox
        WHERE domain = "x.x" AND active=1)
    WHERE address="[email protected]" AND domain="x.x";
END