日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

實際上“FOR EACH ROW”意味著更新或刪除的每個匹配行。換句話說，我們可以說觸發器并不應用于每一行，它只是說對每個受影響的表行執行觸發器主體。我們可以通過以下示例來說明這一點 –

示例

在此示例中，我們創建兩個表，Sample 和 Sample_rowaffected，如下 –

mysql> Create table Sample(id int, value varchar(20));
Query OK, 0 rows affected (0.47 sec)

mysql> Insert into Sample(id, value) values(100, 'same'),(101,
'Different'),(500, 'excellent'),(501, 'temporary');
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> Select * from Sample;
+------+-----------+
| id   | value     |
+------+-----------+
| 100  | same      |
| 101  | Different |
| 500  | excellent |
| 501  | temporary |
+------+-----------+
4 rows in set (0.00 sec)

mysql> Create table Sample_rowaffected(id int);
Query OK, 0 rows affected (0.53 sec)

mysql> Select Count(*) as ‘Rows Affected’ from sample_rowaffected;
+---------------+
| Rows Affected |
+---------------+
|             0 |
+---------------+
1 row in set (0.10 sec)

登錄后復制

現在，我們將創建一個觸發器，該觸發器在刪除表“Sample”中的任何值之前觸發，如下所示 –

mysql> Delimiter //
mysql> Create trigger trigger_before_delete_sample BEFORE DELETE on
Sample
    -> FOR EACH ROW
    -> BEGIN
    -> SET @count = if (@count IS NULL, 1, (@count+1));
    -> INSERT INTO sample_rowaffected values (@count);
    -> END ;
    -> //
Query OK, 0 rows affected (0.15 sec)
mysql> Delimiter ;

登錄后復制

現在，以下查詢將從表“Sample”中刪除一些值，并且刪除的行數將存儲在 @count 用戶變量中 –

mysql> Delete from Sample WHERE ID >=500;
Query OK, 2 rows affected (0.11 sec)

mysql> Select @count;
+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.03 sec)

登錄后復制

借助以下查詢，我們可以檢查受刪除影響的行的值，插入到sample_rowaffected表中，如下 –

mysql> Select Count(*) as 'Rows Affected' from sample_rowaffected;
+---------------+
| Rows Affected |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> Select * from Sample;
+------+-----------+
| id   | value     |
+------+-----------+
| 100  | same      |
| 101  | Different |
+------+-----------+
2 rows in set (0.00 sec)

登錄后復制

在上面的示例的幫助下，很明顯“FOR EACH ROW”意味著更新或刪除的每個匹配行。

以上就是MySQL 觸發器中的“FOR EACH ROW”如何工作？的詳細內容，更多請關注www.92cms.cn其它相關文章！