m151016_080952_editDetailsCrossesTrigger.php
2.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<?php
use yii\db\Migration;
/**
* Class m151016_080952_editDetailsCrossesTrigger
* заменим существующий триггер на новый и переименуем его на более информативное имя
* с w_details_crosses на w_details_crosses_before_insert
* существующая функциональность сохраняется
* добавляется замена брендов и вставка новых брендов в таблицу w_brand
*/
class m151016_080952_editDetailsCrossesTrigger extends Migration
{
public function init()
{
// используем специальное подключение с супер правами
$this->db = 'full_privileges_db';
parent::init();
}
public function safeUp()
{
$drop_trigger = 'Drop trigger if exists w_details_crosses';
$before_insert_trigger = <<< MySQL
CREATE DEFINER=`root`@`localhost` TRIGGER `w_details_crosses_before_insert` BEFORE INSERT ON `w_details_crosses`
FOR EACH ROW BEGIN
DECLARE vBrand varchar(150);
SET vBrand = '';
SELECT to_brand INTO vBrand from w_brands_replace where from_brand = NEW.BRAND;
IF vBrand = '' or vBrand = NULL then
insert ignore into w_brands (BRAND) values(NEW.BRAND);
else
SET NEW.BRAND = vBrand;
end if;
SET vBrand = '';
SELECT to_brand INTO vBrand from w_brands_replace where from_brand = NEW.CROSS_BRAND;
IF vBrand = '' or vBrand = NULL then
insert ignore into w_brands (BRAND) values(NEW.CROSS_BRAND);
else
SET NEW.CROSS_BRAND = vBrand;
end if;
insert ignore into `w_details_description`(`name`, `brand`)
values (NEW.`ARTICLE`,NEW.`BRAND`),(NEW.`CROSS_ARTICLE`,NEW.`CROSS_BRAND`);
END
MySQL;
$this->execute($drop_trigger);
$this->execute($before_insert_trigger);
}
public function safedown()
{
// вернем все как было
$drop_trigger = 'Drop trigger if exists w_details_crosses_before_insert';
$before_insert_trigger = <<< MySQL
CREATE DEFINER=`italautocomua`@`localhost` TRIGGER `w_details_crosses` BEFORE INSERT ON `w_details_crosses`
FOR EACH ROW BEGIN
insert ignore into `w_details_description`(`name`, `brand`)
values (NEW.`ARTICLE`,NEW.`BRAND`),(NEW.`CROSS_ARTICLE`,NEW.`CROSS_BRAND`);
END
MySQL;
$this->execute($drop_trigger);
$this->execute($before_insert_trigger);
}
}