m151016_090927_editDetailsTrigger.php
2.97 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_090927_editDetailsTrigger
* заменим существующий триггер на новый и переименуем его на более информативное имя
* с w_details на w_details_before_insert
* существующая функциональность сохраняется
* добавляется замена брендов и вставка новых брендов в таблицу w_brand,
* а также поиск и замена налогов товаров по таблице w_details_replace
*/
class m151016_090927_editDetailsTrigger extends Migration
{
public function init()
{
// используем специальное подключение с супер правами
$this->db = 'full_privileges_db';
parent::init();
}
public function safeUp()
{
$drop_trigger = 'Drop trigger if exists w_details';
$before_insert_trigger = <<< MySQL
CREATE DEFINER=`root`@`localhost` TRIGGER `w_details_before_insert` BEFORE INSERT ON `w_details`
FOR EACH ROW BEGIN
DECLARE vBrand varchar(150);
DECLARE vArticle varchar(100);
SET vBrand = '';
SET vArticle = '';
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_name, to_brand INTO vArticle, vBrand from w_details_replace where from_name = NEW.ARTICLE AND from_brand = NEW.BRAND LIMIT 1;
IF vArticle != '' then
SET NEW.BRAND = vBrand;
SET NEW.ARTICLE = vArticle;
end if;
INSERT INTO `w_details_description`(`name`, `brand`, `supplier_description`, `article`)
values (NEW.`ARTICLE`,NEW.`BRAND`,NEW.`DESCR`,NEW.`FULL_ARTICLE`)
on duplicate key update `supplier_description` = if (`supplier_description` = '',values(`supplier_description`),`supplier_description`);
END
MySQL;
$this->execute($drop_trigger);
$this->execute($before_insert_trigger);
}
public function safedown()
{
// вернем все как было
$drop_trigger = 'Drop trigger if exists w_details_before_insert';
$before_insert_trigger = <<< MySQL
CREATE DEFINER=`italautocomua`@`localhost` TRIGGER `w_details` BEFORE INSERT ON `w_details`
FOR EACH ROW BEGIN
INSERT INTO `w_details_description`(`name`, `brand`, `supplier_description`, `article`)
values (NEW.`ARTICLE`,NEW.`BRAND`,NEW.`DESCR`,NEW.`FULL_ARTICLE`)
on duplicate key update `supplier_description` = if (`supplier_description` = '',values(`supplier_description`),`supplier_description`);
END
MySQL;
$this->execute($drop_trigger);
$this->execute($before_insert_trigger);
}
}