40.9. 무지개 토토 절차

예 40-3. 갑PL/pgSQL무지개 토토 절차

이 예제 무지개 토토는 행이 테이블에 삽입되거나 업데이트될 때마다 현재 사용자 이름과 시간이 행에 스탬프되도록 합니다. 그리고 직원의 이름이 주어지고 급여가 양수 값인지 확인합니다.

테이블 emp 생성(
    엠프 이름 텍스트,
    급여 정수,
    last_date 타임스탬프,
    last_user 텍스트
);

CREATE FUNCTION emp_stamp() $emp_stamp$로 무지개 토토를 반환합니다.
    시작
        -- 사원명과 급여가 입력되었는지 확인하세요.
        NEW.empname이 NULL인 경우
            RAISE EXCEPTION 'empname은 null일 수 없습니다.';
        종료하면;
        NEW.salary가 NULL인 경우
            RAISE EXCEPTION '%는 null 급여를 가질 수 없습니다.', NEW.empname;
        종료하면;

        -- 그녀가 비용을 지불해야 하는데 누가 우리를 위해 일합니까?
        NEW.salary < 0이면
            RAISE EXCEPTION '%는 음수 급여를 가질 수 없습니다.', NEW.empname;
        종료하면;

        -- 언제 급여를 변경했는지 기억해 보세요.
        NEW.last_date := 현재_타임스탬프;
        NEW.last_user := 현재_사용자;
        새로운 반품;
    끝;
$emp_stamp$ 언어 plpgsql;

emp를 삽입하거나 업데이트하기 전에 emp_stamp 무지개 토토를 생성하세요.
    각 행 실행 절차 emp_stamp();

예 40-4. 갑PL/pgSQL감사를 위한 무지개 토토 절차

이 예제 무지개 토토는 행의 삽입, 업데이트 또는 삭제를 보장합니다.emp테이블은 다음에 기록(즉, 감사)됩니다.emp_audit테이블. 현재 시간과 사용자 이름이 행에 수행된 작업 유형과 함께 행에 표시됩니다.

테이블 emp 생성(
    empname 텍스트는 NULL이 아닙니다.
    급여 정수
);

테이블 만들기 emp_audit(
    작업 char(1) NOT NULL,
    스탬프 타임스탬프 NOT NULL,
    사용자 ID 텍스트가 NULL이 아닙니다.
    empname 텍스트는 NULL이 아닙니다.
    급여 정수
);

함수 생성 또는 교체 process_emp_audit()는 $emp_audit$로 무지개 토토를 반환합니다.
    시작
        --
        -- emp에서 수행된 작업을 반영하기 위해 emp_audit에 행을 생성합니다.
        -- 특수 변수 TG_OP를 사용하여 작업을 수행합니다.
        --
        IF (TG_OP = '삭제') THEN
            INSERT INTO emp_audit SELECT 'D', now(), 사용자, OLD.*;
            오래된 것을 반환하십시오;
        ELSIF (TG_OP = 'UPDATE') 그런 다음
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            새로운 반품;
        ELSIF (TG_OP = 'INSERT') 그런 다음
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            새로운 반품;
        종료하면;
        NULL을 반환합니다. -- AFTER 무지개 토토이므로 결과는 무시됩니다.
    끝;
$emp_audit$ LANGUAGE plpgsql;

무지개 토토 생성 emp_audit
emp에서 삽입, 업데이트 또는 삭제 후
    각 행 실행 절차 process_emp_audit();

예 40-5. 갑PL/pgSQL감사를 위한 무지개 토토 절차 보기

이 예는 뷰에 대한 무지개 토토를 사용하여 뷰를 업데이트 가능하게 만들고 뷰에 있는 행의 삽입, 업데이트 또는 삭제가 기록(즉, 감사)되도록 합니다.emp_audit테이블. 현재 시간과 사용자 이름이 수행된 작업 유형과 함께 기록되며 보기에는 각 행의 마지막 수정 시간이 표시됩니다.

테이블 emp 생성(
    empname 텍스트 기본 키,
    급여 정수
);

테이블 만들기 emp_audit(
    작업 char(1) NOT NULL,
    사용자 ID 텍스트가 NULL이 아닙니다.
    empname 텍스트는 NULL이 아닙니다.
    급여 정수,
    스탬프 타임스탬프 NOT NULL
);

emp_view AS 보기 만들기
    e.emp이름을 선택하세요.
           e.급여,
           max(ea.stamp) AS last_updated
      Emp e에서
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     1, 2로 그룹화;

함수 생성 또는 교체 update_emp_view()는 $$로 무지개 토토를 반환합니다.
    시작
        --
        -- emp에서 필요한 작업을 수행하고 emp_audit에 행을 생성합니다.
        -- emp에 대한 변경 사항을 반영합니다.
        --
        IF (TG_OP = '삭제') THEN
            emp WHERE emp에서 삭제 empname = OLD.empname;
            찾을 수 없으면 NULL을 반환합니다. 종료하면;

            OLD.last_updated = 지금();
            INSERT INTO emp_audit VALUES('D', 사용자, OLD.*);
            오래된 것을 반환하십시오;
        ELSIF (TG_OP = 'UPDATE') 그런 다음
            UPDATE emp SET 급여 = NEW.salary WHERE empname = OLD.empname;
            찾을 수 없으면 NULL을 반환합니다. 종료하면;

            NEW.last_updated = 지금();
            INSERT INTO emp_audit VALUES('U', 사용자, NEW.*);
            새로운 반품;
        ELSIF (TG_OP = 'INSERT') 그런 다음
            INSERT INTO emp VALUES(NEW.empname, NEW.salary);

            NEW.last_updated = 지금();
            INSERT INTO emp_audit VALUES('나', 사용자, NEW.*);
            새로운 반품;
        종료하면;
    끝;
$$ 언어 plpgsql;

무지개 토토 생성 emp_audit
emp_view에 대한 삽입, 업데이트 또는 삭제 대신
    각 행 실행 절차 update_emp_view();

예 40-6. 갑PL/pgSQL요약 테이블 유지를 위한 무지개 토토 절차

여기에 자세히 설명된 스키마는 부분적으로 다음을 기반으로 합니다.식료품점예제데이터 웨어하우스 툴킷작성자: 랄프 킴볼.

--
-- 기본 테이블 - 시간 차원 및 판매 사실.
--
CREATE TABLE time_dimension(
    time_key 정수는 NULL이 아닙니다.
    day_of_week 정수는 NULL이 아닙니다.
    day_of_month 정수는 NULL이 아닙니다.
    월 정수는 NULL이 아닙니다.
    분기 정수는 NULL이 아닙니다.
    연도 정수 NULL이 아님
);
고유 인덱스 생성 time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key 정수는 NULL이 아닙니다.
    product_key 정수는 NULL이 아닙니다.
    store_key 정수는 NULL이 아닙니다.
    amount_sold 숫자(12,2) NOT NULL,
    units_sold 정수는 NULL이 아닙니다.
    amount_cost 숫자(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- 요약 테이블 - 시간별 판매.
--
CREATE TABLE sales_summary_bytime(
    time_key 정수는 NULL이 아닙니다.
    amount_sold 숫자(15,2) NOT NULL,
    units_sold 숫자(12) NOT NULL,
    amount_cost 숫자(15,2) NULL이 아님
);
고유 인덱스 생성 sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- UPDATE, INSERT, DELETE 시 요약된 열을 수정하는 함수 및 무지개 토토입니다.
--
함수 생성 또는 교체 maint_sales_summary_bytime() 반환 무지개 토토
AS $maint_sales_summary_bytime$
    선언
        delta_time_key 정수;
        delta_amount_sold 숫자(15,2);
        delta_units_sold 숫자(12);
        delta_amount_cost 숫자(15,2);
    시작

        -- 증가/감소 금액을 계산합니다.
        IF (TG_OP = '삭제') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') 그런 다음

            -- time_key를 변경하는 업데이트를 금지합니다. -
            -- (아마도 DELETE + INSERT가 대부분의 방법이므로 그다지 번거롭지는 않을 것입니다.
            -- 변경사항이 적용됩니다.)
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXEPTION 'time_key 업데이트: % - % 허용되지 않음',
                                                      OLD.time_key, NEW.time_key;
            종료하면;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') 그런 다음

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        종료하면;

        -- 새 값으로 요약 행을 삽입하거나 업데이트합니다.
        <<삽입_업데이트
        루프
            업데이트 sales_summary_bytime
                SET 판매_금액 = 판매_금액 + 델타_판매_금액,
                    단위_판매 = 단위_판매 + 델타_단위_판매,
                    amount_cost = amount_cost + delta_amount_cost
                여기서 time_key = delta_time_key;

            insert_update가 발견되면 종료하세요.

            시작
                sales_summary_bytime에 삽입(
                            시간_키,
                            금액_판매,
                            단위_판매,
                            금액_비용)
                    값(
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                종료 insert_update;

            예외
                WHEN UNIQUE_VIOLATION THEN
                    -- 아무것도 하지 마세요
            끝;
        END LOOP insert_update;

        NULL을 반환합니다.

    끝;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

생성 무지개 토토 maint_sales_summary_bytime
sales_fact에 대한 삽입, 업데이트 또는 삭제 후
    각 행 실행 절차 maint_sales_summary_bytime();

sales_fact VALUES(1,1,1,10,3,15)에 삽입;
sales_fact VALUES(1,2,1,20,5,35)에 삽입;
sales_fact VALUES(2,2,1,40,15,135)에 삽입;
sales_fact VALUES(2,3,1,10,1,13)에 삽입;
SELECT * FROM sales_summary_bytime;
product_key = 1인 sales_fact에서 삭제합니다.
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET 유닛_판매 = 유닛_판매 * 2;
SELECT * FROM sales_summary_bytime;

예 40-7. 갑PL/pgSQL이벤트 무지개 토토 절차

이 예시 무지개 토토는 단순히 a공지지원되는 명령이 실행될 때마다 메시지가 표시됩니다.

함수 생성 또는 교체 snitch()는 event_trigger를 $$로 반환합니다.
시작
    알림 올리기 '밀고자: % %', tg_event, tg_tag;
끝;
$$ 언어 plpgsql;

ddl_command_start 실행 절차 snitch()에서 이벤트 무지개 토토 생성 snitch();