제품&판매 관리 시스템 : 히스토리 테이블에 trigger 생성

 

 

 

[ 운영상 이슈 1 ] 제품&판매 관리 시스템 : 삭제, 수정 히스토리 테이블 생성 및 관리

[ 운영상 이슈 2 ]

2022.10.04 ~ 2022.10.05

<코드 비교분석>

- 운영상 이슈 1이 반영되기 이전시스템(2022.09.16)과 이후시스템(2022.09.29) 비교

- https://docs.google.com/document/d/10ssDIIz1snW6kVqG5QmxJZZMXY-7XmCdtkb01VgK8ho/edit?usp=sharing 

코드비교분석_221005

 

 

 

2022.10.05

<테이블 생성>

- product, product_history, sales과 속성과 내용이 같은 테이블을 각각 product2, product_history2, sales2으로 생성

– product 테이블 복제하여 product2 생성
create table if not exists product2 (select *  from product);
select * from product2;
 
– sales 테이블 복제하여 sales2 생성
create table if not exists sales2 (select * from sales);
select * from sales2;
 
– product_history 테이블 복제하여 product_history2 생성
create table if not exists product_history2 (select * from product_history);
select * from product_history;
 
– 각 테이블의 기본키, 속성 설정
alter table product2 add primary key(pCode);
alter table product_history2 add primary key(rowNo);
alter table sales2 add primary key(seqNo, sCode);
ALTER TABLE product_history2 MODIFY rowNo INT NOT NULL AUTO_INCREMENT;
 
– 올바르게 생성되었는지 확인
desc product2;
desc product_history2;
desc sales2;

 

 

 

<트리거란?>

- 테이블에 이벤트가 발생하면 자동으로 실행되는 작업 (pull the trigger)

- 데이터 무결성을 지키기 위해 사용

- 트리거를 통해 수정하려 할 때 에러가 발생하면 프로시저로 처리(한 테이블 내에서 트리거 실행 불가능)

- 매 이벤트를 동일하게 처리하지 않는 경우 프로시저로 처리

 

 

 

<트리거 생성>

1. product2 테이블에 insert가 발생했을 때 product_history2 테이블에 row를 추가하는 트리거

DELIMITER $$ 
CREATE TRIGGER autoBackupInsert
AFTER INSERT ON product2
FOR EACH ROW 
BEGIN 
INSERT INTO product_history2(pCode, pName, unitPrice, discountRate) VALUE (NEW.pCode, NEW.pName, NEW.unitPrice, NEW.discountRate); 
END $$ 
DELIMITER ;

 

2. product2 테이블에 update가 발생했을 때 product_history2 테이블의 기존 데이터의
    validUntil(종료일)을 현재로 수정하고 새 row를 추가하는 트리거

DELIMITER $$ 
CREATE TRIGGER autoBackupUpdate
AFTER UPDATE ON product2
FOR EACH ROW 
BEGIN 
UPDATE product_history2 SET validUntil = now() where pCode = OLD.pCode and validUntil = '9999-12-31 23:59:59';
INSERT INTO product_history2(pCode, pName, unitPrice, discountRate) VALUE (NEW.pCode, NEW.pName, NEW.unitPrice, NEW.discountRate); 
END $$ 
DELIMITER ;

 

3. product2 테이블에 delete가 발생했을 때 product_history2 테이블의 현재 유효한 데이터의
    validUntil(종료일)을 현재로 수정하는 트리거

DELIMITER $$ 
CREATE TRIGGER autoBackupDelete
AFTER DELETE ON product2
FOR EACH ROW 
BEGIN 
UPDATE product_history2 SET validUntil = now() where pCode = OLD.pCode and validUntil = '9999-12-31 23:59:59';
END $$ 
DELIMITER ;

 

 

 

<요구사항>

A. 이전시스템을 복제하여 모든 파일명 맨 뒤에 2를 붙임

 

 

B. product2, product_history2, sales2 테이블을 활용하여 운영상 이슈1과 같은 기능을 하도록

    판매조회(코드별-세무자료용) / (코드별-주주총회용) 메뉴를 추가

 

 

 

<완성 코드>

- 이후 시스템과 동작은 같음. 하지만 코드로 구현하지 않고 mysql의 trigger 기능을 활용했다는 점이 가장 큰 차이.

- 이후 시스템에서 복사/붙여넣기 한 부분 : 메인함수, ShowTables(천의자리 콤마), read_code_sales 메서드

- read_code_sales 메서드의 쿼리에서 테이블명만 product2, product_history2, sales2로 변경

- https://drive.google.com/file/d/1Zl181a-_bAQJkuMcrHOqWyofYGhB5rj-/view?usp=sharing 

 

- 주요 코드 : read_code_sales 메서드

    def read_code_sales(self, subject):
        # 22-09-29 생성 : history테이블의 최소값을 곱하여 세무자료를 만든다.
        cd1 = ConnectDB() # 클래스 생성
        st1 = ShowTables()
        subjects = {'min':'세무자료용', 'max':'주주총회용'}
        
        sql = """select S.seqNo, S.sCode, P.pName, S.sDate, S.Qty, S.Amt 
                from sales S, product P where S.sCode = P.pCode;"""
                # product 테이블과 sales 테이블을 inner join
                # product에 존재하지 않는 code를 가진 레코드는 출력될 필요가 없어서 inner join했다.
        rows = cd1.db_find_all(sql, "전체 목록 조회 오류입니다.") 
 
        if rows:
            print('\n<<<전체 목록 조회>>>')
            st1.print_sales_pname(rows)
            
            in_Code = input('조회할 코드를 입력하세요 : ')
            print('\n<<<판매조회 (코드별-{})>>>'.format(subjects[subject]))
            sql = f"""select S.seqNo, S.sCode, P.pName, S.sDate, S.Qty, 
                        S.Qty * (select {subject}(unitPrice) from product_history where pCode = '{in_Code}')
                        from sales S, product P where S.sCode = P.pCode and S.sCode = '{in_Code}'"""
                # inner join으로 제품명으로 가져오고, select문에서 서브쿼리로
                # history 테이블에서 최소단가를 가져와 곱한다.
            rows = cd1.db_find_all(sql, "판매코드 개별 조회 오류입니다.")
 
            if rows: 
                st1.print_sales_pname(rows)
                
                print('-'*82)
                amttotal = 0.00
                for row in rows:
                    amttotal = amttotal + row[5]
                print("{}".format(fmt("판매금액합계", 66)), end='')
                print(fmt("{:,.2f}".format(amttotal), 14, 'r'))
 
            else : 
                print("입력하신 코드에 해당하는 판매가 없습니다.")

 

 

 

<구현 결과>

1. show triggers;

 

2. 제품 등록

 

3. 제품 수정

 

4. 제품 삭제

 

5. 판매조회(코드별) 세무자료용

 

5. 판매조회(코드별) 주주총회용