pgsql触发器函数

-- Function: maint_sales_summary_bytime()

-- DROP FUNCTION maint_sales_summary_bytime();

CREATE OR REPLACE FUNCTION maint_sales_summary_bytime()

  RETURNS trigger AS

$BODY$

    DECLARE

        delta_time_key          integer;

        delta_amount_sold       numeric(15,2);

        delta_units_sold        numeric(12);

        delta_amount_cost       numeric(15,2);

    BEGIN

        -- Work out the increment/decrement amount(s).

        IF (TG_OP = 'DELETE') 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') THEN

            -- forbid updates that change the time_key -

            -- (probably not too onerous, as DELETE + INSERT is how most

            -- changes will be made).

            IF ( OLD.time_key != NEW.time_key) THEN

                RAISE EXCEPTION 'Update of time_key : % -> %

 not allowed', OLD.time_key, NEW.time_key;

            END IF;

            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') THEN

            delta_time_key = NEW.time_key;

            delta_amount_sold = NEW.amount_sold;

            delta_units_sold = NEW.units_sold;

            delta_amount_cost = NEW.amount_cost;

        END IF;

        -- Insert or update the summary row with the new values.

        <<insert_update>>

        LOOP

            UPDATE sales_summary_bytime

                SET amount_sold = amount_sold +

 delta_amount_sold,

                    units_sold = units_sold + delta_units_sold,

                    amount_cost = amount_cost + delta_amount_cost

                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN

                INSERT INTO sales_summary_bytime (

                            time_key,

                            amount_sold,

                            units_sold,

                            amount_cost)

                    VALUES (

                            delta_time_key,

                            delta_amount_sold,

                            delta_units_sold,

                            delta_amount_cost

                           );

                EXIT insert_update;

            EXCEPTION

                WHEN UNIQUE_VIOLATION THEN

                    -- do nothing

            END;

        END LOOP insert_update;

        RETURN NULL;

    END;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

ALTER FUNCTION maint_sales_summary_bytime() OWNER TO postgres;

------------------------------------------------------------------------------------------

-- Function: process_emp_audit()

-- DROP FUNCTION process_emp_audit();

CREATE OR REPLACE FUNCTION process_emp_audit()

  RETURNS trigger AS

$BODY$

    BEGIN

        --

        -- Create a row in emp_audit to reflect the

        -- operation performed on emp, make use of the

        -- special variable TG_OP to work out the

        -- operation.

        --

        IF (TG_OP = 'DELETE') THEN

            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;

            RETURN OLD;

        ELSIF (TG_OP = 'UPDATE') THEN

            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;

            RETURN NEW;

        ELSIF (TG_OP = 'INSERT') THEN

            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;

            RETURN NEW;

        END IF;

        RETURN NULL; -- result is ignored since this is an AFTER trigger

    END;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

ALTER FUNCTION process_emp_audit() OWNER TO postgres;

GRANT EXECUTE ON FUNCTION process_emp_audit() TO postgres;

--------------------------------------函数---------------------------------------

-- Function: proc_check_max(character, character)

-- DROP FUNCTION proc_check_max(character, character);

CREATE OR REPLACE FUNCTION proc_check_max(v_tno character, v_cno character)

  RETURNS integer AS

$BODY$

DECLARE

    a_max integer;

    a_cur integer;

BEGIN

    LOCK TABLE 教师选课结果表 IN EXCLUSIVE MODE;

    SELECT 人数上限 INTO a_max FROM 上课地点表;

    SELECT 选课人数 INTO a_cur FROM 教师选课结果表

 WHERE 教师号 IS NOT NULL and 选课号 IS NOT NULL ;

    IF a_max < ( a_cur + 1 ) THEN

         RETURN 0 ;

--RAISE EXCEPTION '超过最大人数限制,无法报名';

    ELSE

        RETURN 1 ;

    END IF;

END;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

ALTER FUNCTION proc_check_max(character, character) OWNER TO postgres;