View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000656 | ГРАНИТ | General | public | 2014-05-20 16:48 | 2014-06-17 13:39 |
| Reporter | ruslan | Assigned To | barry | ||
| Priority | urgent | Severity | feature | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Summary | 0000656: +БД - новые поля в таблице Price; PriceGoods | ||||
| Description | Нужно сделать поля в табличках LastEdit по аналогии с остальными таблицами, я табе крипты сброшу таблиц и для однообразия Добавь поле UserName и его заполнение из программы по аналогии с остальными таблицами где это сделано. | ||||
| Tags | No tags attached. | ||||
|
|
pricegoods.txt (5,097 bytes)
/******************************************************************************/
/*** Generated by IBExpert 20.05.2014 10:58:40 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE GENERATOR PRICEGOODS_ID;
CREATE TABLE PRICEGOODS (
ID INTEGER NOT NULL,
GDSKEY INTEGER NOT NULL,
PRICE DOUBLE PRECISION,
PRICEID INTEGER NOT NULL,
CPRICE DOUBLE PRECISION,
USERNAME VARCHAR(20),
LASTEDIT TIMESTAMP DEFAULT Current_TimeStamp
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE PRICEGOODS ADD CONSTRAINT PRICEGOODS_PK PRIMARY KEY (ID);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE PRICEGOODS ADD CONSTRAINT PRICEGOODS_GDSKEY FOREIGN KEY (GDSKEY) REFERENCES GOODS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PRICEGOODS ADD CONSTRAINT PRICEGOODS_PRICEID FOREIGN KEY (PRICEID) REFERENCES PRICELIST (ID) ON DELETE CASCADE ON UPDATE CASCADE;
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: PRICEGOODS_AI */
CREATE OR ALTER TRIGGER PRICEGOODS_AI FOR PRICEGOODS
ACTIVE AFTER INSERT POSITION 0
AS
declare variable aBASE_PRICE_ID INTEGER;
begin
/*insert into PALM_PRICELISTGOODS (PRICEGOODS_ID,MODIFY) values (NEW.ID,0); */
/* Изменим цену базового прайс-листа в ТОВАРАХ */
select BASE_PRICE_LIST_ID from MOBIL_MAIN where ID = 1
into :aBASE_PRICE_ID;
if (NEW.PRICEID = :aBASE_PRICE_ID) then
update GOODS SET BASE_PRICE = NEW.PRICE
where ID = NEW.GDSKEY;
end
^
/* Trigger: PRICEGOODS_AU */
CREATE OR ALTER TRIGGER PRICEGOODS_AU FOR PRICEGOODS
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable aBASE_PRICE_ID INTEGER;
begin
/* insert into PALM_PRICELISTGOODS (PRICEGOODS_ID,MODIFY) values (NEW.ID,0); */
/* update PALM_PRICELISTGOODS set MODIFY = 1
where PRICEGOODS_ID = NEW.ID; */
/* Редактирование базовой цены в ТОВАРАХ */
select BASE_PRICE_LIST_ID from MOBIL_MAIN where ID = 1
into :aBASE_PRICE_ID;
if (NEW.PRICEID = :aBASE_PRICE_ID) then
update GOODS SET BASE_PRICE = NEW.PRICE
where ID = NEW.GDSKEY;
end
^
/* Trigger: PRICEGOODS_BI0 */
CREATE OR ALTER TRIGGER PRICEGOODS_BI0 FOR PRICEGOODS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (New.ID is Null) then New.ID = Gen_ID(PriceGoods_ID, 1);
end
^
/* Trigger: PRICEGOODS_BU0 */
CREATE OR ALTER TRIGGER PRICEGOODS_BU0 FOR PRICEGOODS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
New.LASTEDIT = Current_TimeStamp;
end
^
SET TERM ; ^
/******************************************************************************/
/*** Descriptions ***/
/******************************************************************************/
COMMENT ON TABLE PRICEGOODS IS
'Позиции товаров в прайс-листах';
/******************************************************************************/
/*** Fields descriptions ***/
/******************************************************************************/
COMMENT ON COLUMN PRICEGOODS.USERNAME IS
'Имя пользователя';
COMMENT ON COLUMN PRICEGOODS.LASTEDIT IS
'Дата / время последней модификации записи';
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
|
|
|
pricelist.txt (4,869 bytes)
/******************************************************************************/
/*** Generated by IBExpert 20.05.2014 10:57:22 ***/
/******************************************************************************/
/******************************************************************************/
/*** Following SET SQL DIALECT is just for the Database Comparer ***/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE PRICELIST (
ID INTEGER NOT NULL,
BASEPRICEID INTEGER,
BASEPRICE SMALLINT DEFAULT 0,
NAME VARCHAR(80),
PROC DOUBLE PRECISION,
CODE VARCHAR(20),
CURS DOUBLE PRECISION DEFAULT 1.0,
RND DOUBLE PRECISION,
BONUSPRICE DOUBLE PRECISION DEFAULT 0.0 NOT NULL,
USERNAME VARCHAR(20),
LASTEDIT TIMESTAMP DEFAULT Current_TimeStamp
);
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE PRICELIST ADD CONSTRAINT PRICELIST_PK PRIMARY KEY (ID);
/******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE PRICELIST ADD CONSTRAINT PRICELIST_BASEPRICEID FOREIGN KEY (BASEPRICEID) REFERENCES PRICELIST (ID) ON UPDATE CASCADE;
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/*** Triggers for tables ***/
/******************************************************************************/
/* Trigger: PRICELIST_BEFORE_DELETE */
CREATE OR ALTER TRIGGER PRICELIST_BEFORE_DELETE FOR PRICELIST
ACTIVE BEFORE DELETE POSITION 0
AS
begin
Delete From PriceGoods where PriceID=OLD.ID;
end
^
/* Trigger: PRICELIST_BU0 */
CREATE OR ALTER TRIGGER PRICELIST_BU0 FOR PRICELIST
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
New.LastEdit = Current_TimeStamp;
end
^
SET TERM ; ^
/******************************************************************************/
/*** Descriptions ***/
/******************************************************************************/
COMMENT ON TABLE PRICELIST IS
'Прайс-Листы';
/******************************************************************************/
/*** Fields descriptions ***/
/******************************************************************************/
COMMENT ON COLUMN PRICELIST.ID IS
'Уникальный код записи';
COMMENT ON COLUMN PRICELIST.BASEPRICEID IS
'Ссылка на базовый прайс-лист
(для производных прайс-листов)';
COMMENT ON COLUMN PRICELIST.BASEPRICE IS
'Тип прайс-листа:
=1 - базовый
=0 - производный';
COMMENT ON COLUMN PRICELIST.NAME IS
'Наименование Прайс-Листа';
COMMENT ON COLUMN PRICELIST.PROC IS
'Скидка в % от базового прайс-листа';
COMMENT ON COLUMN PRICELIST.CODE IS
'Код прайс-листа (сокращение)';
COMMENT ON COLUMN PRICELIST.CURS IS
'Курс у.е. (для перерасчетов)';
COMMENT ON COLUMN PRICELIST.RND IS
'Степень округления цен,
например:
0.01 - до одной коп.
1.00 - до 1 грн.';
COMMENT ON COLUMN PRICELIST.BONUSPRICE IS
'% бонуса';
COMMENT ON COLUMN PRICELIST.USERNAME IS
'Имя пользователя';
COMMENT ON COLUMN PRICELIST.LASTEDIT IS
'Дата / время последней модификации записи';
/******************************************************************************/
/*** Privileges ***/
/******************************************************************************/
|
|
|
stor32.zip mebl_ib5.zip |
|
|
Там ты триггеры BU в таблицах не добавил... там обновляется значение LastEdit |
|
|
Извини- не проверил весь скрипт... просто в начале был Drop Trigger а в нижней части уже Create ... а ты Update не делаешь? Или Drop/Create надежнее? |
|
|
да какая разница как оно делает главное делает одинаковые базы |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-05-20 16:48 | ruslan | New Issue | |
| 2014-05-20 16:48 | ruslan | File Added: pricegoods.txt | |
| 2014-05-20 16:48 | ruslan | File Added: pricelist.txt | |
| 2014-05-21 19:27 | barry | Note Added: 0001579 | |
| 2014-05-21 19:27 | barry | Status | new => resolved |
| 2014-05-21 19:27 | barry | Resolution | open => fixed |
| 2014-05-21 19:27 | barry | Assigned To | => barry |
| 2014-05-23 11:07 | ruslan | Note Added: 0001585 | |
| 2014-05-23 11:07 | ruslan | Status | resolved => feedback |
| 2014-05-23 11:07 | ruslan | Resolution | fixed => reopened |
| 2014-05-23 12:09 | ruslan | Note Added: 0001586 | |
| 2014-05-23 12:09 | ruslan | Status | feedback => assigned |
| 2014-05-23 12:09 | ruslan | Status | assigned => resolved |
| 2014-05-23 12:09 | ruslan | Resolution | reopened => fixed |
| 2014-05-23 20:28 | barry | Note Added: 0001588 | |
| 2014-06-17 13:39 | ruslan | Status | resolved => closed |
