예 35-2. 갑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();

예 35-3. 갑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$ 언어 plpgsql;

트리거 생성 emp_audit
emp에서 삽입, 업토토 사이트트 또는 삭제 후
    각 행 실행 절차 process_emp_audit();

예 35-4. 갑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()은 $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 EXCEPTION '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;

        -- 이 time_key가 포함된 행이 없을 수도 있습니다(예: 새 데이터!).
        IF (찾을 수 없음) 그러면
            시작
                sales_summary_bytime에 삽입(
                            시간_키, 
                            금액_판매, 
                            단위_판매, 
                            금액_비용)
                    값( 
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );
            예외
                --
                -- 두 트랜잭션이 데이터를 추가할 때 경쟁 조건을 포착합니다.
                -- 새로운 time_key의 경우.
                --
                WHEN UNIQUE_VIOLATION THEN
                    업토토 사이트트 sales_summary_bytime
                        SET 판매_금액 = 판매_금액 + 델타_판매_금액,
                            단위_판매 = 단위_판매 + 델타_단위_판매,
                            amount_cost = amount_cost + delta_amount_cost
                        여기서 time_key = delta_time_key;

            끝;
        종료하면;
        NULL을 반환합니다.

    끝;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

생성 트리거 maint_sales_summary_bytime
sales_fact에 대한 삽입, 업토토 사이트트 또는 삭제 후
    각 행 실행 절차 maint_sales_summary_bytime();